Blog

PRODUCTS

KEYWORDS

Getting Started: Prisma and Doltgres

Nathan Gabrielson
Nathan Gabrielson
12 min read

We build Doltgres, the worlds first version-controlled PostgreSQL database. With Doltgres, you can branch, push or pull, merge, and diff your data in all the same ways Git lets you work with code.

Prisma is a popular Object Relational Mapper for TypeScript and NodeJS. It’s easy to use with many popular databases, such as MySQL and MongoDB, but also, of course, PostgreSQL. DoltgreSQL is PostgreSQL compatible, so we’re excited to add it to this list. Prisma has a couple of products, but for their ORM the primary features are Prisma Migrate and Prisma Client

This blog will be similar to the walkthrough we did for using Dolt and Prisma together in 2024, with some changes for differences between PostgreSQL and MySQL, as well as accounting for the major changes to Prisma’s handling of driver adapters & configuration options in Prisma 7.

Installation#

If you don’t have Doltgres installed already, you can grab the latest version on GitHub. For Prisma, all you’ll need is Node.js, which can be installed here, or by another method of your choice.

For this guide we’ll loosely be following Primsa’s quickstart guide for a new PostgreSQL (or compatible alternative) database. If you want to connect to an existing Doltgres instance, you’ll need to do an additional step and baseline your migrations. I’ll describe the process below, but you can read this article if you want a more in depth explanation. Finally, we’ll look over some ways you can use Doltgres’ versioning features with Prisma.

Set Up#

First we’ll need to have a Doltgres server. Run doltgres

% doltgres
INFO[0000] Creating root@localhost superuser            
INFO[0000] Server ready. Accepting connections.         
WARN[0000] secure_file_priv is set to "", which is insecure. 
WARN[0000] Any user with GRANT FILE privileges will be able to read any file which the sql-server process can read. 
WARN[0000] Please consider restarting the server with secure_file_priv set to a safe (or non-existent) directory.

Let’s create a new database. Use psql with the default information (username postgres with the highly secure password password)

% psql -U postgres
Password for user postgres:
psql (15.15 (Homebrew), server 15.17)
Type "help" for help.

postgres=> CREATE DATABASE prisma;
CREATE DATABASE

Quickstart#

If you don’t want to read the full guide, after doing the above steps you can clone this repo. Change the .env file to your database URL, then run the following.

% npx prisma migrate deploy
% npx prisma db seed

Your database should now have the tables and data for needed for the demo. To test the version control features, you’ll want to run the file dolt.ts which covers commits, branching, merging, status, and diffs, as well as mistake.ts which covers recovering lost data and tables.

% npx tsx dolt.ts
% npx tsx mistake.ts

If you want the longer walkthrough, continue forward.

Starting Prisma#

We’ll need a directory for our Prisma project

% mkdir first-prisma
% cd first-prisma

Now we can initialize our TypeScript project and install the required dependencies. We’ll run the following commands, and if you want explanation of each package, Prisma explains here

% npm init
... (I went with the default options)
% npm install typescript tsx @types/node --save-dev
added 8 packages, and audited 9 packages in 3s

2 packages are looking for funding
  run `npm fund` for details

found 0 vulnerabilities
% npx tsc --init

Created a new tsconfig.json
                                                                                                    TS
You can learn more at https://aka.ms/tsconfig
% npm install prisma @types/pg --save-dev
...
% npm install @prisma/client @prisma/adapter-pg pg dotenv
...

Now we’ll need to initialize our Prisma project. This does a couple of things:

  1. Creates a prisma.config.ts file where you can use prisma’s avaliable options. You don’t need to make changes for now.
  2. Creates the .env file where we’ll specify our database connection URL.
  3. Creates Prisma’s schema.prisma file. This contains all the “models” defined within our database, primarly tables, views, and their associated columns and indices.

Prisma’s schema file is how you access the table in your TypeScript code, which means the software offers two methods of usage. The first is to just modify the database directly, and then frequently pull down changes to keep the schema file up to date.

The alternative and intended method is to treat the schema.prisma as the ideal/final state, and use migrations to update your database. This is the pattern we’ll follow. The below command sets up your prisma workspace.

% npx prisma init --datasource-provider postgresql --output ../generated/prisma

