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.