SQL

Nitric provides functionality for provisioning and interacting with SQL databases.

Enabling SQL databases

SQL databases are currently in Preview. To enable this feature in your project add the following to your nitric.yaml file

preview:
  - sql-databases

Creating a SQL database

Here's an example of how to create a SQL database:

import { sql } from '@nitric/sdk'

const db = sql('my-database')

Accessing a SQL database

Unlike some Nitric resources, which abstract away the underlying service to enable portability, SQL databases are already portable. This means Nitric can allow you to interact with databases directly, freeing you to use the database client of your choice.

Nitric provides a helper function to retrieve the connection details for your SQL database:

import { sql, api } from '@nitric/sdk'

const db = sql('my-database')

api('example').get('/hello', async (ctx) => {
  const connStr = await db.connectionString()
  // TODO: Interact with the database using the connection string
  return ctx
})

Schemas and Migrations

Nitric provides mechanisms that allow you to run migrations against your database, ensuring the schema is up to date with your application. This functionality is designed to be as flexible as possible, allowing you to define your migrations in a variety of ways, including SQL files, other scripts, or even code.

There are two primary ways to run migrations:

• SQL files
• As a one-time task in a docker container

SQL files

Nitric supports running SQL files as migrations. To do this, create a directory in your project, usually migrations/[database_name] by convention, and add your SQL files to it. These files should follow the naming convention [version]_[name].[up/down].sql, where [version] indicates the order in which the migrations should be run, [name] is a descriptive name for the migration and [up/down] indicates whether to run the migration on deployment (up) or whether it's for rollbacks (down). Here's an example of a directory structure:

migrations/
  my-database/
    1_create_table.up.sql
    2_add_column.up.sql
    3_create_index.up.sql

Here is another example of a directory structure with both up and down migrations:

migrations/
  my-database/
    20240424002208_init.up.sql
    20240424002208_init.down.sql
    20240426001202_add_column.up.sql
    20240426001202_add_column.down.sql
    20240426001203_create_index.up.sql
    20240426001203_create_index.down.sql

Next, configure your database to use these migration files, ensuring they're automatically applied when you deploy your project.

import { sql } from '@nitric/sdk'

const db = sql('my-database', {
  migrations: 'file://migrations/my-database',
})

When new migration files are added Nitric will automatically apply them when you redeploy your project.

Docker container

Nitric also provides a more powerful option for migrations - running them as a once-per-deployment task using a docker container. This is useful when you need to run more complex migrations or you're using tools such as an ORM which can't generate SQL migration files. To do this, create a Dockerfile in your project that runs your migrations and add it to your database configuration.

import { sql } from '@nitric/sdk'

const db = sql('my-database', {
  migrations: 'dockerfile://migrations.dockerfile',
})

The Dockerfile can operate as you see fit, but it must contain an entrypoint that runs your migrations and terminates when it's done. Here's an example of a Dockerfile that runs a migration script using Prisma and Node:

FROM node

ENV DB_URL=""
COPY package*.json ./
RUN npm install
COPY . .

# Build the Prisma client
RUN npx prisma generate

# Run the migrations and start the application when the Docker container starts
# We define the entrypoint like this so we can correctly copy it out when running it
# in the various clouds (e.g. AWS Codebuild does not respect ENTRYPOINT and CMD)
ENTRYPOINT ["sh", "-c", "npx prisma migrate deploy"]

Alternatives

While Nitric's SQL database support is a great option if you prefer to host your database alongside your other Nitric resources, there are many other great serverless or managed options available for data persistence, some of which offer unique features or true serverless pricing.

Here are a few alternatives that work well with Nitric:

• Supabase (PostgreSQL)
• Neon (PostgreSQL)
• PlanetScale (MySQL)

Guides are available for some of these alternatives in the guides section.