Skip to main content

Automatic migration planning for Prisma

TL;DR​

  • Prisma is a popular, open-source ORM for TypeScript and Node.js.
  • Atlas is a database schema management tool, based on modern DevOps principles.
  • Developers using Prisma to build their applications can use Atlas to manage their database schema, covering advanced use cases not supported by Prisma's native migrate command.

Automatic migration planning for Prisma​

Prisma is an open-source ORM for TypeScript and Node.js. It allows developers to define their database schema using a declarative language and then generate TypeScript code that can be used to interact with the database.

Prisma boasts one of the best migration solutions of all ORMs today, prisma migrate, with features like automatic migration planning and versioned migrations.

However, as a migration tool focused on solving for the specific needs of an ORM, Prisma's migrate does not cover all the use cases that a general-purpose schema management tool like Atlas can. ORMs, by design, are meant to be used as an abstraction layer between the application and the database, and as such, tend to focus on the things that are common across all databases (tables, columns, foreign keys, basic index types, etc.)

By integrating with Atlas, Prisma users can leverage features such as:

FeatureDescription
Database FeaturesAutomatic migration planning for advanced database objects such as Views, Stored Procedures, Triggers, Row Level Security, etc.
Continuous IntegrationCatch issues before they hit production with robust GitHub Actions, GitLab, and CircleCI Orbs integrations. Detect risky migrations, test data migrations, database functions, and more.
Continuous DeliveryAtlas can be integrated into your pipelines to provide native integrations with your deployment machinery (e.g. Kubernetes Operator, Terraform, etc.)
Schema MonitoringAtlas can monitor your database schema and alert you when it drifts away from its expected state.

How to Atlas + Prisma​

Atlas integrates seamlessly with Prisma using the external_schema data source. This allows you to use your existing Prisma schema as the source schema for Atlas, enabling you utilize all Atlas features for managing your database.

Atlas replaces the prisma migrate command, providing you with a dedicated tool for schema changes.

If you need to extend your database schema beyond what Prisma supports, Atlas can help you manage these objects using the composite_schema data source which enables you to compose database schemas from multiple sources.

When to use Atlas instead of prisma migrate​

As mentioned above, Prisma's migrate command is a great tool for many use cases. However, there are some scenarios where you may want to consider using Atlas instead:

  1. You are building a Platform. If you are building an Internal Developer Platform (IDP) for your company and need to support "Paved Paths" for multiple ORMs and programming languages, Atlas can provide a consistent way to manage database schemas across all your projects.
  2. You need robust CI/CD. If you need to ensure that your database schema changes are tested and linted before they are applied to production or want to natively integrate migrations into your CD machinery (e.g. Kubernetes, Terraform, GitHub Actions, ArgoCD, FluxCD, etc.), it is useful to choose a tool that has all of these features built-in.
  3. You need to manage advanced database objects. If you need to manage advanced database objects such as Views, Stored Procedures, Triggers, Row Level Security, etc., Atlas provides a way to manage these objects in a declarative way.

Configuring Atlas with a New Prisma Project​

In this guide, we will show you how to configure Atlas to automatically plan migrations for your Prisma project.

Installation Atlas & Prisma CLI​

To download and install the latest release of the Atlas CLI, simply run the following in your terminal:

curl -sSf https://atlasgo.sh | sh

To install the Prisma CLI, run the following command:

npm install prisma --save-dev

New Prisma Project​

We will create a new Prisma project with a PostgreSQL database for this guide:

npx prisma init --datasource-provider postgresql

After running the command you will see the following project structure:

my-prisma-project
├── prisma
│   ├── schema.prisma
├── .gitignore
└── .env

Configuring Atlas to use Prisma schema​

To configure Atlas to use your Prisma schema, you need to export the schema to SQL format. Atlas supports the external_schema data source, which allows you to run an external program to generate the schema.

Let's create atlas.hcl in your project root folder:

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"
  }
}

In the above configuration, we utilize the Prisma Migrate engine command to generate a full DDL, from the schema.prisma file by comparing the current schema with an empty state. The generated Data Definition Language (DDL) will be used as the source schema for Atlas.

Add a new model to the Prisma schema​

Let's add a new model to the schema.prisma file:

prisma/schema.prisma
model User {
  id    Int    @id @default(autoincrement())
  name  String
  email String @unique
}

Next, let's verify Atlas is able to read the schema by running the following command:

 atlas schema inspect --env local --url env://schema.src --format "{{ sql . }}"

This command reads the desired schema from the schema.src URL and outputs the SQL representation of the schema:

-- Create "User" table
CREATE TABLE "User" ("id" serial NOT NULL, "name" text NOT NULL, "email" text NOT NULL, PRIMARY KEY ("id"));
-- Create index "User_email_key" to table: "User"
CREATE UNIQUE INDEX "User_email_key" ON "User" ("email");

Applying the schema to a target database​

Next, let's show how to apply the schema to a target database using Atlas.

First, create a PostgreSQL development database with Docker:

docker run --name postgres -e POSTGRES_PASSWORD=postgres -p 5432:5432 -d postgres:latest

Then, run the following command to apply the schema to the development database:

atlas schema apply --env local --url "postgresql://postgres:postgres@:5432/postgres?search_path=public&sslmode=disable"

Atlas will load the desired state of the database schema from the schema.prisma file and compare it with the current state of the database. Next, Atlas will generate a migration plan and prompt you for approval:

Planning migration statements (2 in total):

  -- create "user" table:
    -> CREATE TABLE "User" (
         "id" serial NOT NULL,
         "name" text NOT NULL,
         "email" text NOT NULL,
         PRIMARY KEY ("id")
       );
  -- create index "user_email_key" to table: "user":
    -> CREATE UNIQUE INDEX "User_email_key" ON "User" ("email");

-------------------------------------------

Analyzing planned statements (2 in total):

  -- no diagnostics found

  -------------------------
  -- 47.402834ms
  -- 2 schema changes

-------------------------------------------

? Approve or abort the plan:
  ▸ Approve and apply
    Abort

After approving the plan, Atlas will apply the migration to the target database:

Applying approved migration (2 statements in total):

  -- create "user" table
    -> CREATE TABLE "User" (
         "id" serial NOT NULL,
         "name" text NOT NULL,
         "email" text NOT NULL,
         PRIMARY KEY ("id")
       );
  -- ok (6.179167ms)

  -- create index "user_email_key" to table: "user"
    -> CREATE UNIQUE INDEX "User_email_key" ON "User" ("email");
  -- ok (1.637ms)

  -------------------------
  -- 7.913583ms
  -- 1 migration
  -- 2 sql statements

Let's try to re-run the command to apply the schema to the target database:

atlas schema apply --env local --url "postgresql://postgres:postgres@:5432/postgres?search_path=public&sslmode=disable"

As expected, Atlas detects that the schema is already up-to-date and does not apply any changes:

Schema is synced, no changes to be made

This flow (schema apply) is called the "Declarative Workflow" in Atlas, and you can learn more about it here.

Running Atlas to plan migrations​

Alternatively, Atlas can be used to drive a more traditional "Versioned Workflow" for managing database schema changes.

In the context of Prisma, this means you can run Atlas to plan migrations instead of using the prisma migrate command.

Based on our existing setup from the previous steps, let's use the atlas migrate diff command to generate a migration plan:

atlas migrate diff --env local

This command will generate migration plans in the atlas/migrations folder:

my-prisma-project
├── prisma
│   ├── schema.prisma
├── atlas
|   ├── migrations
│   │   ├── 20241017062735.sql
│   │   ├── atlas.sum
├── atlas.hcl
├── .gitignore
└── .env

20241017062735.sql contains the migration plan for the new User model:

20241017062735.sql
-- Create "User" table
CREATE TABLE "User" ("id" serial NOT NULL, "name" text NOT NULL, "email" text NOT NULL, PRIMARY KEY ("id"));
-- Create index "User_email_key" to table: "User"
CREATE UNIQUE INDEX "User_email_key" ON "User" ("email");

Amazing! Atlas automatically generated a migration file that will create the User table in our database.

To apply the migrations against our local database, let's first clean up the existing database:

atlas schema clean --env local --url "postgresql://postgres:postgres@:5432/postgres?search_path=public&sslmode=disable"

Atlas suggests dropping some resources:

Planning migration statements (1 in total):

  -- drop "user" table:
    -> DROP TABLE "User";

-------------------------------------------

? Approve or abort the plan:
  ▸ Approve and apply
    Abort

After approving and applying the plan, we are left with an empty database.

Now, we can finally apply the migrations to our local database:

atlas migrate apply --env local --url "postgresql://postgres:postgres@:5432/postgres?search_path=public&sslmode=disable"

Atlas prints the following output:

Migrating to version 20241126082831 (1 migrations in total):

  -- migrating version 20241126082831
    -> CREATE TABLE "User" ("id" serial NOT NULL, "name" text NOT NULL, "email" text NOT NULL, PRIMARY KEY ("id"));
    -> CREATE UNIQUE INDEX "User_email_key" ON "User" ("email");
  -- ok (2.942834ms)

  -------------------------
  -- 29.386709ms
  -- 1 migration
  -- 2 sql statements

This flow (migrate apply) is called the "Versioned Workflow" in Atlas, and you can learn more about it here.

