apps/blog/content/blog/advanced-database-schema-management-with-atlas-and-prisma-orm/index.mdx
Atlas is a powerful migration tool that enables advanced database schema management workflows, like CI/CD, schema monitoring, versioning, and more. Learn how to use Atlas together with Prisma ORM and make use of Atlas' capabilities by using low-level database features.
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.
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:
To successfully complete this guide, you need:
prisma and @prisma/client packages installed)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.
To kick off this tutorial, first install the Atlas CLI:
-sSf https://atlasgo.sh | sh
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:
touch atlas.hcl
Now, add the following code it:
// atlas.hcl
data "external_schema" "prisma" {
program = [
"npx",
"prisma",
"migrate",
"diff",
"--from-empty",
"--to-schema-datamodel",
"prisma/schema.prisma",
"--script"
]
}
env "local" {
dev = "docker://postgres/16/dev?search_path=public"
schema {
src = data.external_schema.prisma.url
}
migration {
dir = "file://atlas/migrations"
exclude = ["_prisma_migrations"]
}
}
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:
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.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:
atlas migrate diff --env local
This command:
local environment and generates SQL migration files based on the external_schema defined in your Atlas schema.atlas/migrations folder and puts the SQL migration in there.After running it, your folder structure should look similar to this:
.
├── README.md
├── atlas
│ └── migrations
│ ├── 20241210094213.sql
│ └── atlas.sum
├── atlas.hcl
├── prisma
│ ├── migrations
│ │ ├── 20241210092000_init
│ │ │ └── migration.sql
│ │ └── migration_lock.toml
│ └── schema.prisma
├── src
└── ...
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:
atlas migrate apply \
--env local \
--url __DATABASE_URL__ \
--baseline __TIMESTAMP__
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:
atlas migrate apply \
--env local \
--url "postgresql://johndoe:mypassword42@localhost:5432/example-db?search_path=public&sslmode=disable" \
--baseline 20241210094213
The command output will say the following:
No migration files to execute
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-1branch of the example repo.
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:
atlas migrate diff to create migration filesatlas migrate apply to execute the migration files against your databaseprisma generate to update your Prisma ClientFor 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:
model User {
id Int @id @default(autoincrement())
email String @unique
name String?
posts Post[]
}
model Post {
id Int @id @default(autoincrement())
title String
content String?
published Boolean @default(false)
author User? @relation(fields: [authorId], references: [id])
authorId Int?
+ tags Tag[]
}
+ model Tag {
+ id Int @id @default(autoincrement())
+ name String @unique
+ posts Post[]
+ }
With that change in place, now run the command to create the migration files on your machine:
atlas migrate diff --env local
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:
-- Create "Tag" table
CREATE TABLE "Tag" ("id" serial NOT NULL, "name" text NOT NULL, PRIMARY KEY ("id"));
-- Create index "Tag_name_key" to table: "Tag"
CREATE UNIQUE INDEX "Tag_name_key" ON "Tag" ("name");
-- Create "_PostToTag" table
CREATE TABLE "_PostToTag" ("A" integer NOT NULL, "B" integer NOT NULL, CONSTRAINT "_PostToTag_A_fkey" FOREIGN KEY ("A") REFERENCES "Post" ("id") ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT "_PostToTag_B_fkey" FOREIGN KEY ("B") REFERENCES "Tag" ("id") ON UPDATE CASCADE ON DELETE CASCADE);
-- Create index "_PostToTag_AB_unique" to table: "_PostToTag"
CREATE UNIQUE INDEX "_PostToTag_AB_unique" ON "_PostToTag" ("A", "B");
-- Create index "_PostToTag_B_index" to table: "_PostToTag"
CREATE INDEX "_PostToTag_B_index" ON "_PostToTag" ("B");
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):
atlas migrate apply \
--env local \
--url __DATABASE_URL__ \
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:
npx prisma generate
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.:
const tag = await prisma.tag.create({
data: {
name: "Technology",
posts: {
create: { title: "Prisma and Atlas are a killer combo!" }
}
}
})
Your project should now be in a state looking similar to the
step-2branch of the example repo.
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:
atlas directory that reflects the desired changeatlas.hcl to include that SQL file so that Atlas is aware of itatlas migrate diff to create migration filesatlas migrate apply to execute the migration files against your databaseThis 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:
touch atlas/published_posts_index.sql
Then, add the following code to it:
CREATE INDEX "idx_published_posts"
ON "Post" ("id")
WHERE "published" = true;
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.:
const publishedPosts = await prisma.post.findMany({
where: { published: true }
}
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:
data "external_schema" "prisma" {
program = [
"npx",
"prisma",
"migrate",
"diff",
"--from-empty",
"--to-schema-datamodel",
"prisma/schema.prisma",
"--script"
]
}
env "local" {
// dev = "postgresql://nikolasburk:nikolasburk@localhost:5432/atlas-prisma?sslmode=disable"
dev = "docker://postgres/13/dev?search_path=public"
schema {
+ src = data.composite_schema.prisma-extended.url
}
migration {
dir = "file://atlas/migrations"
exclude = ["_prisma_migrations"]
}
}
+ data "composite_schema" "prisma-extended" {
+ schema "public" {
+ url = data.external_schema.prisma.url
+ }
+ schema "public" {
+ url = "file://atlas/published_posts_index.sql"
+ }
+ }
Note that
composite_schemais only available via the Atlas Pro plan and requires you to be authenticated viaatlas login.
Atlas is now aware of the schema change, so you can go ahead and generate the migration files as before:
atlas migrate diff --env local
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):
atlas migrate apply \
--env local \
--url __DATABASE_URL__ \
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-3branch of the example repo.
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.