Skip to main content

Database

By default, every Mosaic microservice has one PostgreSQL database to hold and manage its data. The database is used to create the GraphQL schema to expose entities in the API. It is also used for other internal service data, not exposed over API.

Schemas​

To accommodate both GraphQL and internal data, Mosaic uses different database schemas and database roles. The ones that are meant to be used in your project are:

  • app_public: this database schema should contain all the tables that you want to expose as a part of the GraphQL API. The database role DATABASE_GQL_ROLE should receive fine granular GRANTs to select, insert, update, and delete all or some data for those tables and columns.
  • app_hidden: this database schema should contain all the tables that are accessible to the database role DATABASE_GQL_ROLE but should not be made available in the GraphQL API. Those tables might be used and exposed through PostgreSQL functions (in the app_public schema) or used in plug-ins.
  • app_private: this database schema contains tables and functions that are not available to the database role DATABASE_GQL_ROLE. Those are only available to the database owner role and contain secret/sensitive data.
  • ax_utils: this database schema contains PostgreSQL utility functions that help you to create a consistent database structure and optimal integration into existing Mosaic plug-ins. Those functions are provided by the @axinom/mosaic-db-common library and should not be changed.

Database Migration​

To create and maintain your database, you can use any database migration tool or manually create and alter the database. In our template projects, we use the Graphile migrate library for easy and fast development cycles which automatically update the database and the local GraphQL API on every change to the migration SQL file.

Every project has a ./migrations/current.sql file in which you can add your SQL migration code. As the Graphile migrate is a forward only migration and is executed on many times against the local database it must be idempotent. This means, for example, to add a DROP TABLE IF EXISTS app_public.movies CASCADE; before calling the CREATE TABLE app_public.movies (…). When the migration is applied (exactly once) in production, this does not cause any issues. However, it is immensely helpful to have fast development cycles locally.

When your local service runs in the dev mode, it watches for any change to the current.sql file. Whenever you save the file, it is run against your local database. This way, you get immediate feedback if there are any errors in the SQL file. If it can be applied successfully, it automatically triggers a rebuild of the GraphQL API to make the new changes directly available without any manual rebuild/restart. If you use the GraphiQL web IDE, this automatically reloads the schema as well. In summary, this allows to change something in the current.sql file which directly reflects in the GraphiQL web app.

When you are happy with the migration SQL code, you can run the Graphile migrate commit command to freeze the contents of your current.sql file into a committed database migration file. This is a Grapile migration step - not to be confused with GIT commits. After this step, the current.sql file is empty again. If you want to change something in the last committed database migration file, you can run the uncommit command which removes the last commit file and adds the contents back to the current.sql file. This operation must be used only as long as it was never deployed to production!

As a general best practice, the current.sql file should always be empty when you GIT commit code to the master branch.

When the application is deployed to some productive server, it executes all the committed database migration files to the database that were not run so far. The current.sql file is only executed in the DEV mode - not in the production mode.