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 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.

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.