Advanced Database Schema Management with Atlas & Prisma ORM

Introduction

Atlas is a powerful data modeling and migrations tool that enables advanced database schema management workflows, like CI/CD integrations, schema monitoring, versioning, and more.

In this guide, you will learn how to make use of Atlas advanced schema management and migration workflows by replacing Prisma Migrate in an existing Prisma ORM project with it.

That way, you can still use Prisma ORM's intuitive data model and type-safe query capabilities while taking advantage of the enhanced migration capabilities provided by Atlas.

You can find the example repo for this tutorial on GitHub. The repo has branches that correspond to every step of this guide.

Why use Atlas instead of Prisma Migrate?

Prisma Migrate is a powerful migration tool that covers the majority of use cases application developers have when managing their database schemas. It provides workflows specifically designed for taking you from development to production and with team collaboration in mind.

However, for even more capabilities, you may use a dedicated tool like Atlas to supercharge your migration workflows in the following scenarios:

• Continuous Integration (CI): With Atlas, you can issues before they hit production with robust GitHub Actions, GitLab, and CircleCI Orbs integrations. You can also detect risky migrations, test data migrations, database functions, and more.
• Continuous Delivery (CD): Atlas can be integrated into your pipelines to provide native integrations with your deployment machinery (e.g. Kubernetes Operator, Terraform, etc.).
• Schema monitoring: Atlas can monitor your database schema and alert you when it drifts away from its expected state.
• Support for low-level database features: Automatic migration planning for advanced database objects such as Views, Stored Procedures, Triggers, Row Level Security, etc.

Prerequisites

To successfully complete this guide, you need:

