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

A sensible man as soon as mentioned…

… that was me.

However all jokes and memes apart, automation helps minimize down the period of time spent on tedious and repetitive duties.

This information will train you how you can mannequin your database schema and auto-generate customizable SQL migrations utilizing Prisma when working with Platformatic.

Stipulations

Your dev toolbox

To comply with alongside, guarantee you’ve gotten the next put in:

Observe: If you do not have Docker put in, you may arrange a free hosted database on Railway or set up PostgreSQL.

Assumed information

This information would require you to have fundamental familiarity with the next applied sciences:

• JavaScript
• GraphQL APIs
• REST APIs

Arrange your Platformatic app

On this tutorial, you will use the next starter repository. It incorporates the setup recordsdata for a brand new Platformatic challenge.

To get began, clone the repository and checkout to the automated-migrations department.

Clone the repository:

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

Now, carry out the next actions to get began:

1. Navigate to the cloned listing:

   cd prisma-platformatic
   

2. Set up dependencies:

   npm set up
   

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

   cp .env.instance .env
   

4. Begin the PostgreSQL database with docker:

   docker-compose up -d
   

Observe: If you have already got an present database server operating domestically, replace the worth of the DATABASE_URL in your .env file together with your database’s person and password values:

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

Hook up with your database occasion utilizing psql or your most well-liked SQL consumer. Copy and run the next SQL to create a database:

CREATE DATABASE weblog;

Mission construction and recordsdata

The challenge has the next construction:

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

The noteworthy recordsdata within the listing are:

• .env: Incorporates the database connection string on your PostgreSQL database.
• docker-compose.yml: Defines the Docker picture and configuration on your PostgreSQL database.
• bundle.json: Defines your software dependencies. platformatic is presently the one dependency within the challenge.
• platformatic.db.json: Defines Platformatic’s configuration such because the server’s hostname and port, migration listing, and your database’s connection string.

Information modeling and automatic migrations

Now that you have arrange your software, it is time to get your fingers soiled with Prisma!

Set Prisma in your challenge

To get began, first set up the Prisma CLI as a growth dependency in your challenge:

npm set up prisma --save-dev

The Prisma CLI offers the instruments that mean you can evolve your database schema in your challenge.

Now you can initialize Prisma in your challenge with the next command:

npx prisma init

The command creates a prisma folder on the root containing a schema.prisma file. The schema.prisma file serves as a supply of fact on your database schema.

Once you open up the schema.prisma file, you need to see the next:

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

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

The Prisma schema makes use of an intuitive and human-readable language known as the Prisma Schema language.

The schema file consists of three primary parts:

• Information supply: Defines your database connection particulars such because the supplier and database’s connection string.
• Generator: Defines the belongings generated when particular Prisma instructions are invoked. On this case, Prisma Consumer, a type-safe question builder on your database, shall be generated.
• Information mannequin: Defines the entities of your software that map to your database’s tables (for relational databases) or collections (MongoDB). The schema would not have any but, however fashions are denoted with the mannequin key phrase, adopted by the entity identify.

Mannequin your database schema

For this information, you’ll create a Submit mannequin with the next fields in your schema.prisma file:

// ./prisma/schema.prisma
mannequin Submit {
  id        Int      @id @default(autoincrement())
  title     String
  content material   String?
  printed Boolean  @default(false)
  viewCount Int      @default(0)
  createdAt DateTime @default(now())
}

The snippet above defines a mannequin known as Submit with the next fields and properties:

• id: An auto-incrementing integer that would be the major key for the mannequin.
• title: A non-null String discipline.
• content material: A nullable String discipline.
• printed: A Boolean discipline with a default worth of false.
• viewCount: An Int discipline with a default worth of 0.
• createdAt: A DateTime discipline with a timestamp of when the worth is created as its default worth.

Discuss with the Prisma documentation for additional particulars on how you can mannequin your knowledge utilizing Prisma.

Generate a migration with migrate diff

With the schema outlined, you’ll now auto-generate a database migration utilizing prisma migrate diff.

prisma migrate diff compares (or “diffs”) two schemas, the present, and the anticipated model. The present model is the from state, and the anticipated model is the to state. The command generates a SQL script describing the adjustments.

