Why Prisma and Platformatic are a great match
Tags: node, prisma, database, migrations
Published: 2022-12-14 11:27:35

---
title: Why Prisma and Platformatic are a great match
published: true
description: Learn how to model your database schema and auto-generate your SQL migrations using Prisma.
tags: nodejs, prisma, database, migrations
series: Prisma and Platformatic
cover_image: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/u5guksoiktt136vzqf3e.png
---

[Platformatic](https://oss.platformatic.dev/) is a new tool that helps Node.js developers ship APIs faster. In this 3-part series, we’ll explore why [Prisma](https://www.prisma.io/) 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](https://oss.platformatic.dev/docs/reference/db/plugin) 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](https://www.prisma.io/docs/concepts/components/prisma-schema). 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](https://www.prisma.io/docs/concepts/components/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

```groovy
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](https://www.prisma.io/docs/concepts/components/prisma-schema/data-model) to learn more about data modeling.


<!-- Modeling data using SQL comes with a few shortcomings. For example, understanding the current database state requires combing through the migration history, which can be challenging to keep track. -->

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

{% twitter 1575443725047812098 %}

> I also think writing your own migrations introduces risk for user error like forgetting to create a foreign key.
> 
> [Kishan Gajera](https://twitter.com/kgajera24) from [Generate Up and Down Migrations for Platformatic DB using Prisma](https://kgajera.com/blog/2022/11/15/platformatic-db-using-prisma-for-migrations/)

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](https://www.prisma.io/docs/concepts/components/prisma-cli).

The following SQL migration will be generated based on the schema in the [previous section](#intuitive-data-modeling-language):

```sql
-- 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](https://oss.platformatic.dev/docs/reference/db/plugin) for extending your API. It also provides a [SQL mapper](https://oss.platformatic.dev/docs/reference/sql-mapper/entities/introduction) 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](https://oss.platformatic.dev/docs/reference/cli#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.

![Prisma VS Code intellisense](https://res.cloudinary.com/practicaldev/image/fetch/s--staxrNn_--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_66%2Cw_880/https://dev-to-uploads.s3.amazonaws.com/i/87cdnxf4y98g7rflagx5.gif)

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.

![Prisma VS Code type validation](https://res.cloudinary.com/practicaldev/image/fetch/s--MFi7Afig--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_66%2Cw_880/https://i.imgur.com/3lYgGAI.gif)

> Learn more how you can write type-safe JavaScript with JsDoc in [this article](https://www.prisma.io/blog/type-safe-js-with-jsdoc-typeSaf3js)

## 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. 🤙🏾