Now we’ll want to connect Prisma. There should be a .env file already created with a placeholder value for DATABASE_URL. Set it to the following:

DATABASE_URL="postgresql://postgres:password@localhost:5432/prisma?schema=public"

If you used anything other than the values I’ve set up so far, make sure you use that instead. With that, all of the set up is out of the way! Let’s talk briefly about using an existing Doltgres database, then use Prisma.

Baselining#

If you have existing tables in your database, you’ll need to create an initial migration for Prisma to have an initial setup to work from. Create a migration directory beginning with 0_ so that it is applied first in lexicographic order, then mark it as already resolved.

If you want to see and/or review the sql generated before resolving the migration, a file will have been generated in /prisma/migrations/0_init/migration.sql after the diff command is run, which you can modify if you so choose.

% mkdir -p prisma/migrations/0_init
% npx prisma migrate diff --from-empty --to-schema prisma/schema.prisma --script > prisma/migrations/0_init/migration.sql
Loaded Prisma config from prisma.config.ts.
% npx prisma migrate resolve --applied 0_init
Loaded Prisma config from prisma.config.ts.

Prisma schema loaded from prisma/schema.prisma.
Datasource "db": PostgreSQL database "first", schema "public" at "localhost:5432"

Migration 0_init marked as applied.

Prisma offers an explanation of the baselining process if you’re curious.

Defining the Schema & Running Migrations#

Let’s add the following models to our prisma/schema.prisma file:

model Employee {
  emp_no     Int       @id
  first_name String    @db.VarChar(14)
  last_name  String    @db.VarChar(16)
  dept_emps  DeptEmp[]

  @@map("employees")
}

model Department {
  dept_no   String   @id @db.Char(4)
  dept_name String   @db.VarChar(40) @unique
  dept_emps DeptEmp[]

  @@map("departments")
}

model DeptEmp {
  emp_no     Int
  dept_no    String     @db.Char(4)
  from_date  DateTime
  to_date    DateTime
  employee   Employee   @relation(fields: [emp_no], references: [emp_no], onDelete: Cascade)
  department Department @relation(fields: [dept_no], references: [dept_no], onDelete: Cascade)

  @@id([emp_no, dept_no])
  @@map("dept_emp")
}

To add these to the database we run:

npx prisma migrate dev --name init

Let’s see if the tables were created. In psql:

prisma=> \dt
               List of relations
 Schema |        Name        | Type  |  Owner
--------+--------------------+-------+----------
 public | _prisma_migrations | table | postgres
 public | departments        | table | postgres
 public | dept_emp           | table | postgres
 public | employees          | table | postgres
(4 rows)

prisma=> show create table departments;
    Table    |                    Create Table
-------------+----------------------------------------------------
 departments | CREATE TABLE "departments" (                      +
             |   "dept_no" bpchar(4) NOT NULL,                   +
             |   "dept_name" varchar(40) NOT NULL,               +
             |   PRIMARY KEY ("dept_no"),                        +
             |   UNIQUE "departments_dept_name_key" ("dept_name")+
             | )
(1 row)

Sweet! They’re all there. Let’s add some data.

Seeding#

Prisma offers a seeding tool which allows you to easily add and re-add data to a database in development. Let’s create the file prisma/seed.ts which will add several employees.

import "dotenv/config";
import { Pool } from "pg";
import { PrismaPg } from "@prisma/adapter-pg";
import { PrismaClient } from "../generated/prisma/client";

const connectionString = `${process.env.DATABASE_URL}`;
const pool = new Pool({ connectionString });
const adapter = new PrismaPg(pool);
const prisma = new PrismaClient({ adapter });