Enjoyable truth: For those who’ve used the prisma migrate dev command earlier than, it runs prisma migrate diff below the hood.

The command, prisma migrate diff accepts the next schema sources for comparability:

• A dwell database
• A migration historical past
• Schema knowledge mannequin (outlined within the Prisma schema)
• An empty schema

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

• --from-schema-datasource: Makes use of the URL outlined within the datasource block.
• --to-schema-datamodel: Makes use of the information mannequin outlined within the Prisma schema for the diff.
• --script (optionally available): Outputs a SQL script.

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

Create the migrations listing that you’ll use to retailer a historical past of migrations:

mkdir migrations

The migrations listing is utilized by Platformatic to retailer and monitor the historical past of utilized migrations.

Subsequent, open up a terminal inside your challenge listing run the next 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. Replace the output filename for any future migrations to stop overwriting the contents of 001.do.sql
2. You’ll be able to bounce to the Side quest part to be taught how one can automate versioning and producing migrations with the @ruheni/db-diff utility library
3. For those who omit the --script argument, the command will generate a human-readable abstract that appears one thing like this:

[+] Added tables
 - Submit

The command creates a file known as 001.do.sql contained in the migrations listing with the next contents:

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

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

You will discover that the command generated the SQL that describes the adjustments you outlined within the Prisma schema file.

Begin your API server

In your challenge listing, apply the migrations to your database utilizing the Platformatic CLI:

npx platformatic db migrations apply

Subsequent, begin up your API server:

npx platformatic db begin

The command will:

• Begin the Platformatic API server
• Auto-generate a REST and GraphQL API out of your SQL database

Discover and work together together with your API

Now you can discover your GraphQL API on http://localhost:3042/graphiql or your REST API on http://localhost:3042/documentation.

Run the next mutation on GraphiQL to insert a document in your database:

mutation INSERT_POST {
  insertPost(
    inputs: {
      title: "Prisma 💚 Platformatic"
      content material: "Study how one can auto-generate your database migrations utilizing Prisma for Platformatic"
    }
  ) {
    id
    title
    content material
    createdAt
    printed
  }
}

You must see the next output with a special createdAt worth:

{
  "knowledge": {
    "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 variations mannequin

Beneath the hood, Platformatic makes use of Postgrator to run migrations. Postgrator creates a desk within the database known as variations to trace the utilized migrations.

The variations desk is just not but captured within the Prisma schema. When auto-generating future migrations, Prisma may immediate you to drop the variations desk, which isn’t ultimate.

To stop this, you may run prisma db pull to introspect the database and populate the Prisma schema with the lacking mannequin:

npx prisma db pull

Your Prisma schema ought to now comprise a model mannequin:

// ./prisma/schema.prisma
mannequin Submit {
  id        Int      @id @default(autoincrement())
  title     String
  content material   String?
  printed Boolean  @default(false)
  viewCount Int      @default(0)
  createdAt DateTime @default(now())
}

+mannequin variations {
+  model BigInt    @id
+  identify    String?
+  md5     String?
+  run_at  DateTime? @db.Timestamptz(6)
+}

Add the @@ignore attribute perform to the mannequin to exclude it from the Prisma Consumer API:

// ./prisma/schema.prisma
mannequin Submit {
  id        Int      @id @default(autoincrement())
  title     String
  content material   String?
  printed Boolean  @default(false)
  viewCount Int      @default(0)
  createdAt DateTime @default(now())
}

mannequin variations {
  model BigInt    @id
  identify    String?
  md5     String?
  run_at  DateTime? @db.Timestamptz(6)
+
+  @@ignore
}

Aspect quest 🧙🏽: Automate versioning and technology of your database migrations

The method for producing migrations within the earlier part usually works advantageous. The one caveat is that you must manually specify the model of the migration file with each migration, i.e., 001.do.sql, 002.do.sql, and so forth.

One other friction level is that the command could be very lengthy, tedious and there’s a chance of constructing an error.

To get round these friction factors, I constructed a utility library known as @ruheni/db-diff. The device wraps across the prisma migrate diff command. It may generate an up and a down migration. @ruheni/db-diff additionally variations the generated migration file and are Postgrator-compatible. On prime of that, you may generate an up and down migration for each schema change.

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

Set up the helper utility

To get began, you may set up @ruheni/db-diff as a growth dependency in your challenge:

npm set up --save-dev @ruheni/db-diff

Replace your schema

Subsequent, replace your Prisma schema by making a Person mannequin with the next fields:

• id: the first key with an auto-incrementing integer
• e-mail: a string worth with a @distinctive constraint
• identify: a string worth (nullable/ not-required)
• posts: a one-to-many relationship between the Submit and Person fashions, respectively

Your Prisma schema ought to resemble the schema within the code block under:

// ./prisma/schema.prisma
mannequin Person {
  id    Int     @id @default(autoincrement())
  e-mail String  @distinctive
  identify  String?
  posts Submit[]
}

mannequin Submit {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  title     String
  content material   String?
  printed Boolean  @default(false)
  viewCount Int      @default(0)
  writer    Person?    @relation(fields: [authorId], references: [id])
  authorId  Int?
}

mannequin variations {
  model BigInt    @id
  identify    String?
  md5     String?
  run_at  DateTime? @db.Timestamptz(6)

  @@ignore
}

// ./prisma/schema.prisma
+mannequin Person {
+  id    Int     @id @default(autoincrement())
+  e-mail String  @distinctive
+  identify  String?
+  posts Submit[]
+}

mannequin Submit {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  title     String
  content material   String?
  printed Boolean  @default(false)
  viewCount Int      @default(0)
+  writer    Person?    @relation(fields: [authorId], references: [id])
+  authorId  Int?
}

mannequin variations {
  model BigInt    @id
  identify    String?
  md5     String?
  run_at  DateTime? @db.Timestamptz(6)

  @@ignore
}

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

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

npx db-diff --up

The command ought to generate a brand new file known as 002.do.sql with the next contents:

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

-- CreateTable
CREATE TABLE "Person" (
    "id" SERIAL NOT NULL,
    "e-mail" TEXT NOT NULL,
    "identify" TEXT,

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

-- CreateIndex
CREATE UNIQUE INDEX "User_email_key" ON "Person"("e-mail");

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

You’ll be able to specify the kind of migration you wish to generate by passing both --up for less than the up migration or --down for the down migration.

@ruheni/db-diff utility library will auto-generate an up and a down migration in the event you do not present both the --up or --down flags. For those who preserve down migrations, make sure the migration model identify is at par with the up migration.

Apply the generated migration utilizing Platformatic CLI:

npx platformatic db migrations apply

Restart and work together together with your API utilizing Platformatic

Restart the API server:

npx platformatic db begin

Platformatic will regenerate the GraphQL and REST APIs.

Open up GraphiQL on http://localhost:3042/graphiql and run the next mutation to create a person document in your database:

mutation INSERT_USER {
  insertUser(inputs: { identify: "Alex", e-mail: "alex@prisma.io" }) {
    id
    identify
  }
}

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

Run one other question to hyperlink the person document with the prevailing put up document you created in a earlier step:

mutation SAVE_POST {
  savePost(enter: { id: 1, authorId: 1 }) {
    id
    title
    content material
    writer {
      identify
    }
  }
}

{
  "knowledge": {
    "savePost": {
      "id": "1",
      "title": "Prisma 💚 Platformatic",
      "content material": "Study how one can auto-generate your database migrations utilizing Prisma for Platformatic",
      "person": {
        "identify": "Alex"
      }
    }
  }
}

And also you’re all carried out! 🎉

Wrapping up

To recap what was coated on this half, you:

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

The following article will cowl how one can lengthen the generated GraphQL and REST API utilizing Prisma Consumer.

Be at liberty to discuss with prisma migrate diff reference docs to find out how you need to use it to automate your database migration workflows. For those who construct one thing cool you wish to share with the remainder of the world, be at liberty to share it on this GitHub discussion thread.

Within the occasion you run into any points working with @ruheni/db-diff, be at liberty to create a GitHub issue or contribute to the library.

Blissful hacking! 🚀