Skip to main content

Integrating Atlas with an existing Prisma project

This guide explains how to configure Atlas to automatically plan migrations for your existing Prisma project and manage untracked objects using Atlas.

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