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:
-
Navigate to the cloned directory:
cd prisma-platformatic
1. Install dependencies:
```bash
npm install
-
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: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-nullString
field. -
content
: A nullableString
field. -
published
: ABoolean
field with a default value offalse
. -
viewCount
: AnInt
field with a default value of0
. -
createdAt
: ADateTime
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 runsprisma 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 thedatasource
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:
- Update the output filename for any future migrations to prevent overwriting the contents of
001.do.sql
- 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- 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 thePost
andUser
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: "alex@prisma.io" }) {
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! 🚀
Top comments (2)
Thanks Ruheni.
Hi alex the post was great . I love it .
But I have a problem with the @@map function in the model. I have User model but then added @@map("users") in the end to clip it with the users table. The DB is of Postgress. But then when I am making any api call it is saying User does not exist. Its like it is not able to read the @@map function. What is the solution for this?