async function main() {
  // Upsert Departments
  const engineering = await prisma.department.upsert({
    where: { dept_no: "d001" },
    update: {},
    create: {
      dept_no: "d001",
      dept_name: "Engineering",
    },
  });

  const sales = await prisma.department.upsert({
    where: { dept_no: "d002" },
    update: {},
    create: {
      dept_no: "d002",
      dept_name: "Sales",
    },
  });

  // Upsert Employees
  const tim = await prisma.employee.upsert({
    where: { emp_no: 1 },
    update: {},
    create: {
      emp_no: 1,
      first_name: "Tim",
      last_name: "Sehn",
      dept_emps: {
        create: [
          {
            dept_no: engineering.dept_no,
            from_date: new Date("2018-08-06"),
            to_date: new Date("9999-01-01"),
          },
          {
            dept_no: sales.dept_no,
            from_date: new Date("2018-08-06"),
            to_date: new Date("9999-01-01"),
          },
        ],
      },
    },
  });

  const brian_f = await prisma.employee.upsert({
    where: { emp_no: 2 },
    update: {},
    create: {
      emp_no: 2,
      first_name: "Brian",
      last_name: "Fitzgerald",
      dept_emps: {
        create: [
          {
            dept_no: sales.dept_no,
            from_date: new Date("2018-08-06"),
            to_date: new Date("9999-01-01"),
          },
        ],
      },
    },
  });

  const aaron = await prisma.employee.upsert({
    where: { emp_no: 3 },
    update: {},
    create: {
      emp_no: 3,
      first_name: "Aaron",
      last_name: "Son",
      dept_emps: {
        create: [
          {
            dept_no: engineering.dept_no,
            from_date: new Date("2018-08-06"),
            to_date: new Date("9999-01-01"),
          },
        ],
      },
    },
  });

  const brian_h = await prisma.employee.upsert({
    where: { emp_no: 4 },
    update: {},
    create: {
      emp_no: 4,
      first_name: "Brian",
      last_name: "Hendricks",
      dept_emps: {
        create: [
          {
            dept_no: engineering.dept_no,
            from_date: new Date("2018-08-6"),
            to_date: new Date("9999-01-01"),
          },
        ],
      },
    },
  });

  console.log({ engineering, sales, tim, brian_f, aaron, brian_h });
}

main()
  .then(async () => {
    await prisma.$disconnect();
    await pool.end();
  })
  .catch(async (e) => {
    console.error(e);
    await prisma.$disconnect();
    await pool.end();
    process.exit(1);
  });

We’ll also need to inform Prisma where the seed file is located. Add to our prisma.config.ts file the following, under migrations:

...  
  migrations: {
    path: "prisma/migrations",
    seed: "tsx prisma/seed.ts",
  },
...

Then all you have to do is run npx prisma db seed. In psql we can see:

prisma=> select * from employees;
 emp_no | first_name | last_name  
--------+------------+----------
      1 | Tim        | Sehn       
      2 | Brian      | Fitzgerald 
      3 | Aaron      | Son        
      4 | Brian      | Hendricks  
(4 rows)

Version Control#

Now that we have some tables and some data, it’s time to use the features offered by Doltgres. I’ll create a new file dolt.ts and add something to print out the status of our staged and working changes. Similar to our last Prisma blog, we’ll want to use PrismaTransaction later on to ensure we maintain our transaction. Liu Liu’s blog covers this in more detail.

Be sure to keep in mind that we have not imported the Doltgres system tables, so we’ll need to query them directly.

import "dotenv/config";
import { Pool } from "pg";
import { PrismaPg } from "@prisma/adapter-pg";
import { PrismaClient, PrismaTransaction } from "./generated/prisma/client";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const adapter = new PrismaPg(pool);
const prisma = new PrismaClient({ adapter });

interface DoltStatus {
    table_name: string;
    staged: number;
    status: string;
}

export async function printStatus(prisma: PrismaTransaction) {
    const res = await prisma.$queryRaw<DoltStatus[]>`SELECT * FROM dolt_status`;
    console.log("Status:");
    if (res.length === 0) {
        console.log("No tables modified");
    } else {
        res.forEach((row) => {
            console.log(`  ${row.table_name}: ${row.status}`);
        });
    }
}

printStatus(prisma).finally(async () => {
    await prisma.$disconnect();
    await pool.end();
});

Run it and we’ll see:

% npx tsx dolt.ts
Status:
  public.departments: new table
  public.dept_emp: new table
  public.employees: new table

A Doltgres Commit#

We’ve added new tables, how do we commit them? We can call the procedure dolt_commit, of course! Let’s add to our file:

interface CommitLog {
  commit_hash: string;
  committer: string;
  message: string;
}

export async function doltCommit(
    prisma: PrismaTransaction,
    author: string,
    msg: string
) {
    const result = await prisma.$executeRaw`SELECT DOLT_COMMIT('--author', ${author}, '-Am', ${msg})`;
    console.log("Created commit:", result);
}