Configuring Atlas with an existing Prisma project​

Installation Atlas & Prisma CLI​

To download and install the latest release of the Atlas CLI, simply run the following in your terminal:

curl -sSf https://atlasgo.sh | sh

To install the Prisma CLI, run the following command:

npm install prisma --save-dev

If you have an existing Prisma project with a migrations folder already applied to the target database, you can move your project to using Atlas with a few adjustments.

In this section, we demonstrate how to replace prisma migrate with Atlas for managing your database schema.

Create an example Prisma project​

To mimick the scenario of an existing project, let's create a new Prisma project with PostgreSQL database, with an existing User model in the target database.

Run this command to create a new Prisma project:

npx prisma init --datasource-provider postgresql

Add User model to the schema.prisma file:

prisma/schema.prisma
model User {
  id Int @id @default(autoincrement())
  name String
  email String @unique
}

Create a PostgreSQL development database with Docker:

docker run --name postgres -e POSTGRES_PASSWORD=postgres -p 5432:5432 -d postgres:16

Run this command to generate the first migration and apply it to the development database:

DATABASE_URL=postgresql://postgres:postgres@localhost:5432/postgres npx prisma migrate dev --name init

The project structure should look like this:

my-prisma-project
├── prisma
|   ├── migrations
|   |   ├── 20241017062735_init
|   |   |   ├── migration.sql
|   |   ├── migration_lock.toml
│   ├── schema.prisma
├── .gitignore
└── .env

Configuring Atlas to use Prisma schema​

Now that we have an existing Prisma project, let's see how we can use Atlas to replace the prisma migrate command.

At the project root folder, create atlas.hcl file:

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"
  }
}

Running Atlas to plan migrations​

Run this command to plan migrations with Atlas:

atlas migrate diff --env local

This command will generate a migration plans in atlas/migrations folder:

my-prisma-project
├── prisma
|   ├── migrations
|   |   ├── 20241017062735_init
|   |   |   ├── migration.sql
|   |   ├── migration_lock.toml
│   ├── schema.prisma
├── atlas
|   ├── migrations
│   │   ├── 20241018044955.sql
│   │   ├── atlas.sum
├── atlas.hcl
├── .gitignore
└── .env

20241018044955.sql contains the migration plan for the new User model:

20241018044955.sql
-- Create "User" table
CREATE TABLE "User" ("id" serial NOT NULL, "name" text NOT NULL, "email" text NOT NULL, PRIMARY KEY ("id"));
-- Create index "User_email_key" to table: "User"
CREATE UNIQUE INDEX "User_email_key" ON "User" ("email");

Now, to ensure that Atlas works well when applying the migration to the target database, we have the development database already at the latest state, so we can reuse it for applying.

atlas migrate apply --env local --url "postgresql://postgres:postgres@localhost:5432/postgres?search_path=public&sslmode=disable"

The expected output will look like this:

Error: sql/migrate: connected database is not clean: found table "User" in schema "public". baseline version or allow-dirty is required

This error occurs because Prisma migrations have already been applied to the target database.

 Schema |        Name        | Type  |  Owner
--------+--------------------+-------+----------
 public | User               | table | postgres
 public | _prisma_migrations | table | postgres

Let's edit our atlas.hcl file to exclude the _prisma_migrations table from the schema:

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"]
  }
}

With this configuration, the _prisma_migrations table will be excluded from the schema. Then we need to set the baseline version to the latest migration version to avoid duplicate migrations being applied to the database

atlas migrate apply --env local --url "postgresql://postgres:postgres@localhost:5432/postgres?search_path=public&sslmode=disable" --baseline 20241018044955
note

Adjust the version number 20241018044955 to match your setup.

the output should be like this:

No migration files to execute
 Schema |        Name            | Type  |  Owner
--------+------------------------+-------+----------
 public | User                   | table | postgres
 public | _prisma_migrations     | table | postgres
 public | atlas_schema_revisions | table | postgres

Great. Now you have successfully replaced the prisma migrate command with Atlas for managing your database schema.

To plan a new change, modify the schema.prisma file, run atlas migrate diff --env local to generate a new migration plan, and then apply the migration with atlas migrate apply --env local.

Manage untracked objects with Atlas​

In some cases, you might have untracked objects in the database that are not managed by Prisma. Like custom DDL, functions, triggers, etc. which are not part of the Prisma schema. To manage these untracked objects, you can use Atlas to inspect them and convert them into the Atlas schema.

To demonstrate this, let's manually add a function to the development database that we used for previous steps.

Run by docker command to connect to the development database:

docker exec -it postgres psql -U postgres -d postgres

Then run:

