Import and migrations

Asqueel connects the recipe and the database in two directions. From the recipe to the database, the migration compares the model with the live database and applies the difference. From the database to Python, the importer reads the PostgreSQL catalog. The migration needs the extra named migration. On PostgreSQL, it also needs the extra named postgresql.

On screen (addition): pip install 'asqueel[postgresql,migration]'

db dot migration_plan compares the model with the live database. It changes nothing. It returns a MigrationPlan with three fields. commands is the DDL text. warnings lists the warnings of the migration engine. skipped lists the changes the backend cannot apply. The property empty is True when there is no command. On a new server, the plan starts with CREATE DATABASE. The database name comes from the connection settings, here the DSN, not from the name of the recipe. db dot migrate computes a new plan, applies it, and returns it. Then it compares again, and raises MigrationError if differences remain. A second plan is empty.

The plan creates the database, the schema of the package, and every table. The table name carries the package prefix, because the package declares sqlprefix. The sys fields of lesson 25 add the underscore underscore timestamp columns and their indexes. unique equal True on inv number becomes a UNIQUE constraint. Every relation column gets an index, unless the relation says indexed equal False. A foreign key constraint exists only where the relation declares foreign key equal True. It carries ON UPDATE CASCADE by default. onDelete equal cascade adds no ON DELETE clause. That cascade is a Python relation action, as in lesson 23. Virtual columns, such as aliasColumn and formulaColumn, create nothing.

A layer adds a phone column to customer and a new table, invoice log. The plan adds the column and creates the table. foreignKeys equal False creates no foreign key constraint. The index of the relation column stays. Foreign keys that already exist in the database are kept. With the default, True, the plan also adds the foreign key. After db dot migrate, a new plan is empty.

The key of invoice log has dtype serial4. serial4 is a 32-bit PostgreSQL serial column. serial is the 64-bit form. On insert, the database assigns the key. The insert reads it back and writes it into the record.

Removals are disabled by default. Without phone in the recipe, the plan is empty. allow_removals equal True enables the supported removals. In this release, the supported removals are columns. Whole tables, indexes, relations and constraints are not removed. The second example changes product code from text to a 64-bit integer. The table holds the codes 100 and P-2. The plan itself raises MigrationError, because a value would not convert. force equal True converts anyway, and values that do not convert become NULL. backup equal True implies force. It first copies the column into code underscore underscore A, named after the old type. After migrate, product P-2 has a NULL code, and its old value is in the backup column. The returned plan warns about every backup column that keeps lost values.

inspect_postgres takes a psycopg connection and a list of schema names. It reads the catalog and changes nothing. It does not commit, roll back or close your connection. The example opens a read-only transaction to read one consistent snapshot. It returns an ImportResult with two fields, model and warnings. A schema that does not exist produces a warning. The model holds the physical tables, with their columns, keys and foreign keys. Table names are the physical names, with the prefix. A foreign key is a relation named after its constraint. Common PostgreSQL types become asqueel type codes, and the original type stays in raw underscore type. The importer needs PostgreSQL 15 or newer.

In this release, the imported model is a description. The query compiler refuses it with TypeError, because it needs a model built from a recipe. To query a database, you write a recipe, as in lesson 3. The imported model can be projected for the migration engine. A model with import warnings cannot be projected, so we inspect only the existing schema. allow_constraint_rename equal True accepts that constraint names differ from the generated ones. SqlModelReader turns the projection into a builder tree. SqlPythonEmitter emits the Python source of that tree.

The emitted source is a SqlBuilder class. It uses schemas and tables, not packages, and physical names. Every index of the database is an explicit index element with its database name. The emitted module has no connection. In this release, it does not load as a configured database when its tables have relations. The explicit indexes repeat the indexes that the relations already imply, and validation refuses the duplicates. Use the emitted source as the starting point of a recipe you edit by hand.

migration_plan shows the DDL, and migrate applies it and checks the result. Four options control removals, foreign keys and type conversions. inspect_postgres returns a description of the catalog, which you inspect or emit as source. The next lesson runs the same migrations from the command line.