Prisma ORM: how to use the great database mapping package
NodeJS is awesome as a backend development platform: it is fully asynchronous, it is cross-platform, it is compatible with browser development language, and already has tons for sweet open-source packages. But there is one "except". The lack of good ORM for SQL databases with the auto-migrations system.
Prisma is a brand new, well-sponsored open-source ORM. Prisma was first released in summer 2020, but already earned 11k stars on GitHub! Let's give it a try and see how many actions we will need to set up a fully functional simple web app. We will also make simple changes in the data structure to see how hard would it be to manage the lib in real-life projects.
Why we are not satisfied with existing Node ORMs
The current world of GitHub-powered opensource gives us tools for everything. But as a Node developer up to this day, I must admit that some niches are still very pure and free for the awesome next-generation packages. I have nothing against Sequalize, the super-popular and reliable ORM. But the default way of creating migrations... Once you add, delete or change a model you have to create a file with migration and write code that modifies the database. I think a lot of developers in the node world think it is a normal acceptable way.
However, if you had a chance to touch Python's SQLAlchemey or async GINO with Alembic migration tool or Django framework, then you know that all database work could bring pleasure and satisfaction. In that Python world, you think only about data structure, relations, indexes. You just describe the model using a high-level language. Then you run the migration creator with one simple CLI command. It creates a migration file for you and when you apply migration with another simple command, it saves state in the database itself. So database instance is aware of its own migrations state. So you create migration during local development and CI automatically applies it to the staging database and then to the live database once you merge git branches.
In Sequalize you need always think "Please God, don't let me forget to describe migration for a field that I just created, don't let me make a mistake here", "Oh crap, I spent a whole day on migrations", "Oh crap, all crashed when I deployed to prod. I don't know why". One guy tried to solve it and created sequelize-auto-migrations. Know what? This staff stores the migration state in the filesystem, not in the database. I am not sure how it could be shared with colleagues? Should they write down by pencil which migrations were applied and which not? Probably your DevOps already searching for a new job while you installing that package.
In our VUCA world to be a successful developer you have to be agile. You need to be able to quickly recreate a database structure, add a new field, denormalize the database to boost performers. You should be able to do any structural changes with one flick of the finger.
Simple example
No more words, only quick guide as we always do at Hinty.
Create a Node project:
| mkdir prismatic && cd prismatic | |
| npm init -y |
Initialize Prizma in the project:
| npm i -D prisma | |
| npx prisma init |
In .env change database string
| DATABASE_URL="postgresql://johndoe:randompassword@localhost:5432/mydb | |
| ?schema=public" |
To:
DATABASE_URL="sqlite:../test.sqlite"
As you can see it already suggests you PostgreSQL. The beauty of each ORM is that it is compatible with lots of databases. Now for fast guide purposes, we will use SQLlite which is a database in one file without dependencies. But for your mature projects, you can Use Postgress or MySQL.
Now open prisma/schema.prisma
Change provider line to:
provider = "sqlite"
Now at the bottom of this schema file let's describe our structure. I will show you a simple notepad structure. My aim is to create two tables with next valuable fields:
So here is what we write in the schema file:
| model Note { | |
| id Int @id @default(autoincrement()) | |
| content String? | |
| createdAt DateTime @default(now()) | |
| isDraft Boolean @default(false) | |
| category Category? @relation(fields: [categoryId], references: [id]) | |
| categoryId Int? | |
| } | |
| model Category { | |
| id Int @id @default(autoincrement()) | |
| name String? | |
| notes Note[] | |
| } |
Description way looks super simple and is same time defines Foreign key relation in a pretty explicit way.
Let's try to sync these models into the real database:
npx prisma db push
Command says:
🚀 Your database is now in sync with your schema. Done in 56
😲 That's all I need to migrate Database schema?
Wait, let's check the database using a simple web SQLite browser, in the new terminal run:
| docker run -it --rm -p 8080:8080 -v $PWD:/data -e SQLITE_DATABASE="test.sqlite" \ | |
| coleifer/sqlite-web |
I am using docker to run SQLite browser without installing extra tools. Fast and simple. If you are on Windows, you can install docker simply now. On Linux/Ubuntu it is even simpler, just run
sudo apt install docker.io
Now go to web SQL browse http://127.0.0.1:8080/
Prisma mapped all as we described in the model:
SQL for our table:
| CREATE TABLE "Note" ( | |
| "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, | |
| "content" TEXT, | |
| "createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| "isDraft" BOOLEAN NOT NULL DEFAULT false, | |
| "categoryId" INTEGER, | |
| FOREIGN KEY ("categoryId") REFERENCES "Category" ("id") | |
| ON DELETE SET NULL ON UPDATE CASCADE | |
| ) |
Let's try to modify the schema, a little bit. Rename content ➡ text :
text String?
And also make notes "drafted by default":
isDraft Boolean @default(true)
Sync again:
npx prisma db push
Works like a clock:
| CREATE TABLE "Note" ( | |
| "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, | |
| "text" TEXT, | |
| "createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| "isDraft" BOOLEAN NOT NULL DEFAULT true, | |
| "categoryId" INTEGER, | |
| FOREIGN KEY ("categoryId") REFERENCES "Category" ("id") | |
| ON DELETE SET NULL ON UPDATE CASCADE | |
| ) |
Database access layer
Well, Prisma did well with migrations. I must state that it is even better than Python's tool mentioned above.
Let's see how to work with actual data.
To make it closer to real-life let's code a couple of REST endpoints using an express webserver.
npm i express cors -D
Add "type": "module", to a package.json to support better ES6 imports.
Also, create an index.js in the root folder:
| import express from 'express' | |
| import cors from 'cors' | |
| import Prisma from '@prisma/client'; | |
| const { PrismaClient } = Prisma; | |
| const app = express() | |
| app.use(express.json()); | |
| app.use(cors()) | |
| app.listen(3000, '127.0.0.1', () => { | |
| console.log("Listening on http://127.0.0.1:3000") | |
| }) | |
| const prisma = new PrismaClient() | |
| app.get('/list', async (req, res) => { | |
| const notes = await prisma.note.findMany({ | |
| where: { isDraft: false }, | |
| include: { category: true }, | |
| }) | |
| res.json(notes) | |
| }) | |
| app.post('/note', async (req, res) => { | |
| const { text, categoryName } = req.body | |
| let category = await prisma.category.findFirst({ | |
| where: { name: categoryName }, | |
| }) | |
| if (!category) { | |
| category = await prisma.category.create({ | |
| data: { | |
| name: categoryName, | |
| }, | |
| }) | |
| } | |
| const note = await prisma.note.create({ | |
| data: { | |
| text, | |
| categoryId: category.id, | |
| }, | |
| }) | |
| res.json(note) | |
| }) | |
| app.put('/undraft/:id', async (req, res) => { | |
| const { id } = req.params; | |
| const note = await prisma.note.update({ | |
| where: { id: +id }, | |
| data: { isDraft: false }, | |
| }) | |
| res.json(note) | |
| }) |
Run express:
node index.js
Node versions less then 12 might need extra flag node --experimental-modules index.js You might note that I allowed CORS using app.use(cors()) . This was done only to allow browser performing requests from our Postwoman will be executed on another subdomain (Postwomen @ 127.0.0.1:4000 will make requests to our API server @ 127.0.0.1:3000). Newer do it in production. First, make sure you understand CORS well.
Testing our API with postwomen
Since the Postman tool might sound kinda non-PC this time I will use postwomen in the new terminal:
docker run --rm -p 4000:3000 liyasthomas/postwoman:latest
Now open http://127.0.0.1:4000/ to access postwomen UI and make a POST request to /note endpoint with Raw JSON BODY:
Check the response:
Make another post and by changing text this time:
| { | |
| "text": "My second prsma note", | |
| "categoryName": "Etc notes" | |
| } |
You can check in our SQL Lite viewer also. During development phases it is a very good idea to debug data using DB admin tool like pgAdmin, MySQL Workbench, DataGrip by JetBrains, and so on:
Now let's "undraft" first note:
Let's try to list undrafted:
And it is working:
Conclusion
As a guy who has been doing web development for years, I must admit that I've never seen more simple and more comfortable ORM. If someone thinks the price of simplicity is a limited feature I would say it is not about a Prisma. In this case, the cost of simplicity is clear and fresh minds of developers. It is thinking about package users. It is about making effort on pushing technologies forward.
When I used the Prisma the first time I was doing it with a feeling of happiness. Happiness is about living in the open world of changes where there is the road for real brains who make the beauty of technologies possible.
