Learning PostgreSQL 11 - Third Edition

Version control systems

It's recommended you maintain your code using a revision-control system, such as Git or SVN. When writing SQL code, it's better to create an installation script and execute it in one transaction. This approach makes it easy to clean up if an error occurs. Also, a good practice is to create a rollback script to quickly return schema to the previous state if something goes wrong at the application level.

Database objects have different properties: some are a part of the physical schema, and some control database access. The following is a proposal for organizing the database code in order to increase the separation of concerns (SoC).

For each database in a PostgreSQL cluster, you should maintain the DDL script, for objects ...