📚 Friendly Data Modeling & Auto-generated, Editable Migrations for Platformatic with Prisma

A wise man once said...

... that was me.

But all jokes and memes aside, automation helps cut down the amount of time spent on tedious and repetitive tasks.

This guide will teach you how to model your database schema and auto-generate customizable SQL migrations using Prisma when working with Platformatic.

Prerequisites

Your dev toolbox

To follow along, ensure you have the following installed:

• Node.js v16.17.0
• npm v7 or later
• Prisma VSCode Extension (optional)
• Thunder Client VSCode Extension (optional)
• Docker (optional)

Note: If you don't have Docker installed, you can set up a free hosted database on Railway or install PostgreSQL.

Assumed knowledge

This guide will require you to have basic familiarity with the following technologies:

• JavaScript
• GraphQL APIs
• REST APIs

Set up your Platformatic app

In this tutorial, you'll use the following starter repository. It contains the setup files for a new Platformatic project.

To get started, clone the repository and checkout to the automated-migrations branch.

Clone the repository:

git clone -b automated-migrations https://github.com/ruheni/prisma-platformatic.git

Now, perform the following actions to get started:

1. Navigate to the cloned directory:
   

   cd prisma-platformatic
   

2. Install dependencies:
   

   npm install
   

3. Create a .env file based-off of the .env.example file:
   

   cp .env.example .env
   

4. Start the PostgreSQL database with docker:
   

   docker-compose up -d
   

Note: If you already have an existing database server running locally, update the value of the DATABASE_URL in your .env file with your database's user and password values:

# .env
DATABASE_URL="postgres://<USER>:<PASSWORD>@localhost:5432/blog"

Connect to your database instance using psql or your preferred SQL client. Copy and run the following SQL to create a database:

CREATE DATABASE blog;

Project structure and files

The project has the following structure:

prisma-platformatic
  ├── .env.example
  ├── .env
  ├── .gitignore
  ├── README.md
  ├── docker-compose.yml
  ├── package-lock.json
  ├── package.json
  └── platformatic.db.json

The noteworthy files in the directory are:

• .env: Contains the database connection string for your PostgreSQL database.
• docker-compose.yml: Defines the Docker image and configuration for your PostgreSQL database.
• package.json: Defines your application dependencies. platformatic is currently the only dependency in the project.
• platformatic.db.json: Defines Platformatic's configuration such as the server's hostname and port, migration directory, and your database's connection string.

Data modeling and automated migrations

Now that you've set up your application, it's time to get your hands dirty with Prisma!

Set Prisma in your project

To get started, first install the Prisma CLI as a development dependency in your project:

npm install prisma --save-dev

The Prisma CLI provides the tools that allow you to evolve your database schema in your project.

You can now initialize Prisma in your project with the following command:

npx prisma init

The command creates a prisma folder at the root containing a schema.prisma file. The schema.prisma file serves as a source of truth for your database schema.

When you open up the schema.prisma file, you should see the following:

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgres"
  url      = env("DATABASE_URL")
}

The Prisma schema uses an intuitive and human-readable language called the Prisma Schema language.

The schema file is composed of three main components:

• Data source: Defines your database connection details such as the provider and database's connection string.
• Generator: Defines the assets generated when specific Prisma commands are invoked. In this case, Prisma Client, a type-safe query builder for your database, will be generated.
• Data model: Defines the entities of your application that map to your database's tables (for relational databases) or collections (MongoDB). The schema doesn't have any yet, but models are denoted with the model keyword, followed by the entity name.

Model your database schema

For this guide, you will create a Post model with the following fields in your schema.prisma file:

// ./prisma/schema.prisma
model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  viewCount Int      @default(0)
  createdAt DateTime @default(now())
}

The snippet above defines a model called Post with the following fields and properties:

• id: An auto-incrementing integer that will be the primary key for the model.
• title: A non-null String field.
• content: A nullable String field.
• published: A Boolean field with a default value of false.
• viewCount: An Int field with a default value of 0.
• createdAt: A DateTime field with a timestamp of when the value is created as its default value.

Refer to the Prisma documentation for further details on how to model your data using Prisma.

Generate a migration with migrate diff

With the schema defined, you will now auto-generate a database migration using prisma migrate diff.

prisma migrate diff compares (or "diffs") two schemas, the current, and the anticipated version. The current version is the from state, and the anticipated version is the to state. The command generates a SQL script describing the changes.

