Database Migrations and ax_define functions
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
ax_define PostgreSQL schema. The
ax_utils` functions are used during
the service runtime while the
ax_define functions are used in SQL migrations.
ax_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.
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.
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
Apart from snippets related to specific
there are also snippets for commonly used code pieces, which can include the
usage of multiple different
ax_define functions, or non at all.
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_utilsfunctions are configured to be updated automatically and it’s recommended to keep it that way.
ax_definefunctions must be updated manually
VSCode snippets must be updated manually
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
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:
This call will adjust the
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.
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
functions would have been updated automatically, outside of migrations,
following scenario would be true:
ax_define.define_indexes_with_id(fieldName text, tableName text, schemaName text)and we would change the
schemaNametype to an enum type with values
app_hidden, etc… the old migrations would fail next time a database is created from start (e.g. new environment).
graphile-migratewould also throw an error when a new migration would be committed.
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
.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.