Using PostgreSQL as an API for application development

Being a distillation of past experience, current thoughts, and (just possibly) a small amount making-it up as I go along (to fill any gaps found) with respect to the database side of developing applications using PostgreSQL.

The concept is to combine a defined set of conventions with database metadata to create the necessary database objects needed for using the database as an application API.

Some (hopefully minimal) assembly required.

Contents

• Givens
• Ground Rules
• Goals
• Constraints
• Documentation
• Version Control
• Logging
• Testing

Givens

top

1. The database back-end is PostgreSQL.

2. There may be multiple developers working on the database back-end.

3. Other development activity (middle tier, web, etc.) may be performed by other parties.

4. The application is "non-trivial".

5. There may be multiple different clients/applications connecting to the database.

Ground Rules

1. No one, but no one, is allowed to connect directly to database tables. All client interactions are performed through database views, functions, and/or procedures.

2. All client SELECTs are to be through views and/or set returning functions.

3. All client INSERTs, UPDATEs, and DELETEs are to be through updatable views, functions, and/or procedures.

The rational behind this is that, by not allowing external access to tables, it becomes much easier to refactor the table structure at a later date without promulgating too much breakage to any clients using the database.

This also helps to ensure, if there are multiple client applications interacting with the database, that the clients all play by the same rules. This is especially important with respect to access control; if the access control is centralized in the database itself then it is less likely that any client can subvert the access rules.

4. API documentation exists

5. No application objects are to be created in the public schema.

Goals

1. There are documented conventions, style guides, etc. for the database objects.

2. The majority of the API code is generated by using PostgreSQL functions and views against the database metadata.

3. API documentation is generated from the DDL files for the database objects.

4. There is a testing framework at the database level.

5. Much of the testing framework (and test cases) are generated using PostgreSQL functions and views on the database metadata.

6. It should be possible to copy the contents of this project to a new directory as a starting point for a new database application. TODO: should there be a script for initializing a new project?

7. Recurring development tasks that can be reasonably scripted should be scripted.

By using convention plus metadata, the intent is to accomplish these goals while:

1. Minimizing the amount of rote typing required for creating the database objects.

2. Leaning into the "Principle of Least Surprise" (PLS) (a.k.a. the "Principle of Least Astonishment" (POLA)).

3. Avoiding spending effort on repetitive or inconsequential decisions, because avoiding decision fatigue and making smart decisions is a thing.

4. Encouraging more up-front thought being put into the data model.

Constraints

1. To the extent practicable, all code generation functions are to be written as PostgreSQL functions and should make extensive use of the database metadata.

2. All SQL scripts are intended to be run using the psql client.

3. All other developer scripts, to the fullest extent practicable, are to be written in shell or bash.

Documentation

1. Creating good documentation is hard.

2. Having good documentation is very useful.

3. The further the documentation is from the thing being documented, the less likely it is to be correct.

4. Having documentation for an object in the same file as the object is useful when creating/updating the code for the object.

5. When trying to use an API, having the API documentation in one place is much more useful than having to poke around all the source files to determine what the API is.

6. Part of having good documentation includes having a decent data dictionary.

Version Control

All documentation, code, etc. should be managed using a version control system.

While I use git there are other (preferably cross-platform) software configuration management (SCM) options available such as darcs, fossil, or mercurial just to name a couple.

Since the database development team may consist of multiple people and since they may be different people from those developing the other pieces (middle tier, web, etc.) then it may make sense to keep the database code in a separate repository from any non-database code.

Logging

Logging at the database level for procedures/functions that update data has proven to be incredibly useful in troubleshooting update failures and also for generating test cases and data. However, performing database level logging has also been shown to be a potentially significant percentage of the time spent performing data updates. Logging should be performed as long as the overhead of doing so doesn't adversely affect the application performance.

Testing

There should be testing at the database level.