Skip to main content

@axinom/mosaic-db-common

This library provides common Mosaic logic around database access and usage.

The Mosaic solution is based on two main technologies when it comes to database access. On the one hand, there is PostGraphile. This is the fundamental tool, used to generate the GraphQL API and manage all the aspects around GraphQL. The other library is zapatos. It generates strong type support for the databases and provides functionality for all kinds of CRUD operations. It is no ORM but offers to access the database in a strongly typed way.

Most of the functionality provided in the @axinom/mosaic-db-common package revolves around those two libraries.

Database pools​

For security reasons, the database access is split into two different pools. On the one hand, there is the "Login Pool". This pool is used from the GraphQL API to make CRUD operations on non-critical data. The other pool is the "Owner Pool". This pool is used to access restricted data like hashed passwords and other data that should not be exposed. The owner pool will also bypass any Postgres Row-Level Security (RLS) policies. This is to ensure that the owner of the database can always access the data.

The setupLoginPgPool and setupOwnerPgPool are used to set up the pools. While the getLoginPgPool and getOwnerPgPool are used to get the corresponding pools.

Both of those pools are provided to the PostGraphile library and can be used in conjunction with zapatos.

Authorization​

The authorization functionality makes sure, that the currently authenticated user/service account is used within the corresponding database calls. It makes sure to honor the used database permissions if Mosaic-based row-level security is enabled on the PostgreSQL database.

The buildPgSettings takes authentication subject name, permissions, and tags and sets them as PostgreSQL variables on the database connection. The database can use those variables with row-level security to ensure safe data handling.

This functionality is used whenever the database access is established. This is the case for PostGraphile at start up and for every zapatos connection.

Strong type generation with zapatos​

We use zapatos to generate TypeScript files to easily work with your database. The transactionWithContext function is the most used one. It provides a database transaction with the desired isolation level and includes all the variables from the PostGraphile context (see the Authorization section). This is often used when a database transaction must be executed in the context of the database owner.

Database migration​

Database migrations use the graphile-migrate library. It offers fast development iteration speed and integrates nicely into PostGaphile.

We offer common functionality to run the database migrations in a reliable way. The migrations and the used scripts are already set up for your convenience in the provided projects.

Database scripts​

This library provides SQL functions to help you to set up and change your PostgreSQL database in a performant and secure way. All the scripts are written in an idempotent way to work nicely together with the "graphile-migrate" library

As a best-practice you should add/update those helper scripts whenever you are applying a database migration. The getBeforeMigrationScripts will make sure to include them.

Other​

A messaging counter is provided for reliable message retries in case of fatal errors. It can be initialized with const counter = initMessagingCounter(getOwnerPgPool(app));. You can provide it then in the setupMessagingBroker call.