How to use the shadow database in Prisma

The "shadow database" concept in Prisma is primarily used during migrations to ensure that your database schema changes are safe. It acts as a temporary database where Prisma tests out the migrations before they are applied to the main database.

This allows for a safer migration process because any potential issues are caught on the shadow database, rather than the main one.

We’ve put together this tutorial to show you how it works.

What is Prisma?

Prisma is an open-source database toolkit that aims to make working with databases easier and more productive for developers. At its core, Prisma comprises:

1. Prisma Client: A type-safe and auto-generated database client that lets developers query their databases in a more intuitive way using TypeScript or JavaScript. It replaces traditional ORMs and removes the need to write SQL in many cases.
2. Prisma Migrate: A declarative data modeling and migration system. With Prisma Migrate, you can define your database schema using the Prisma Schema Language (PSL), and then generate and execute SQL migration scripts against your database.
3. Prisma Studio: A modern and powerful GUI to view and edit data in your database. It provides a user-friendly interface to interact with your data without writing SQL queries.

Why use Prisma?

• Type-Safety: With Prisma Client, you get auto-generated and type-safe database queries, reducing runtime errors and enhancing the developer experience.
• Declarative Schema: Define your database schema in a human-readable format with the Prisma Schema Language.
• Ease of Use: From easy setup to its intuitive querying system, Prisma streamlines database workflows.
• Flexibility: Prisma supports multiple databases like PostgreSQL, MySQL, SQLite, and SQL Server, allowing for flexible deployments.
• Active Community: Being open-source, Prisma has a vibrant community that contributes to its development and offers support.

Now that we've introduced Prisma, let's dive into one of its features: the shadow database.

The shadow database in Prisma

What is the shadow database?

Before diving into how to use it, you should know that Prisma uses the shadow database automatically during the migration process. Prisma connects to this database, runs the migrations, and then throws it away. If the migrations are successful on the shadow database, they are likely to also be successful on your main database.

Setting up the environment

To enable the use of a shadow database, your database user must have permissions to:

• Create databases
• Create tables
• Alter tables

In many production environments, your main database user might not have these permissions for security reasons. If that's the case, Prisma allows you to define a special database user just for the migrations.

Configuring a separate database user for migrations

Modify your schema.prisma file by adding a migrations field in the datasource block:

datasource db {
  provider   = "postgresql"
  url        = env("DATABASE_URL")
  migrations = env("DATABASE_MIGRATIONS_URL")
}

Now, in your .env file (or your environment configuration), set the DATABASE_MIGRATIONS_URL to the connection string of your migration-specific user.

DATABASE_URL=postgresql://main_user:password@localhost:5432/mydb
DATABASE_MIGRATIONS_URL=postgresql://migrations_user:password@localhost:5432/mydb

Running migrations

When you're ready to create a migration, use the following command:

prisma migrate dev --name your_migration_name

This will:

• Generate SQL migration files in the prisma/migrations directory
• Apply these migrations first to the shadow database
• If successful, apply them to your main database

Check migration status

You can check the status of your migrations using:

prisma migrate status

Production migrations

In a production setting, you typically use:

prisma migrate deploy

This command also uses the shadow database (if permissions allow) to test out migrations before they're applied to the main database.

Handling errors

If a migration fails on the shadow database, Prisma will provide error messages indicating what went wrong. This helps you catch potential issues early on, and you can then make the necessary adjustments to your migration files or schema before trying again.

Best practices

• Always backup your production database before running migrations, even if you're using a shadow database.
• Ensure that your migrations user has the correct permissions but is not overly privileged.
• Apart from relying on the shadow database, consider having staging or development databases where migrations can be tested before applying them to production.

Conclusion

The shadow database feature in Prisma provides an extra layer of safety during database migrations. It ensures that your migrations are tested on a temporary database before being applied to the main one. By combining this with best practices like backups and thorough testing, you can reduce the risks associated with database migrations.