CREATE OR REPLACE FUNCTION public.echo(text) RETURNS text AS $$
    SELECT $1;
$$ LANGUAGE SQL;

At this point, we have a function in the database that is not managed by Prisma. The idea is to use atlas schema diff command to compare the target database with our external schema.

Ok, let's create an atlas/prisma_objects.sql file to store these untracked objects, and run the command below to inspect these objects into the file:

atlas schema diff \
  --env local \
  --from "file://atlas/migrations" \
  --to "postgresql://postgres:postgres@localhost:5432/postgres?search_path=public&sslmode=disable" \
  --exclude "_prisma_migrations" \
  --exclude "atlas_schema_revisions" > atlas/prisma_objects.sql

The atlas/prisma_objects.sql file should contain the function that is not managed by Prisma:

atlas/prisma_objects.sql
-- Create "echo" function
CREATE FUNCTION "echo" (text) RETURNS text LANGUAGE sql AS $$ SELECT $1; $$;

After that, edit the atlas.hcl file to include the atlas/prisma_objects.sql with composite schemas

atlas.hcl
data "external_schema" "prisma" {
    program = [ 
      "npx",
      "prisma",
      "migrate",
      "diff",
      "--from-empty",
      "--to-schema-datamodel",
      "prisma/schema.prisma",
      "--script"
    ]
}

data "composite_schema" "prisma-objects" {
  schema "public" {
    url = data.external_schema.prisma.url
  }
  schema "public" {
    url = "file://atlas/prisma_objects.sql"
  }
}

...

Then replace the src with the composite_schema:

atlas.hcl
...

env "local" {
  dev = "docker://postgres/16/dev?search_path=public"
  schema {
    src = data.composite_schema.prisma-objects.url
  }
  migration {
    dir = "file://atlas/migrations"
  }
}

Run this command to plan migrations with Atlas:

atlas migrate diff --env local

The output should be like this:

my-prisma-project
├── prisma
|   ├── migrations
|   |   ├── 20241017062735_init
|   |   |   ├── migration.sql
|   |   ├── migration_lock.toml
│   ├── schema.prisma
├── atlas
|   ├── migrations
│   │   ├── 20241018044955.sql
│   │   ├── 20241018071458.sql
│   │   ├── atlas.sum
├── atlas.hcl
├── .gitignore
└── .env

Congratulations! You are now successfully managing both the Prisma schema and custom DDL with Atlas.

atlas/migrations/20241018071458.sql
-- Create "echo" function
CREATE FUNCTION "echo" (text) RETURNS text LANGUAGE sql AS $$ SELECT $1; $$;

In the last step, set the baseline version to the latest migration version, to avoid applying duplicates to the database:

atlas migrate apply \
  --env local \
  --url "postgresql://postgres:postgres@localhost:5432/postgres?search_path=public&sslmode=disable" \
  --baseline 20241018071458
note

Since the baseline version can be set only once, if you have already set the database to a different baseline version (as described in the section above), you can use the atlas migrate set command instead:

atlas migrate set 20241018071458 \
  --env local \
  --url "postgresql://postgres:postgres@localhost:5432/postgres?search_path=public&sslmode=disable"

This command sets the database to version 20241018071458. Hence, only migration files with versions higher than this will be applied to the database. This is intended, as these objects are already in the database.

The code for this tutorial is available under providers/prisma

Extending Prisma schema with composite_schema​

Prisma .schema files do not support many database objects like FUNCTION, TRIGGER, MATERIALIZED VIEW, etc.

With Atlas, you can extend the Prisma schema by using composite_schema to include these objects in the Atlas schema.

To illustrate this, see the configuration below:

atlas.hcl
data "external_schema" "prisma" {
    program = [ 
      "npx",
      "prisma",
      "migrate",
      "diff",
      "--from-empty",
      "--to-schema-datamodel",
      "prisma/schema.prisma",
      "--script"
    ]
}

data "composite_schema" "prisma-extended" {
  schema "public" {
    url = data.external_schema.prisma.url
  }
  schema "public" {
    url = "file://path/to/schema.hcl"
  }
  schema "public" {
    url = "file://path/to/schema.sql"
  }
}

env "local" {
  dev = "docker://postgres/16/dev?search_path=public"
  schema {
    src = data.composite_schema.prisma-extended.url
  }
  migration {
    dir = "file://atlas/migrations"
  }
}

Wrapping up​

In this guide, we've outlined the steps to configure Atlas for automatic migration planning in your Prisma project. We covered how to utilize the Prisma migration engine to generate a complete DDL schema, which can then be used in Atlas to plan migrations.

Additionally, we explained how to manage untracked objects in the database and extend the Prisma schema with composite_schema

Have questions? Feedback? Find our team on our Discord server.