Database Migrations and ax_define functions

Introduction

Writing database migrations is an integral part of the development process when working with Mosaic backend services. As already seen from other articles, e.g Create New Entity and Extend Existing Entity, migrations are written using SQL. This allows all the flexibility that PostgreSQL provides.

To make the process of writing migrations easier and less error-prone, Mosaic provides a lot of SQL helper functions that can be used when writing migrations. These functions encapsulate commonly used logic in a compact way and are provided as part of the @axinom/mosaic-db-common npm package.

Some of the functions are located in ax_utils PostgreSQL schema, while others are in ax_define PostgreSQL schema. The ax_utils`` functions are used during the service runtime while the ax_definefunctions are used in SQL migrations. Theseax_define` functions are optional to use and can be replaced with regular PostgreSQL code, but practice has shown that using them makes migrations more readable and overall simplified the process of writing migrations code.

When creating SQL migration files, VSCode cannot provide intellisense within SQL files. Thus you would always have to look up the function declaration for the ax_utils and ax_define functions in the Mosaic documentation. Luckily, VScode supports custom code snippets, which are also applicable to SQL files. A set of such custom snippets is included in our solution templates to provide development-time documentation on how to use those SQL helper functions.

Overview of ax_define functions using snippets

By default, ax_utils and ax_define helper functions and related VSCode snippets are included in all backend services of solution templates. To test them out, pick an SQL file (e.g. migrations/current.sql) and start typing ax-. A dropdown will appear with a list of all available snippets.

Snippets usage example

All mosaic-related custom snippets start with ax- prefix, to distinguish them from other possible custom snippets. Snippets allow you to discover currently supported functions, provide usage documentation and populate your migration file with correct SQL code. Snippets are designed with tabulation in mind, which means that you modify highlighted placeholder values, which will change all placeholders of the same type, and switch between placeholders using the Tab key.

Snippets tabulation example

Apart from snippets related to specific ax_define and ax_utils functions, there are also snippets for commonly used code pieces, which can include the usage of multiple different ax_define functions, or non at all.

Keeping snippets and ax_define functions up-to-date

As already stated, the @axinom/mosaic-db-common npm package provides us with everything we need to work with custom functions and snippets, and the solution templates have everything included and enabled by default. When the package is updated, it is possible that it will update SQL helper functions and snippets as well.

• ax_utils functions are configured to be updated automatically and it’s recommended to keep it that way.
• ax_define functions must be updated manually
• VSCode snippets must be updated manually

Updating ax_define functions

ax_define functions are added and updated as part of the database migrations. To actually produce the migration for the update, @axinom/mosaic-db-common package provides an extension to the @axinom/mosaic-cli. This CLI npm package must be installed in either the project or repository to make the following command work:

yarn mosaic generate-define-func-migration

There is also a wrapper script for it in the solution templates backend service projects, so the following call will do the same:

yarn util:define-func-migration

This call will adjust the migrations/current.sql file in the project for which the script was called, adding a migration code based on the contents of the @axinom/mosaic-db-common package. The migrations/current.sql must be empty or only contain comments for this script to work, otherwise, an error would be thrown.

After the migration code is generated, it can be reviewed and committed as any other migration. It is not recommended to change the generated code.

Reason for migrations update approach

The current approach with migrations was chosen because these functions are optional to use and to prevent updates from breaking SQL migrations that depend on an older version of ax_define functions. These functions do not contain any business logic and only define database schema. For example, if ax_define functions would have been updated automatically, outside of migrations, following scenario would be true:

If an old migration was using ax_define.define_indexes_with_id(fieldName text, tableName text, schemaName text) and we would change the schemaName type to an enum type with values app_public, app_hidden, etc... the old migrations would fail next time a database is created from start (e.g. new environment). graphile-migrate would also throw an error when a new migration would be committed.

Updating VSCode snippets

After ax_define functions are updated, existing VSCode snippets might become outdated, so it makes sense to update them as well. Similarly, @axinom/mosaic-db-common package provides an extension to the @axinom/mosaic-cli for a dedicated command to help with an update. The command is:

yarn mosaic generate-vscode-sql-snippets

Running it would generate a mosaic-sql-migrations.code-snippets file in a .vscode folder of the repository. The convention is to have a file with an extension name .code-snippets in the .vscode folder, and VScode will use the snippets from it. It is recommended to not make any manual changes to this file and if you need to make some changes - add a .code-snippets file with a different name and add a custom snippet there, to make sure that next time the command is executed - it will not override the custom snippets.