Fun fact: If you've used the prisma migrate dev command before, it runs prisma migrate diff under the hood.

The command, prisma migrate diff accepts the following schema sources for comparison:

• A live database
• A migration history
• Schema data model (defined in the Prisma schema)
• An empty schema

The prisma migrate diff command will use the following arguments to generate a migration:

• --from-schema-datasource: Uses the URL defined in the datasource block.
• --to-schema-datamodel: Uses the data model defined in the Prisma schema for the diff.
• --script (optional): Outputs a SQL script.

The --from-schema-datasource and --to-schema-datamodel also require a path to your Prisma schema file.

Create the migrations directory that you will use to store a history of migrations:

mkdir migrations

The migrations directory is used by Platformatic to store and track the history of applied migrations.

Next, open up a terminal within your project directory run the following command to auto-generate your first migration:

npx prisma migrate diff \
--from-schema-datasource ./prisma/schema.prisma \
--to-schema-datamodel ./prisma/schema.prisma \
--script > migrations/001.do.sql \
--exit-code

Notes:

1. Update the output filename for any future migrations to prevent overwriting the contents of 001.do.sql
2. You can jump to the Side quest section to learn how you can automate versioning and generating migrations with the @ruheni/db-diff utility library
3. If you omit the --script argument, the command will generate a human-readable summary that looks something like this:

[+] Added tables
 - Post

The command creates a file called 001.do.sql inside the migrations directory with the following contents:

-- migrations/001.do.sql
-- CreateTable
CREATE TABLE "Post" (
    "id" SERIAL NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "title" TEXT NOT NULL,
    "content" TEXT,
    "published" BOOLEAN NOT NULL DEFAULT false,
    "viewCount" INTEGER NOT NULL DEFAULT 0,

    CONSTRAINT "Post_pkey" PRIMARY KEY ("id")
);

You'll notice that the command generated the SQL that describes the changes you defined in the Prisma schema file.

Start your API server

In your project directory, apply the migrations to your database using the Platformatic CLI:

npx platformatic db migrations apply

Next, start up your API server:

npx platformatic db start

The command will:

• Start the Platformatic API server
• Auto-generate a REST and GraphQL API from your SQL database

Explore and interact with your API

You can now explore your GraphQL API on http://localhost:3042/graphiql or your REST API on http://localhost:3042/documentation.

Run the following mutation on GraphiQL to insert a record in your database:

mutation INSERT_POST {
  insertPost(
    inputs: {
      title: "Prisma 💚 Platformatic"
      content: "Learn how you can auto-generate your database migrations using Prisma for Platformatic"
    }
  ) {
    id
    title
    content
    createdAt
    published
  }
}

You should see the following output with a different createdAt value:

{
  "data": {
    "insertPost": [
      {
        "id": "1",
        "title": "Prisma 💚 Platformatic",
        "content": "Learn how you can auto-generate your database migrations using Prisma for Platformatic",
        "createdAt": "2022-10-08T14:26:08.101Z",
        "published": false
      }
    ]
  }
}

Congratulations! 🎉

Introspect your database for the versions model

Under the hood, Platformatic uses Postgrator to run migrations. Postgrator creates a table in the database called versions to track the applied migrations.

The versions table is not yet captured in the Prisma schema. When auto-generating future migrations, Prisma might prompt you to drop the versions table, which is not ideal.

To prevent this, you can run prisma db pull to introspect the database and populate the Prisma schema with the missing model:

npx prisma db pull

Your Prisma schema should now contain a version model:

// ./prisma/schema.prisma
model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  viewCount Int      @default(0)
  createdAt DateTime @default(now())
}

+model versions {
+  version BigInt    @id
+  name    String?
+  md5     String?
+  run_at  DateTime? @db.Timestamptz(6)
+}

Add the @@ignore attribute function to the model to exclude it from the Prisma Client API:

// ./prisma/schema.prisma
model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  viewCount Int      @default(0)
  createdAt DateTime @default(now())
}

model versions {
  version BigInt    @id
  name    String?
  md5     String?
  run_at  DateTime? @db.Timestamptz(6)
+
+  @@ignore
}

Side quest 🧙🏽: Automate versioning and generation of your database migrations

The approach for generating migrations in the previous section generally works fine. The only caveat is that you have to manually specify the version of the migration file with every migration, i.e., 001.do.sql, 002.do.sql, and so forth.

Another friction point is that the command is very long, tedious and there is a possibility of making an error.

