Database Upgrading Framework
Database Migration Tool
Usage: dbupgrade [OPTIONS] [-l API_LEVEL|-L] DBNAME SCHEMA DIRECTORY
Upgrade the given SCHEMA
in the database specified as DBNAME
with SQL
scripts from DIRECTORY
. DIRECTORY
is searched for all files with the.sql
suffix. These files are SQL scripts with a special header sections:
-- Schema: my-db-schema
-- Version: 25
-- API-Level: 3
-- Dialect: postgresql
CREATE TABLE ...
The following headers are required:
postgresql
or sqlite
, but do not includepostgresql
instead ofpostgresql+psycopg
.yes
(default) and no
. When thisThe database must contain a table db_config
with three columns: schema
,version
, and api_level
. If this table does not exist, it is created.
This table must contain exactly one row for the given schema. If the row
does not exist, it will be created with the version
and api_level
columns
initially set to 0.
The current version and API level of the schema are retrieved from the
database, and all scripts with a higher version number are applied in order.
If any version numbers are missing, the script will stop after the
last version before the missing version.
Unless the -l
or -L
option is supplied, only scripts that do not
increase the API level will be applied. If the -l
option is given, all
scripts up to the given API level will be applied. -L
will apply all
scripts without regard to the API level.
Each script is executed in a separate transaction. If a script fails, all
changes made by that script will be rolled back, and the script will terminate
with an error message and a non-zero return status.
When supplying the --json
option, dbupgrade
will print information about
the applied scripts as JSON to the standard output. Sample output:
{
"success": true,
"oldVersion": {
"version": 123,
"apiLevel": 15
},
"newVersion": {
"version": 125,
"apiLevel": 16
},
"appliedScripts": [
{
"filename": "0124-create-foo.sql",
"version": 124,
"apiLevel": 15
},
{
"filename": "0125-delete-bar-sql",
"version": 125,
"apiLevel": 16
}
],
"failedScript": {
"filename": "0126-change-stuff.sql",
"version": 126,
"apiLevel": 16
}
}
The success
key is true
if all scripts were applied successfully or if no
scripts needed to be applied. In this case, the failedScript
key is not
included. The appliedScripts
key is always present and contains an array
of applied scripts. If no scripts were applied, this array is empty.