Skip to main content

Setting up Prisma with Atlas

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.

Setting up Prisma with Atlas​

This guide explains how to configure Atlas to automatically plan migrations for your Prisma project. It is ideal for those starting a new Prisma project and using Atlas as the initial migration planning tool.

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.