Your Database as an API

Many developers have encountered databases with hundreds of poorly named tables which themselves have poorly named fields and haphazardly specified relationships. Sometimes this is a developer’s first experience with a large database and they are permanently soured.

Your database should be appropriately structured as it grows to contain and encapsulate complexity, just like you would with your code.


In web development the term ‘API’ has become practically synonymous with a JSON HTTP server. Tables and views in your database also constitute an API - an API that gives you projection, filtering, aggregation, ordering and joins for free, as well as a much more efficient binary transport.

You may not want to use your database as an external public API, as it can be hard to predict query performance, but it makes for a perfectly good internal one.

A Suggestion

I’m going to give an example of a way to structure your database that uses PostgreSQL’s privileges , schemas and views to give your database more structure.

The schemas will be somewhat analogous to modules, and publicly accessible views will be analogous to public API interfaces. Therefore, the public views should be kept backwards compatible whenever possible.

Each sub-system of your application could have its own database user that has internal access to a few relevant schemas, and access to all public views.

This allows you to change the internals of your schema and maintain backwards compatibility.


It’s also important to have visibility of the structure of your database.

Show me your flowcharts and conceal your tables, and I shall continue to be mystified. Show me your tables, and I won’t usually need your flowcharts; they’ll be obvious -- Fred Brooks

Documentation tips:

  • When documenting the structure of your database, it can be useful to see both the relationships between tables and the components of derived views.
  • Use the comment PostgreSQL functionality.
  • Auto-generate documentation where possible to stop it going out of date.
  • Documenting the flow of data through your system can also be helpful.

Coming up

It has become popular to split up applications in to multiple databases. I’d like to evaluate some the pros and cons of this approach, as I think this has happened in part as a reaction to poorly maintained databases and there are many drawbacks to be aware of (and, yes, some benefits too).