• an existing Prisma ORM project (with the prisma and @prisma/client packages installed)
• a PostgreSQL database and its connection string
• Docker installed on your machine (to manage Atlas' ephemeral dev databases)

For the purpose of this guide, we'll assume that your Prisma schema contains the standard User and Post models that we use as main examples across our documentation. If you don't have a Prisma ORM project, you can use the orm/script example to follow this guide.

The starting point for this step is the start branch in the example repo.

Step 1: Add Atlas to existing Prisma ORM project

To kick off this tutorial, first install the Atlas CLI:

If you prefer a different installation method (like Docker or Homebrew), you can find it here.

Next, navigate into the root directory of your project that uses Prisma ORM and create the main Atlas schema file, called atlas.hcl:

Now, add the following code it:

To get syntax highlighting and other convenient features for the Atlas schema file, install the Atlas VS Code extension.

In the above snippet, you're doing two things:

• Define an external_schema called prisma via the data block: Atlas is able to integrate database schema definitions from various sources. In this case, the source is the SQL that's generated by the prisma migrate diff command which is specified via the program field.
• Specify details about your environment (called local) using the env block:
  • dev: Points to a shadow database (which is called dev database in Atlas). Similar to Prisma Migrate, Atlas also uses a shadow database to "dry-run" migrations. The connection you provide here is similar to the shadowDatabaseUrl in the Prisma schema. However, for convenience we're using Docker in this case to manage these ephemeral database instances.
  • schema: Points to the database connection URL of the database targeted by Prisma ORM (in most cases, this will be identical to the DATABASE_URL environment variable).
  • migration: Points to the directory on your file system where you want to store the Atlas migration files (similar to the prisma/migrations folder). Note that you're also excluding the _prisma_migrations from being tracked in Atlas' migration history.

In addition to the shadow database, Atlas' migration system and Prisma Migrate have another commonality: They both use a dedicated table in the database to track the history of applied migrations. In Prisma Migrate, this table is called _prisma_migrations. In Atlas, it's called atlas_schema_revisions.

In order to tell Atlas that the current state of your database (with all its existing tables and other database objects) should be the starting point for tracking migrations in your project, you need to do an initial baseline migration.

To do that, first run the following command to create Atlas' migration directory:

This command:

1. looks at the current state of your local environment and generates SQL migration files based on the external_schema defined in your Atlas schema.
2. creates the atlas/migrations folder and puts the SQL migration in there.

After running it, your folder structure should look similar to this:

At this point, Atlas hasn't done anything to your database yet — it only created files on your local machine.

Now, you need to apply the generated migrations to tell Atlas that this should be the beginning of its migration history. To do so, run the atlas migrate apply command but provide the --baseline __TIMESTAMP__ option to it this time.

Copy the timestamp from the filename that Atlas created inside atlas/migrations and use it to replace the __TIMESTAMP__ placeholder value in the next snippet. Similarly, replace the __DATABASE_URL__ placeholder with your database connection string:

Assuming the generated migration file is called 20241210094213.sql and your database is running at postgresql://johndoe:mypassword42@localhost:5432/example-db?search_path=public&sslmode=disable, the command should look as follows:

The command output will say the following:

If you inspect your database now, you'll see that the atlas_schema_revisions table has been created and contains two entries that specify the beginning of the Atlas migration history.

Your project should now be in a state looking similar to the step-1 branch of the example repo.

Step 2: Running a migration with Atlas

Next, you'll learn how to make edits to your Prisma schema and reflect the change in your database using Atlas migrations. On a high-level, the process will look as follows:

1. Make a change to the Prisma schema
2. Run atlas migrate diff to create migration files
3. Run atlas migrate apply to execute the migration files against your database
4. Run prisma generate to update your Prisma Client
5. Access the modified schema in your application code via Prisma Client

For the purpose of this tutorial, we're going to expand the Prisma schema with a Tag model that has a many-to-many relation to the Post model:

With that change in place, now run the command to create the migration files on your machine:

As before, this creates a new file inside the atlas/migrations folder, e.g. 20241210132739.sql, with the SQL code that reflects the change in your data model. In the case of our change above, it'll look like this:

Next, you can apply the migration with the same atlas migrate apply command as before, minus the --baseline option this time (remember to replace the __DATABASE_URL__ placeholder):

Your database schema is now updated, but your generated Prisma Client inside node_modules/@prisma/client isn't aware of the schema change yet. That's why you need to re-generate it using the Prisma CLI:

Now, you can go into your application code and run queries against the updated schema. In our case, that would be a query involving the new Tag model, e.g.:

Your project should now be in a state looking similar to the step-2 branch of the example repo.

Step 3: Add a partial index to the DB schema

In this section, you'll learn how you can expand your database schema with features that are not supported in the Prisma schema. As an example, we're going to use a partial index.

The workflow to achieve this looks as follows:

1. Create a SQL file inside the atlas directory that reflects the desired change
2. Update atlas.hcl to include that SQL file so that Atlas is aware of it
3. Run atlas migrate diff to create migration files
4. Run atlas migrate apply to execute the migration files against your database

This time, you won't need to re-generate Prisma Client because you didn't make any manual edits to the Prisma schema file.

Let's go and add a partial index!

First, create a file called published_posts_index.sql inside the atlas directory:

Then, add the following code to it:

This creates an index on Post records that have their published field set to true. This query is useful when you query for these published posts, e.g.:

You now need to adjust the atlas.hcl file to make sure it's aware of the new SQL snippet for the schema. You can do this by using the composite_schema approach. Adjust your atlas.hcl file as follows:

Note that composite_schema is only available via the Atlas Pro plan and requires you to be authenticated via atlas login.

Atlas is now aware of the schema change, so you can go ahead and generate the migration files as before:

You'll again see a new file inside the atlas/migrations directory. Go ahead and execute the migration with the same command as before (replacing __DATABASE_URL__ with your own connection string):

Congratulations! Your database is now updated with a partial index that will make your queries for published posts faster.

Your project should now be in a state looking similar to the step-3 branch of the example repo.

Conclusion

In this tutorial, you learned how to integrate Atlas into an existing Prisma ORM project. Atlas can be used to supercharge your schema management and migration workflows when using Prisma ORM.

Check out the example repo if you want to have a quick look at the final result of this tutorial.