📚 Why Prisma and Platformatic are a great match

Platformatic is a new tool that helps Node.js developers ship APIs faster. In this 3-part series, we’ll explore why Prisma and Platformatic are such a great fit and how Prisma can help improve your data modeling, migrations, and querying workflows when using Platformatic.

What is Platformatic DB

Platformatic DB is an open-source tool that auto-generates CRUD APIs for your database. It supports both GraphQL and REST (with an OpenAPI schema) APIs.

It also allows you to extend the API using plugins and add authentication as well as authorization for your APIs. Platformatic DB supports SQLite, MySQL, MariaDB, and PostgreSQL databases.

What is Prisma

Prisma is an open-source ORM (Object Relational Mapper). It provides a declarative way to define the structure of your database using the Prisma schema language. It also provides tools for evolving your database schema by auto-generating fully customizable SQL migrations.

Prisma provides a type-safe and intuitive client for interacting with your database called Prisma Client.

How Prisma can improve your development workflow with Platformatic DB

While Platformatic speeds up building REST and GraphQL APIs from your database, Prisma can complement your development workflow in several ways.

In the following sections, we'll explore three concrete ways Prisma makes working with Platformatic DB easier.

Intuitive data modeling language

Prisma provides a Prisma schema — an intuitive, human-readable declarative definition of your database schema. The Prisma schema serves as the source of truth for your database schema by describing its desired end state.

One of the benefits of using the Prisma schema is the auto-completion, with the help of the Prisma extension, and type-checking when modeling your database schema, allowing you to catch errors early.

The following schema represents:

• User and Post models
• A one-to-many relationship between the User and Post models

model User {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  name      String?
  email     String   @unique

  posts     Post[]
}

model Post {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  title     String
  content   String
  published Boolean  @default(false)

  author    User?    @relation(fields: [authorId], references: [id])
  authorId  Int?
}

Refer to the Prisma docs to learn more about data modeling.

Auto-generated and customizable SQL migrations

To generate your API with Platformatic, you must first define your database schema. The database schema is defined using SQL in a set of migrations files that are written manually. However, maintaining SQL migrations by hand can be tedious, brittle, and error-prone.

Simon

@simonboisset

@platformatic is a really nice tool for building api on top of sql migrations. It would be insane if it has a @prisma migration setup.

11:13 AM - 29 Sep 2022

I also think writing your own migrations introduces risk for user error like forgetting to create a foreign key.

Kishan Gajera from Generate Up and Down Migrations for Platformatic DB using Prisma

Editors typically don't provide auto-completion and type-checking when working with SQL. This might make it challenging to spot errors such as missing constraints like foreign keys.

Prisma can help automate this process by generating a fully-customizable migration based on the defined schema using the CLI.

The following SQL migration will be generated based on the schema in the previous section:

-- CreateTable
CREATE TABLE "User" (
    "id" SERIAL NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "email" TEXT NOT NULL,
    "name" TEXT,

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

-- CreateTable
CREATE TABLE "Post" (
    "id" SERIAL NOT NULL,
    "title" VARCHAR(255) NOT NULL,
    "content" TEXT NOT NULL,
    "published" BOOLEAN NOT NULL DEFAULT false,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "authorId" INTEGER,

    CONSTRAINT "Post_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;

Type-safety and auto-completion for database queries

Platformatic provides plugins for extending your API. It also provides a SQL mapper for querying the database.

Besides the out-of-the-box SQL mapper, you can alternatively extend your Platformatic API using Prisma Client to query your database. Prisma Client provides fully type-safe queries with rich type definitions based on your Prisma schema.

Platformatic also provides TypeScript support. You can use the CLI to generate TypeScript types for your entities from your database.

If you're working with the VS Code editor, VS Code's intellisense and suggestions will pop up as you define your queries.

Additionally, VS Code can also run your JavaScript file, which uses Prisma Client through the TypeScript compiler and throws errors when a type is violated.

Learn more how you can write type-safe JavaScript with JsDoc in this article

Wrapping up

Platformatic speeds up the development of REST and GraphQL APIs. Prisma pushes the development experience even further by providing intuitive data modeling, auto-generating customizable migrations, type-safety, and auto-completion.

Look out for parts 2 and 3 — modeling, auto-generating database migrations, and extending your API using a plugin with Prisma Client for Platformatic. 🤙🏾