Schema management and syncing

databaseci can generate and apply diffs - analyzing the changes between the structure of two databases, and generating the statements to transform the data from one to the other.

Managing the state of your database can be cumbersome because statements that modify the structure of the database generally are context-dependent and not usually idempotent.

That's where schema diffs come in.

For example, imagine I have two databases, one with an older version of my schema, and one with the newer version. I want to make older match newer. With many migration frameworks there are many cumbersome steps involved to create and apply the changes needed. But with schema diffing, it's as simple as:

import databaseci

older = databaseci.db('older')
newer = databaseci.db('newer')

diff_statements = older.schemadiff_sql(newer)

print(diff_statements)

This would generate and print the required SQL, perhaps something like:

alter table "public"."book" add column "author" character varying not null;

alter table "public"."book" alter column "name" set not null;

WARNING: Applying diffs do your database can be destructive. For instance, if newer in this example was missing a table present in older, the generated statements would include a DROP TABLE statement for this table!

Always, always carefully review database change scripts before applying them, particularly if they are anywhere near production databases. Apply appropriate permissions to any important databases and data.

Table changes, column changes, constraints, views, functions, indexes and many other PostgreSQL features are all supported.

Once you had reviewed these changes, you would then apply them:

older.q(diff_statements)

Now, older and newer match in structure. A subsequent diff should show no differences.

diff_statements = older.schemadiff_sql(newer)

print(diff_statements)  # Empty!

See usage and options for more ways to do schema diffing. See dev recipes for how to auto-sync your development databases and test your database changes, and deploy usage for how to incorporate diff-style migrations into your build and deployment flows.