Examples of using Schema Guard

• The Postgresql script tracking tool

Here you will see a set of scripts that demonstrate usage of Schema Guard [rdbm] by DBinvent.

The examples show most typical use cases and are written to run in a Docker container. You can also use them as a starting point to adopt Schema Guard into your workflow quickly and without touching Docker at all.

Before we move on to running tests, let's briefly describe the main Schema Guard features.

About Schema Guard

Schema Guard is a database schema migration tool for PostgreSQL that helps manage, control and deploy database schema changes across various environments and development stages.

It employs the automatic SQL generation and plain-SQL approach in one software. Scripts can be assigned with a version and a certain migration phase, which brings developers true flexibility to perform the necessary action at an exact migration stage.

Key features

• Creates database and user if needed
• Provides both: schema/table definition and script execution tracking
• Simple ETL: data extraction from snapshots AND data insert after schema migration
• Supports a separate DB connection for history table as a remote copy of a primary one
• Uses native psql client for script execution, built-in support for psql --single-transaction flag
• No JVM nor Python required to run
• Lightweight, just about 10MB
• Comes with an apt repository to stay updated
• Transactional changes with rollback OR configurable non-transactional mode
• Dry run on existing databases, diff tool friendly output
• Free personal license

Use cases

The use cases perform database schema migrations and make some dumps. Then the produced YAML and SQL files are compared with the expected result.

1. Baseline - Validates the license and performs a self migration.
2. Create DB - Creates user (role) and database on demand.
3. Simple migration - Simple migration, schema in YAML then a few inserts in plain-SQL script.
4. Versioned, schema in YAML files - Sample of declarative schema definition in YAML files, version 1 to version 2 migration.
5. Versioned, schema in SQL scripts - Plain-SQL way to migrate database schema.
6. Stored procure - Repeatable scripts execution on every migration, useful for stored procedures and functions.
7. Mixing SQL with YAML - Mixing declarative schema definition in YAML files with plain-SQL scripts, triggers and before schema generation SQL script.
8. Simple examples of Extract-Transform-Load operations: 1, 2, 3
9. ETL: mixes SQL and CSV - Mixing data sourcing from SQL and CSV files.
10. Tag and User - Tagging migration and run it under a specific user.
11. Dry run - Lists the SQL that is generated for upcoming migration, without making actual changes.
12. Remote history DB - History table in the external database.
13. URL - Passing a connection string through --db_url= param rather than providing the configuration file.
14. ENV - Using the environment variables to set the configuration params.

File naming convention

Versioned scripts, schema files and data files for ETL operations follow simple naming rules. You can find more details in the Script Versions and Types article.

[Type][Version]__[Script_name].[yaml|sql|csv]
  1       2            3              4

1. Type - Single Capital Letter, one of these: P,S,V,R,D or A for 'after'
2. Version - one or more digits divided by _, which further will be replaced with dots. Ex: 1_2 will be 1.2.0
3. Script name - any meaningful name of the script file
4. File type: yaml, sql or csv

File types and execution order

1. P - The Pre-deploy SQL scripts are executed before the schema is changed from S and V files.
2. S - The next Schema version in YAML format. Schema Guard computes the changes required and generates SQL commands on the fly. You can mix S and V files. See also D type.
3. D - The Data files for ETL operations used in batch with the S-files. See ETL example
4. V - Transition to the next schema Version in plain-SQL. Usually, the most-used file types. You can mix V and S files.
5. R - The Repeatable and mutable scripts, applied every time the schema is changed. Useful for stored procedures and functions.
6. A - After migration scripts. Run after all the scripts of the same version or at the end of all migrations. Allowed names are after.sql and after_each.sql

Running the examples

To run the examples, you need the Docker installed.

Clone this repository:

git clone https://github.com/DBinvent/rdbm.git

Then cd to rdbm folder:

cd rdbm

Now you are ready to run examples, one-by-one or all at once. To get the list of all available test cases:

./test.sh

Running a specific example

./test.sh baseline # or any other name from the list displayed by ./test.sh

Running an example and keeping psql prompt open

If you're interested in examining the resulting schema or history table content, it can be useful to keep the psql prompt after the example is completed.

./test.sh baseline psql

Running an example and keeping bash prompt open

You can play with preconfigured rdbm application in the launched Docker container.

./test.sh baseline bash