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_define
functions are used in SQL migrations.
These 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.
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.