🐘 pgfine

CLI tool to help with postgresql database schema updates, migrations and versioning.

The goal of pgfine is to provide project structure declarative as much as possible: - all database objects have their corresponding create script. - migration scripts should only be needed to update data-full objects - tables.

Install

From crates.io

bash cargo install pgfine

From repository

bash git clone https://gitlab.com/mrsk/pgfine cargo install --path ./pgfine

Create a new project

• Choose some version controlled directory.
• Create git-ignored env-local-db-0.sh (as an example) file like this:

```bash

All variables are mandatory to avoid mixed environments.

Connection strings: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING

no need to provide sslmode parameter.

credentials to connect to target db to perform updates and migrations.

role and database will be created if missing (using admin connection).

export PGFINECONNECTIONSTRING="..."

credentials for creating a new database refereced in the above connection string (usually postgres db with user postgres).

export PGFINEADMINCONNECTION_STRING="..."

path pointing to pgfine project, a good choice would be "./pgfine"

export PGFINE_DIR="./pgfine"

role prefix to make them unique per database.

if your plan is to have a single db per postgresql instance you can set it to "" and forget it.

role names should be referenced like "{pgfineroleprefix}role_name" in all the scripts.

if you plan to use global roles you should create them manualy or in ./pgfine/create/ scripts

export PGFINEROLEPREFIX="prod_"

path to root certificate. No tls mode will be attempted if this is set to an empty string.

https://www.postgresql.org/docs/current/ssl-tcp.html

export PGFINEROOTCERT="" ```

Environment variables need to be activated when using pgfine. You can do this by running: bash source env-local-db-0.sh

To initialize pgfine project run: bash pgfine init

This will create directory for storing all pgfine project data:

./pgfine/ ├── create │   ├── 00-create-role.sql │   └── 01-create-database.sql ├── drop │   ├── 00-drop-database.sql │   └── 01-drop-role.sql ├── functions ├── migrations ├── roles ├── tables ├── schemas │   └── public.sql ├── constraints ├── triggers ├── policies └── views

Modify newly created ./pgfine/create/*.sql and ./pgfine/drop/*.sql scripts if needed.

Create a database

• Modify ./pgfine/create/* scripts if needed.
• Setup environment and run:

bash pgfine migrate

Two extra tables will be created:

• pgfine_objects: contains a list of managed pgfine objects and their hashes. sql create table if not exists pgfine_objects ( po_id text primary key, po_type text, po_md5 text, po_script text, po_path text, po_depends_on text[], po_required_by text[] );
• pgfine_migrations: contains a list of executed migrations. Selecting the max value should reveal the current state of database. The first migration will be inserted as empty string. sql create table if not exists pgfine_migrations ( pm_id text primary key );

Making changes to database

• Apply any changes to database objects in ./pgfine/**/*.sql.
• Table chagnes should be additionally implemented via ./pgfine/migrations/* scripts.

• Run bash pgfine migrate

• Test your fresh db maybe.

• Commit all files to version control.

Rollbacks

• Restore database object scripts from previous commits
• Create a new migration script if it involves changing table.
• Apply changes the same way: bash pgfine migrate

Database objects

Database objects are: - tables - views - triggers - constraints - policies - functions - roles - schemas

Filenames for database objects must be of specific format : - tables: ./pgfine/tables/<schema>.<name>.sql - views: ./pgfine/views/<schema>.<name>.sql - functions: : ./pgfine/functions/<schema>.<name>.sql - triggers: ./pgfine/triggers/<schema>.<table>.<name>.sql - constraints: ./pgfine/constraints/<schema>.<table>.<name>.sql - policies: ./pgfine/policies/<schema>.<table>.<name>.sql - roles: ./pgfine/roles/<name>.sql - schemas: ./pgfine/schemas/<name>.sql

Each file contains script to create that object.

Updates are done by dropping the object and creating a new one.

Drop scripts are generated by object type and object name. Tables will never be dropped automatically - they have to be dropper/updated using migration scripts or manually.

Tables

Example ./pgfine/tables/public.table0.sql: ```sql create table table0 ( id bigserial primary key );

-- create indexes -- create constraints -- create rules -- create triggers

```

Table constraints and indeces can be stored along with tables. But to modify them you will have to write migration scripts.

If you have circular foreign key dependencies you should define those constraints in a separate ./pgfine/constraints/ files to break the cycle.

Views

Example ./pgfine/views/public.view0.sql: ```sql -- it is recommended to include "or replace", otherwise it will be dropped and created again each time changes are made. create or replace view view0 as select t0.id from table0 t0 join table1 t1 on t1.id = t0.id

-- create indexes maybe ```

Constraints

Example ./pgfine/constraints/public.table1.t0_id_fk.sql: sql alter table table1 add constraint t0_id_fk foreign key (t0_id) references table1 (id);

Commands

pgfine init

• Initializes pgfine project at path PGFINE_DIR.

pgfine migrate

If database is missing:

• Executes ./pgfine/create/ scripts to create role and database (using admin connection).
• Creates pgfine tables.
• Creates all database objects defined in pgfine project.

If database exists:

• Attempts to drop all dirty objects by comparing pgfine_objects table and project contents.
• Attempts to create all missing objects.

pgfine drop --no-joke

• Force drops all roles found in pgfine_objects table.
• Force drops all roles found in project.
• Executes ./pgfine/drop/ scripts to drop role and database (using admin connection).

Assumptions

• Passwords, database names and roles can only have alphanumeric characters and underscores.
• Each script filename must uniquely identify correspoinding database object.
• Filename information is used to track dependencies between objects using simple whole word search, assuming default public schema.
• Triggers, constraints and policies are assumed to not be required by other objects (always safe to drop).
• Each new file in ./pgfine/migrations/ is assumed to be increasing in alphabetical order.
• empty string is the name of the first migration (inserted if no migrations exist)
• {pgfine_role_prefix} text should not be used for other porpuses as for database-role prefix in your scripts.
• no md5 comparison is done for schema objects. It is assumed that schema script should always be "create schema schema_name;"
• default drop database scrip assumes postgres v13, if you use lower version you should add script to drop connections.

Alternatives

At the current stage pgfine is not the best thing in the world. You might also want to check these alternatives: - refinery - flyway - diesel.rs - dbmigrate - and more...

Post 1.0.0 plan

• [ ] validate table schema when hash has changed (by creating separate DB? and comparing?) before applying all other updates
• [ ] PGFINE_ALLOW_DROP variable to protect production envs
• [ ] example projects at ./example/
• [ ] documentation https://documentation.divio.com/ https://jacobian.org/series/great-documentation/
• [ ] ./pgfine/initial/ execute after the database is created
• [ ] ./pgfine/final/ execute after the database objects are created
• [ ] operations in single transaction if possible
• [ ] configurable search schemas
• [x] make execute order deterministic
• [ ] ignore comments in scripts when resolving dependencies
• [ ] support stable rust
• [ ] support for initial data (can be achieved by creating custom functions to initialize the data)
• [ ] generate project from existing database