And it’d be nice to see the log as well, so we can do that in the meantime:

export async function printCommitLog(prisma: PrismaTransaction) {
    const res = await prisma.$queryRaw<CommitLog[]>`
    SELECT commit_hash, committer, message FROM dolt_log ORDER BY date DESC
  `;
    console.log("Commit log:", res);
}

Let’s also congregate the calls in one place:

async function main() {
    await doltCommit(prisma, "Nathan <nathan@dolthub.com>", "Create tables and data");
    await printStatus(prisma);
    await printCommitLog(prisma);
}

main()
    .then(async () => {
        await prisma.$disconnect();
        await pool.end();
    })
    .catch(async (e) => {
        console.error(e);
        await prisma.$disconnect();
        await pool.end();
        process.exit(1);
    });

This returns the following:

% npx tsx dolt.ts
Created commit: 1
Status:
  No tables modified
Commit log: [
  {
    commit_hash: '4kr2k9eu5cbcjfq7qhpaq3m4v8r112k2',
    committer: 'Nathan',
    message: 'Create tables and data'
  },
  {
    commit_hash: 'rlojmm3o7cqttfp33aaaekue929ualkg',
    committer: 'Nathan',
    message: 'Initialize data repository'
  },
  {
    commit_hash: 'aklap3qo1l3je4qff6andmnuq02eetf4',
    committer: 'postgres',
    message: 'CREATE DATABASE'
  }
]

Branching with Doltgres and Prisma#

Let’s create a branch called intern and add the best (and only) intern (that’s me!) at DoltHub to our data. To do this we’ll need to call the function DOLT_BRANCH(), and then use DOLT_CHECKOUT(). Make sure to use SELECT, not CALL !

We’ll add the following definitions to our file:

interface DoltBranch {
  name: string;
}

export async function getBranch(prisma: PrismaTransaction, branch: string) {
    return prisma.$queryRaw<DoltBranch[]>`SELECT name FROM dolt_branches WHERE name = ${branch}`;
}

export async function createBranch(prisma: PrismaTransaction, branch: string) {
    const res = await getBranch(prisma, branch);
    if (res && res.length > 0) {
        console.log("Branch exists:", branch);
    } else {
        await prisma.$executeRaw`SELECT DOLT_BRANCH(${branch})`;
        console.log("Created branch:", branch);
    }
}

export async function checkoutBranch(prisma: PrismaTransaction, branch: string) {
    await prisma.$executeRaw`SELECT DOLT_CHECKOUT(${branch})`;
    console.log("Using branch:", branch);
}

export async function printActiveBranch(prisma: PrismaTransaction) {
    const res = await prisma.$queryRaw<{ active_branch: string }[]>`SELECT active_branch()`;
    console.log("Active branch:", res[0]?.active_branch);
}

export async function printDiff(prisma: PrismaTransaction, table: string) {
    const query = Prisma.sql`SELECT * FROM ${Prisma.raw("dolt_diff_" + table)} WHERE to_commit = 'WORKING'`;
    const res = await prisma.$queryRaw(query);
    console.log(`Diff for ${table}:`, res);
}

async function addIntern(prisma: PrismaTransaction) {
    const nathan = await prisma.employee.upsert({
        where: { emp_no: 5 },
        update: {},
        create: {
            emp_no: 5,
            first_name: "Nathan",
            last_name: "Gabrielson",
            birth_date: new Date("2000-01-01"),
            hire_date: new Date("2026-04-06"),
            dept_emps: {
                create: [{
                    dept_no: "d001", // Engineering
                    from_date: new Date("2026-04-06"),
                    to_date: new Date("9999-01-01"),
                }],
            },
        },
    });
    console.log("Added intern:", nathan);
}

async function updateEmployees(prisma: PrismaClient) {
    await prisma.$transaction(async (tx) => {
        await createBranch(tx, "add-intern");
        await checkoutBranch(tx, "add-intern");
        await addIntern(tx);
        await printStatus(tx);
        await printDiff(prisma, "employees");
        await doltCommit(tx, "Nathan Gabrielson <nathan@dolthub.com>", "Add intern Nathan Gabrielson");
    });
    console.log("All operations completed successfully.");
}

After we see the diff, it would be nice to merge in our changes to the main branch as well. We can do this with DOLT_MERGE()

