
Ask HN: How do teams co-ordinate the dev of different web apps with one DB? - manojvenkat92
So, in our start-up, we&#x27;re at a perplexing situation where we have a single Postgres database and multiple teams developing different web apps in Java, Clojure &amp; Rails. We&#x27;re facing a problem when the db schema changes, then everybody has to update the code, check for regressions etc.<p>I wanted to ask how other companies&#x2F;start-ups who have multiple components usually deal with this problem.<p>We&#x27;re currently looking into Thrift&#x2F;Proto-Buffer. Let us know if there are better options. Thanks, folks.
======
davismwfl
My answer is don't have each end web app talk to the database directly as
there will always be breaking changes.

The best option (IMO) is to create a service level API to handle all data
reads/writes for everyone (using JSON data), thereby centralizing changes in
one place and not breaking an app just because the schema changed. This is how
I create almost all applications now, because I can hide all the schema
details behind the API, plus I can have unlimited consumers as long as
everyone follows the contract. This takes the pressure of my database from
being an integration point, and I can move data to other databases, add
caching etc all without disturbing the consumers because the contract stays
the same through the API. Teams that need to make a schema change have to
update the API to guarantee the contract stays consistent for all consumers.

Thrift/Proto-Buffer is similar to my data API, but to me there are key
differences. I suggest JSON, not because I think it is faster, it isn't, but I
favor simplicity and compatibility over optimization unless I can prove the
need for optimization. I also lean away from Thrift because I hate adding more
into my build process especially when I have multiple languages involved where
the tools I am adding might have different levels of compatibility/support.

There are also some simple options/steps you can take to help minimize
breaking changes in the interim.

1\. All queries should use named columns, never should there be a * in a
query. I mention this because I still find myself fixing many systems that use
SELECT * in their queries and break when a column gets added. Along that same
line, don't use ordinal positions when accessing the data returned from the
query, yes it is faster but it breaks easily. Sorry if these seem basic, I
still see new systems developed with these issues a lot.

2\. Use stored procedures to provide the data contract instead of a service
API. This gets tough to manage after some time too and IMO is not the right
perm solution but it can help in a pinch. I have versioned stored proc's for
this purpose, where there is a default output, or with a passed in specific
version it will return a "new" version. Not ideal IMO, but it works. e.g.
getUserAccount() gets standard user account data, getUserAccount(2) gets
version 2 of the user account.

3\. Each web app could isolate data access and essentially create its own data
API (you may already have this). While this doesn't fix the real problem it
will isolate the changes to one place in each app which might help speed
things up (or it could make more work). I would't personally do this one
without a lot of thought, but I also don't know your situation enough to say
this wouldn't be viable at least in the short term.