To get around these friction points, I built a utility library called @ruheni/db-diff. The tool wraps around the prisma migrate diff command. It can generate an up and a down migration. @ruheni/db-diff also versions the generated migration file and are Postgrator-compatible. On top of that, you can generate an up and down migration for every schema change.

Alternatively, you can also use platformatic-prisma by Kishan Gajera

Install the helper utility

To get started, you can install @ruheni/db-diff as a development dependency in your project:

npm install --save-dev @ruheni/db-diff

Update your schema

Next, update your Prisma schema by creating a User model with the following fields:

• id: the primary key with an auto-incrementing integer
• email: a string value with a @unique constraint
• name: a string value (nullable/ not-required)
• posts: a one-to-many relationship between the Post and User models, respectively

Your Prisma schema should resemble the schema in the code block below:

// ./prisma/schema.prisma
model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String?
  posts Post[]
}

model Post {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  title     String
  content   String?
  published Boolean  @default(false)
  viewCount Int      @default(0)
  author    User?    @relation(fields: [authorId], references: [id])
  authorId  Int?
}

model versions {
  version BigInt    @id
  name    String?
  md5     String?
  run_at  DateTime? @db.Timestamptz(6)

  @@ignore
}

Expand here to see the schema diff

// ./prisma/schema.prisma
+model User {
+  id    Int     @id @default(autoincrement())
+  email String  @unique
+  name  String?
+  posts Post[]
+}

model Post {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  title     String
  content   String?
  published Boolean  @default(false)
  viewCount Int      @default(0)
+  author    User?    @relation(fields: [authorId], references: [id])
+  authorId  Int?
}

model versions {
  version BigInt    @id
  name    String?
  md5     String?
  run_at  DateTime? @db.Timestamptz(6)

  @@ignore
}

Auto-generate an up migration using @ruheni/db-diff

Next, use @ruheni/db-diff to auto-generate an up migration:

npx db-diff --up

The command should generate a new file called 002.do.sql with the following contents:

-- migrations/002.do.sql
-- AlterTable
ALTER TABLE "Post" ADD COLUMN     "authorId" INTEGER;

-- CreateTable
CREATE TABLE "User" (
    "id" SERIAL NOT NULL,
    "email" TEXT NOT NULL,
    "name" TEXT,

    CONSTRAINT "User_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");

-- AddForeignKey
ALTER TABLE "Post" ADD CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;

You can specify the type of migration you would like to generate by passing either --up for only the up migration or --down for the down migration.

@ruheni/db-diff utility library will auto-generate an up and a down migration if you don't provide either the --up or --down flags. If you maintain down migrations, ensure the migration version name is at par with the up migration.

Apply the generated migration using Platformatic CLI:

npx platformatic db migrations apply

Restart and interact with your API using Platformatic

Restart the API server:

npx platformatic db start

Platformatic will regenerate the GraphQL and REST APIs.

Open up GraphiQL on http://localhost:3042/graphiql and run the following mutation to create a user record in your database:

mutation INSERT_USER {
  insertUser(inputs: { name: "Alex", email: "[email protected]" }) {
    id
    name
  }
}

Expand to view the response

{
  "data": {
    "insertUser": [
      {
        "id": "1",
        "name": "Alex"
      }
    ]
  }
}

Run another query to link the user record with the existing post record you created in a previous step:

mutation SAVE_POST {
  savePost(input: { id: 1, authorId: 1 }) {
    id
    title
    content
    author {
      name
    }
  }
}

Expand to view the response

{
  "data": {
    "savePost": {
      "id": "1",
      "title": "Prisma 💚 Platformatic",
      "content": "Learn how you can auto-generate your database migrations using Prisma for Platformatic",
      "user": {
        "name": "Alex"
      }
    }
  }
}

And you're all done! 🎉

Wrapping up

To recap what was covered in this part, you:

• Modeled your database schema using Prisma
• Used the prisma migrate diff to auto-generate your SQL migrations
• Created a GraphQL and REST API using Platformatic
• Used the @ruheni/db-diff utility to auto-generate and version your SQL migrations

The next article will cover how you can extend the generated GraphQL and REST API using Prisma Client.

Feel free to refer to prisma migrate diff reference docs to learn how you can use it to automate your database migration workflows. If you build something cool you would like to share with the rest of the world, feel free to share it in this GitHub discussion thread.

In the event you run into any issues working with @ruheni/db-diff, feel free to create a GitHub issue or contribute to the library.

Happy hacking! 🚀