How to use the shadow database in Prisma
October 24, 2023
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:
- 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.
- 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
schema.prisma file by adding a
migrations field in the
Now, in your
.env file (or your environment configuration), set the
DATABASE_MIGRATIONS_URL to the connection string of your migration-specific user.
When you're ready to create a migration, use the following command:
- Generate SQL migration files in the
- 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:
In a production setting, you typically use:
This command also uses the shadow database (if permissions allow) to test out migrations before they're applied to the main database.
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.
- 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.
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.
How to automate Prisma migrations in a CI/CD pipeline
How to implement soft deletes in Prisma
How to reset and seed a Prisma database
UUID vs GUID vs CUID vs NanoID: A guide to database primary keys
How to generate UUIDs in Prisma
How to squash migrations in Prisma