Friendly data modeling & auto-generated, editable migrations for Platformatic with Prisma
Tags: node, prisma, database, migrations
Published: 2022-12-15 09:38:54

---
title: Friendly data modeling & auto-generated, editable migrations for Platformatic with Prisma
published: true
description: Learn how you can model your database schema and auto-generate SQL migrations using Prisma for Platformatic.
tags: nodejs, prisma, database, migrations
series: Prisma and Platformatic
cover_image: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/l2ay3nvsvref4szs4t8i.png
---

A wise man once said...

![automate-all-the-things](https://user-images.githubusercontent.com/33921841/194832698-08959b77-9490-48ec-91c4-15f8fd9c6dc5.jpeg)

... 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](https://www.prisma.io) 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](https://marketplace.visualstudio.com/items?itemName=Prisma.prisma) (_optional_)
- [Thunder Client VSCode Extension](https://marketplace.visualstudio.com/items?itemName=rangav.vscode-thunder-client)  (_optional_)
- Docker (_optional_)

> Note: If you don't have Docker installed, you can set up a free hosted database on [Railway](https://railway.app/) or install [PostgreSQL](https://www.postgresql.org/download/).

### 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](https://github.com/ruheni/prisma-platformatic/tree/automated-migrations). It contains the setup files for a new Platformatic project.

To get started, clone the repository and checkout to the [`automated-migrations`](https://github.com/ruheni/prisma-platformatic/tree/automated-migrations) branch.

Clone the repository:

```bash
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
    ```

1. Install dependencies:

    ```bash
    npm install
    ```

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

    ```
    cp .env.example .env
    ```

1. 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:
> ```sql
>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`](http://npmjs.com/package/platformatic) is currently the only dependency in the project.
- `platformatic.db.json`: Defines [Platformatic's configuration](https://oss.platformatic.dev/docs/reference/service/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](https://www.prisma.io/docs/reference/api-reference/command-reference) 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:

```bash
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:

```groovy
// 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_](https://www.prisma.io/docs/concepts/components/prisma-schema#syntax).

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:

```groovy
// ./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](https://www.prisma.io/docs/concepts/components/prisma-schema/data-model) 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`](https://www.prisma.io/docs/reference/api-reference/command-reference#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`](https://www.prisma.io/docs/reference/api-reference/command-reference#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:

```bash
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**](#side-quest-automate-versioning-and-generation-of-your-database-migrations) section to learn how you can automate versioning and generating migrations with the [`@ruheni/db-diff`](https://github.com/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:

```sql
-- 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:

```bash
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`](http://localhost:3042/graphiql) or your REST API on [`http://localhost:3042/documentation`](http://localhost:3042/documentation).

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

```graphql
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:

```json
{
  "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](https://www.npmjs.com/package/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:

```bash
npx prisma db pull
```

Your Prisma schema should now contain a `version` model:

```diff
// ./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:

```diff
// ./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`](https://github.com/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`](https://github.com/kgajera/platformatic-prisma) by [Kishan Gajera](https://twitter.com/kgajera24)

### 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:

```groovy
// ./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
}
```

{% details Expand here to see the schema diff %}

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

{% enddetails %}

### 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:

```sql
-- 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:

```bash
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:

```graphql
mutation INSERT_USER {
  insertUser(inputs: { name: "Alex", email: "alex@prisma.io" }) {
    id
    name
  }
}
```

{% details Expand to view the response %}

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

{% enddetails %}

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

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

{% details Expand to view the response %}

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

{% enddetails %}

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](https://www.prisma.io/docs/reference/api-reference/command-reference#migrate-diff) 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](https://github.com/prisma/prisma/discussions/14101).

In the event you run into any issues working with [`@ruheni/db-diff`](https://github.com/ruheni/db-diff), feel free to [create a GitHub issue](https://github.com/ruheni/db-diff/issues/new) or contribute to the library.

Happy hacking! 🚀