We have developed a long list of conventions for writing SQL schema and stored procedure definitions in order to have some consistency.
Each application should have an sql directory within its project directory. Within this directory will be SQL script files which re-create the project schemas from scratch. These will have typical names, as follows:
- init.sql — Main script, which includes the other scripts to create the entire schema. This file should start with “\set ON_ERROR_STOP” and the remainder of the file should be bracketed with a SAVEPOINT/RELEASE pair to ensure that creating the schema either succeeds entirely or fails entirely.
- init_type.sql — Any DDL that cannot be expressed idempotently. This includes type and domain creation.
- init_main.sql — Contains more \ir directives to include the specific files which create the rest of the schema contents. Everything included from here should be idempotent — that is, running it twice will have the same effect as running it once. For example, views must be created using CREATE OR REPLACE VIEW while tables must be created using CREATE TABLE IF NOT EXISTS.
- init_data.sql — Initial population of application tables. This will typically consist of a number of INSERT statements to load the initial values into application tables. This would typically include the contents of any table for which part of the meaning of the rows comes from application code, and therefore if an additional row is to be added it probably needs changes to the application code as well.
- init_perm.sql — Permissions settings for all schema objects.
To update an application schema in an existing database, a new script should be written which includes init_main.sql as well as any additional commands that are needed to change existing objects. Changes to views and functions which do not affect their interfaces will be handled automatically by the inclusion of init_main.sql if the above notes about making it idempotent are followed. Changes to tables will always require ALTER TABLE statements. The change script should finish with inclusion of init_perm.sql.
Structure of init.sql
This is the top-level script for re-creating an application schema. Typically it should contain the following elements:
- \set ON_ERROR_STOP — So that execution will stop if an error is encountered.
- SAVEPOINT — To ensure that we must be in a transaction in order to run the creation script.
- SET role TO _dba; — Some actions need to take place as privileged user.
- Grant role memberships. This includes relationships between application roles, and granting membership in roles of applications which are dependencies of this applications to appropriate roles of this application.
- CREATE SCHEMA — Create the application schema.
- Schema permissions, typically USAGE to public and ALL to _[application]_root.
- Schema comment.
- Search path setting. The search path should begin with the application schema, continue with application schemas of dependencies, and finish up with _data, public, and pg_temp.
- Alter application login roles to have appropriate search path.
- SET role TO _[application]_root; — Create remaining objects as this role.
- \ir init_type.sql
- \ir init_main.sql
- \ir init_data.sql
- \ir init_perm.sql
- SET role TO _dba;
- SELECT data_configure_schema_objects ('_[application]'); — This sets ownership and some permissions on schema objects.
- RELEASE — Release the savepoint.