export async function doltMerge(prisma: PrismaTransaction, branch: string) {
    await prisma.$executeRaw`SELECT DOLT_MERGE(${branch})`;
    console.log("Merge complete for:", branch);
}

Add the following calls to our main function:

await updateEmployees(prisma);
await mergeBranch(prisma);
await printCommitLog(prisma);

After running all of that, we get:

Created branch: add-intern
Using branch: add-intern
Added intern: {
  emp_no: 5,
  first_name: 'Nathan',
  last_name: 'Gabrielson',
  hire_date: 2026-04-06T00:00:00.000Z
}
Status:
  public.dept_emp: modified
  public.employees: modified
Created commit: 1
All operations completed successfully.
Using branch: main
Active branch: main
Merge complete for: add-intern
Commit log: [
  {
    commit_hash: '9atdaa6175bhjr2dl5ire3bdgk2n7hrj',
    committer: 'Nathan Gabrielson',
    message: 'Add intern Nathan Gabrielson'
  },
  {
    commit_hash: 'fmicn7351k7t9u1k9srfc1stoao1thpl',
    committer: 'Nathan',
    message: 'Create tables and data'
  },
  {
    commit_hash: 'd1a3igcnc6njn0luhbcitlpdf1ae6iv1',
    committer: 'Nathan',
    message: 'Initialize data repository'
  },
  {
    commit_hash: 'c54vsgcleq4tilb4vs538nakb9mcd1th',
    committer: 'postgres',
    message: 'CREATE DATABASE'
  }
]

Rolling back#

Suppose something terrible has happened to your database. Doltgres’ version control offers many easy to use methods of recovery. What happens if, in a bout of confusion, we accidentally drop our beloved table dpt_emp. While this mistake could be challenging or impossible to remedy in many databases, Doltgres makes it easy to fix. We can simply call dolt_reset('hard').

In a new file, mistake.ts:

import "dotenv/config";
import { Pool } from "pg";
import { PrismaPg } from "@prisma/adapter-pg";
import { PrismaClient, PrismaTransaction } from "./generated/prisma/client";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const adapter = new PrismaPg(pool);
const prisma = new PrismaClient({ adapter });

async function showTables(prisma: PrismaTransaction) {
  const res = await prisma.$queryRaw<{ relname: string }[]>`
    SELECT relname FROM pg_class
    WHERE relkind = 'r' AND relnamespace = (
      SELECT oid FROM pg_namespace WHERE nspname = 'public'
    )
    ORDER BY relname
  `;
  console.log("Tables:", res.map((r) => r.relname).join(", "));
}

async function doltResetHard(prisma: PrismaTransaction, commit?: string) {
  if (commit) {
    await prisma.$executeRaw`SELECT DOLT_RESET('--hard', ${commit})`;
    console.log("Resetting to commit:", commit);
  } else {
    await prisma.$executeRaw`SELECT DOLT_RESET('--hard')`;
    console.log("Resetting to HEAD");
  }
}

async function main() {
  await prisma.$transaction(async (tx) => {
    await showTables(tx);

    await tx.$executeRaw`DROP TABLE dept_emp`;
    console.log("Dropped table: dept_emp");

    console.log("Oh no! The table is gone.");
    await showTables(tx);

    console.log("Let's call DOLT_RESET() and try again");
    await doltResetHard(tx);

    await showTables(tx);
  });
}

main()
  .then(async () => {
    await prisma.$disconnect();
    await pool.end();
  })
  .catch(async (e) => {
    console.error(e);
    await prisma.$disconnect();
    await pool.end();
    process.exit(1);
  });

Run it and we see the following:

% npx tsx mistake.ts
Tables: _prisma_migrations, departments, dept_emp, employees
Dropped table: dept_emp
Oh no! The table is gone.
Tables: _prisma_migrations, departments, employees
Let's call DOLT_RESET() and try again
Resetting to HEAD
Tables: _prisma_migrations, departments, dept_emp, employees

Mistakes and unintended calls are easily reversible in Doltgres. They can be reverted easily and quickly.

The End#

That’s all for me! Prisma is easy to use with Doltgres, which allows you to use diffs and rollback functions to ensure safety and control. If you run into any issues with Prisma, or want to see your favorite ORM supported soon, join us on Discord. We’re always happy to chat.

Blog