An Object Relational Mapper (ORM) is a code library that plots the transfer of data stored in a database (usually relational) into objects represented in the code. Node.js works with some great ORM libraries like Sequelize, TypeORM, and Prisma.
In this post, we will learn how to use Prisma with the Express.js framework and PostgreSQL database. We will build a simple Quotes REST API to add and serve up programming-related quotes.
Let’s get started!
Introduction
As discussed, ORMs let us define our models as classes that map to tables in a database. Active Record and Data Mapper are two common patterns followed by ORMs. Both patterns require us to define classes to plot a connection to the database. However, there is a fundamental difference between them.
Active Record closely relates an application’s in-memory representation and database table. For instance, each field in the model will correspond to a field in the database table.
On the other hand, as the name suggests, Data Mapper loosely maps in-memory representation to a database table, decoupling the database and the model.
Prisma uses the Data Mapper pattern, similar to Doctrine ORM in the PHP world. With components like Prisma schema, client and migrate, it has the main bases covered.
Pros and Cons of Using an ORM
One of the most common pros of using an ORM is that you don't need to write SQL. Another advantage is that you can potentially switch databases without any issues, as vendor-specific logic is abstracted in an ORM.
The relation between rich, object-oriented business models is also defined well in the code. Without a doubt, the create, track, and rollback migrations are great reasons to vote for ORM usage.
On the flip side, using an ORM can cause performance issues. The N+1 problem is not hard to find in some ORMs. ORMs are made for general purposes, so they will add a layer of complexity to your application.
Another con of using an ORM is that the query builder can’t do some things that are pretty easy to do with raw SQL queries. Like many other things in software engineering, there will be a trade-off in using an ORM, so you have to choose wisely.
I have used multiple ORMs in my career. Once, I even had to rewrite an ORM-based implementation to raw SQL queries for a business because of some performance and data inconsistency issues. ORMs are an excellent tool for low to medium-level complexity and scale, but you might experience some problems at a higher load.
But this shouldn't stop you from wanting to use an ORM. Next up, we will look at some prerequisites for this tutorial, featuring Prisma, Express.js, and PostgreSQL.
Prerequisites
Before we dive into the code, here are some prerequisites:
- You should have Node.js running on your machine, ideally the LTS version.
- Knowledge and some experience of running npm commands is needed.
- A running local PostgreSQL database is required. You can run it with Docker if you wish.
- Basic knowledge of ORMs is beneficial but not necessary.
With those prerequisites out of our way, it's time to jump into some code.
Set Up Express.js
We'll begin by setting up Express.js for our programming languages Quotes API demo project. First, run the following to create a new project:
1mkdir nodejs-postgres-prisma
2cd nodejs-postgres-prisma
3npm init -y
You'll get output similar to the following:
1Wrote to /path/to/folder/nodejs-postgresql-prisma/package.json:
2
3{
4 "name": "nodejs-postgresql-prisma",
5 "version": "1.0.0",
6 "description": "Repo for Node.js (express) and postgresql database using prisma ORM",
7 "main": "index.js",
8 "scripts": {
9 "test": "echo \"Error: no test specified\" && exit 1"
10 },
11 "repository": {
12 "type": "git",
13 "url": "git+https://github.com/geshan/nodejs-postgresql-prisma.git"
14 },
15 "keywords": [],
16 "author": "",
17 "license": "ISC",
18 "bugs": {
19 "url": "https://github.com/geshan/nodejs-postgresql-prisma/issues"
20 },
21 "homepage": "https://github.com/geshan/nodejs-postgresql-prisma#readme"
22}
Next, install Express.js, our web framework for the Quotes REST API:
1npm install --save expressOn the successful execution of the above command, you'll see:
1npm notice created a lockfile as package-lock.json. You should commit this file.
2+ express@4.17.1
3added 50 packages from 37 contributors and audited 50 packages in 3.155s
4found 0 vulnerabilities
Hello World with Express
Next, we'll add an index.js file with an API showing that Express is working. Create the file in the root of the project and add the following:
1# index.js
2const express = require('express');
3const app = express();
4const port = process.env.PORT || 3000;
5
6app.get('/', (req, res) => {
7 res.json({message: 'alive'});
8});
9
10app.listen(port, () => {
11 console.log(`Listening to requests on port ${port}`);
12});
Let’s do a quick dissection of what the above code does. First, we initialize an Express app and declare a constant called port. If there is an environment variable called PORT we use it - otherwise, it defaults to 3000.
We add a GET route on the root that responds with a simple JSON. Finally, we start the Express server and listen to the specified port with a message for requests. We run the server with:
1node index.jsResulting in:
1Listening to requests on port 3000After that, if we hit http://localhost:3000 on a browser of our choice, it will show us something like the below:
The code I've shown you up to now is available in a pull request for your reference.
Next up, we'll set up a PostgreSQL database to create our tables for the Quotes API.
Set Up a Local PostgreSQL Database
There are multiple ways to run a PostgreSQL database on our local machine. If you already have one, you can move on to the next step. If you want to run a PostgreSQL database with Docker, use the following command:
1docker run --rm --name postgres-quotes -p 5432:5432 -e POSTGRES_PASSWORD=mysecretpassword -d postgres:13-alpineIt will give us this output:
1Unable to find image 'postgres:13-alpine' locally
213-alpine: Pulling from library/postgres
3Digest: sha256:ff384947eb9f5939b7fc5ef2ce620fad088999590973f05e6812037d163c770e
4Status: Downloaded newer image for postgres:13-alpine
571885633db053d9d70df7e3871595a0dd8be78575fbe0fefc926acd0072e4b5a
The docker run command creates a new docker container named postgres-quotes, exposing the container port 5432 to the local port 5432. The --rm parameter is added to remove the container when it stops.
As per the docs, we also set the password to be “mysecretpassword” for the default user postgres. Finally, in the docker run command, we opt to use the 13-alpine image of postgres because it is smaller than the default one.
Our database is up and running. Next up, we will add Prisma to our Node.js project and create our schema.
Add Prisma ORM to Your Node.js Project
Execute the following command:
1npm install prisma --save-devIt will install Prisma as a dev dependency and result in output similar to this:
1
2> prisma@2.26.0 preinstall /path/to/project/nodejs-postgresql-prisma/node_modules/prisma
3> node scripts/preinstall-entry.js
4
5
6> prisma@2.26.0 install /path/to/project//nodejs-postgresql-prisma/node_modules/prisma
7> node scripts/install-entry.js
8
9
10> @prisma/engines@2.26.0-23.9b816b3aa13cc270074f172f30d6eda8a8ce867d postinstall /path/to/project//nodejs-postgresql-prisma/node_modules/@prisma/engines
11> node download/index.js
12
13+ prisma@2.26.0
14added 2 packages from 1 contributor and audited 52 packages in 4.319s
15found 0 vulnerabilities
After that, initialize the Prisma schema with the following command:
1npx prisma initOn initializing the Prisma schema, we will get this output:
1✔ Your Prisma schema was created at prisma/schema.prisma
2 You can now open it in your favorite editor.
3
4Next steps:
51. Set the DATABASE_URL in the .env file to point to your existing database. If your database has no tables yet, read https://pris.ly/d/getting-started
62. Set the provider of the data source block in `schema.prisma` to match your database: postgresql, mysql, sqlserver or sqlite.
73. Run prisma db pull to turn your database schema into a Prisma data model.
84. Run prisma generate to install Prisma Client. You can then start querying your database.
9
10More information in our documentation:
11https://pris.ly/d/getting-started
Consequently, we can add our local database connection string in the .env file created. It will look as follows after the change:
1#.env
2# Environment variables declared in this file are automatically made available to Prisma.
3# See the documentation for more detail: https://pris.ly/d/prisma-schema#using-environment-variables
4
5# Prisma supports the native connection string format for PostgreSQL, MySQL, SQL Server and SQLite.
6# See the documentation for all the connection string options: https://pris.ly/d/connection-strings
7
8DATABASE_URL="postgresql://postgres:mysecretpassword@localhost:5432/postgres?schema=quotes"
The only change here is the DATABASE_URL. For security reasons, it is best to pass the database URL as an environment variable in a production-like environment rather than put the credentials in a file.
Add Models and Run Prisma Migration
We will open the prisma.schema file in the prisma folder and define our database tables for Quotes and Authors with their relation.
As one author can have multiple quotes and one quote will always have only one author, it will be defined as:
1#prisma/schema.prisma
2
3// This is your Prisma schema file,
4// learn more about it in the docs: https://pris.ly/d/prisma-schema
5
6datasource db {
7 provider = "postgresql"
8 url = env("DATABASE_URL")
9}
10
11generator client {
12 provider = "prisma-client-js"
13}
14
15model Author {
16 id Int @id @default(autoincrement())
17 name String @unique
18 Quotes Quote[]
19}
20
21model Quote {
22 id Int @id @default(autoincrement())
23 quote String @unique
24 author Author @relation(fields: [authorId], references: [id])
25 authorId Int
26}
We have added two tables. The first one is the author table with id and name, and the name of the author is unique. The relation is that one author can have one or more quotes.
The following table is the quote table, which has an auto-increment ID and quote that is a unique string. It also has an author id to show which author said the quote.
To convert these models into PostgreSQL database tables, run the following command:
1npx prisma migrate dev --name initThis generates the migration SQL that creates the tables and runs it against the specified database, resulting in this output:
1Environment variables loaded from .env
2Prisma schema loaded from prisma/schema.prisma
3Datasource "db": PostgreSQL database "postgres", schema "quotes" at "localhost:5432"
4
5The following migration(s) have been created and applied from new schema changes:
6
7migrations/
8 └─ 20210702122209_init/
9 └─ migration.sql
10
11Your database is now in sync with your schema.
12
13Running generate... (Use --skip-generate to skip the generators)
14
15> prisma@2.26.0 preinstall /path/to/project/nodejs-postgresql-prisma/node_modules/prisma
16> node scripts/preinstall-entry.js
17
18
19> prisma@2.26.0 install /path/to/project/nodejs-postgresql-prisma/node_modules/prisma
20> node scripts/install-entry.js
21
22+ prisma@2.26.0
23updated 1 package and audited 52 packages in 1.987s
24found 0 vulnerabilities
25
26
27> @prisma/client@2.26.0 postinstall /path/to/project/nodejs-postgresql-prisma/node_modules/@prisma/client
28> node scripts/postinstall.js
29
30+ @prisma/client@2.26.0
31added 2 packages from 1 contributor and audited 54 packages in 6.499s
32found 0 vulnerabilities
33
34
35✔ Generated Prisma Client (2.26.0) to ./node_modules/@prisma/client in 130ms
If the migration is successful, it will have installed the Prisma client and added it to our package.json file. It will also create a migration.sql file that looks as shown below:
1#prisma/migrations/20210702122209_init/migration.sql
2-- CreateTable
3CREATE TABLE "Author" (
4 "id" SERIAL NOT NULL,
5 "name" TEXT NOT NULL,
6
7 PRIMARY KEY ("id")
8);
9
10-- CreateTable
11CREATE TABLE "Quote" (
12 "id" SERIAL NOT NULL,
13 "quote" TEXT NOT NULL,
14 "authorId" INTEGER NOT NULL,
15
16 PRIMARY KEY ("id")
17);
18
19-- CreateIndex
20CREATE UNIQUE INDEX "Author.name_unique" ON "Author"("name");
21
22-- CreateIndex
23CREATE UNIQUE INDEX "Quote.quote_unique" ON "Quote"("quote");
24
25-- AddForeignKey
26ALTER TABLE "Quote" ADD FOREIGN KEY ("authorId") REFERENCES "Author"("id") ON DELETE CASCADE ON UPDATE CASCADE;The above database tables match the model that we defined in the prisma.schema file. The code in this part is available as a pull request here.
Below, you can see how the schema looks after importing the generated SQL to dbdiagram.io:
Next, we will seed the database with one author and a couple of quotes from that author.
How to Seed the PostgreSQL Database
To seed the PostgreSQL database with some initial data, we will create a seed.js file in the same folder where we have our prisma.schema file. Create the file and add the following to it:
1# prisma/seed.js
2const { PrismaClient } = require('@prisma/client');
3const prisma = new PrismaClient();
4
5(async function main() {
6 try {
7 const martinFowler = await prisma.author.upsert({
8 where: { name: 'Martin Fowler' },
9 update: {},
10 create: {
11 name: 'Martin Fowler',
12 Quotes: {
13 create: [
14 {
15 quote: 'Any fool can write code that a computer can understand. Good programmers write code that humans can understand.',
16 },
17 {
18 quote: `I'm not a great programmer; I'm just a good programmer with great habits.`,
19 },
20 ],
21 },
22 },
23 });
24
25 console.log('Create 1 author with 2 quotes: ', martinFowler);
26 } catch(e) {
27 console.error(e);
28 process.exit(1);
29 } finally {
30 await prisma.$disconnect();
31 }
32})();
The above seed file instantiates the Prisma client and then calls an Immediately Invoked Function Expression (IIEF) called main. In the main function, we upsert an author, Martin Fowler, with two amazing quotes.
If there is an error, it is logged, and the process exits. In the case of either success or error, we always disconnect from the database in the finally part of the main function.
To seed the database with the data, run:
1npx prisma db seed --preview-featureThe above command will result in something like:
1Environment variables loaded from .env
2Prisma schema loaded from prisma/schema.prisma
3Running seed from "prisma/seed.js" ...
4Result:
5Create 1 author with 2 quotes: { id: 1, name: 'Martin Fowler' }
6
7🌱 Your database has been seeded.
Read more about Prisma Migrate in the official docs. You can reference the seed changes in this pull request.
Hurray! We have one author and two related quotes from that author in the database. Now, we will expose these quotes in the form of JSON over a REST API.
API to View Quotes
We added quotes via a REST API endpoint with a GET call. We will change the index.js file we created in a previous step with a Hello world API to add the new GET Quotes API.
Make the following changes to index.js:
1#index.js
2const express = require('express');
3const app = express();
4const port = process.env.PORT || 3000;
5
6const { PrismaClient } = require('@prisma/client');
7const prisma = new PrismaClient();
8
9app.get('/', (req, res) => {
10 res.json({message: 'alive'});
11});
12
13app.get('/quotes', async (req, res) => {
14 const currentPage = req.query.page || 1;
15 const listPerPage = 5;
16 const offset = (currentPage - 1) * listPerPage;
17
18 const allQuotes = await prisma.quote.findMany({
19 include: { author: true },
20 skip: offset,
21 take: listPerPage,
22 });
23
24 res.json({
25 data: allQuotes,
26 meta: {page: currentPage}
27 });
28});
29
30app.listen(port, () => {
31 console.log(`Listening to requests on port ${port}`);
32});
The main change here is that we instantiated the Prisma client. We also added the /quotes GET API endpoint, which gets the quotes data with its authors using Prisma’s findMany method. We paginate the quotes with 5 per page — that's why we use skip and take in the findMany parameters. There are other ways to paginate the rows with Prisma. We are opting for the offset-based approach in this example.
At this point, we can rerun the app with:
1node index.jsAnd if we hit http://localhost:3000/quotes on a browser, we'll see output as shown below:
1{
2 "data": [
3 {
4 "id": 1,
5 "quote": "Any fool can write code that a computer can understand. Good programmers write code that humans can understand.",
6 "authorId": 1,
7 "author": {
8 "id": 1,
9 "name": "Martin Fowler"
10 }
11 },
12 {
13 "id": 2,
14 "quote": "I'm not a great programmer; I'm just a good programmer with great habits.",
15 "authorId": 1,
16 "author": {
17 "id": 1,
18 "name": "Martin Fowler"
19 }
20 }
21 ],
22 "meta": {
23 "page": 1
24 }
25}
It may not be formatted as above, but the data is pulled from the table and served up as JSON with effectively 12 lines of code and no written SQL.
If we use AppSignal, we can also find the exact query and its performance on production. AppSignal has a magic dashboard for Node.js and PostgreSQL as well.
The code for GET Quotes API is available in this pull request.
Now we'll bring in a create Quotes API to add more quotes to our service.
Introduce a POST API to Add Quotes to PostgreSQL
To be able to add Quotes to the PostgreSQL database, we will introduce a POST API.
We'll add a route in the index.js file. First, add the following line after the express app is defined:
1#indes.js:4
2app.use(express.json());
By adding the JSON middleware, express can now parse the JSON sent in the request body. We will add a route to handle additional Quotes as a POST quotes API endpoint, as follows:
1#index.js:30
2app.post('/quotes', async (req, res) => {
3 const authorName = req.body.author;
4 const quote = {
5 quote: req.body.quote
6 };
7
8 if (!authorName || !quote.quote) {
9 return res.status(400).json({message: 'Either quote or author is missing'});
10 }
11
12 try {
13 const message = 'quote created successfully';
14 const author = await prisma.author.findFirst({
15 where: { name: authorName }
16 });
17
18 if(!author) {
19 await prisma.author.create({
20 data: {
21 'name': authorName,
22 Quotes: {
23 create: quote
24 }
25 }
26 });
27 console.log('Created author and then the related quote');
28 return res.json({message});
29 }
30
31 await prisma.quote.create({
32 data: {
33 quote: quote.quote,
34 author: { connect: { name: authorName } }
35 }
36 });
37 console.log('Created quote for an existing author');
38 return res.json({message});
39 } catch(e) {
40 console.error(e);
41 return res.status(500).json({message: 'something went wrong'});
42 }
43});
The main logic here is to check if both the quote and author are in the request body. If that basic validation passes, we check if the author exists.
If the author does not exist, we create them and relate the quote to the author. If the author exists, we just create the quote and relate it to the existing author using the connect option in Prisma.
If there is an error on the server-side, we send back a 500 response code with a simple message and log the error for our reference. To test it out when the server is running, we will hit the API with the following curl:
1curl -i -X POST -H 'Accept: application/json' -H 'Content-type: application/json' http://localhost:3000/quotes --data '{"quote":"Before software can be reusable it first has to be usable.","author":"Ralph Johnson"}'It will come back to us with the following output:
1HTTP/1.1 200 OK
2X-Powered-By: Express
3Content-Type: application/json; charset=utf-8
4Content-Length: 40
5ETag: W/"28-5h9zKgCDdv2FIu4KoJVfcy36GpQ"
6Date: Sat, 03 Jul 2021 12:28:01 GMT
7Connection: keep-alive
8Keep-Alive: timeout=5
9
10{"message":"quote created successfully"}
As the author is not there, the quote creates them. If we try the following curl command:
1curl -i -X POST -H 'Accept: application/json' -H 'Content-type: application/json' http://localhost:3000/quotes --data '{"quote":"A heuristic we follow is that whenever we feel the need to comment something, we write a method instead.","author":"Martin Fowler"}'It will not create the author, it will just add a quote and relate it to the existing author id 1 with the name Martin Fowler.
I hit the API with some more curl commands, and after adding the sixth quote, I tried http://localhost/quotes?page=2 to test out the pagination. It gave me only one quote — the sixth one I had added, as follows:
The code that adds the create Quotes API endpoint is accessible in this pull request.
I would strongly recommend you add the update and delete functionality. The Prisma docs can help you with that.
Please keep in mind that the validation done for this tutorial is super basic to keep things simple. In a real-life project, I recommend that you use a full-on validation library like Joi.
This brings us to wrapping up.
Wrap-up
We built a Quotes API with Express.js and Prisma ORM, using a PostgreSQL database running on Docker. I hope this gave you some new insights and was a good starting point to explore all of the software used in this tutorial.
Just keep in mind that an ORM is a great tool to reach for when your project is relatively small or mid-sized. In the case of lots of requests, the general nature of an ORM stands in the way of high performance.
As mentioned earlier, using an ORM often involves a trade-off — a balance between convenience and control. Be very careful with what you are leaving behind and make sure what you gain is worth it.
To finish up, I suggest you further explore some great Prisma ORM features. For instance, query optimization is a good one to read about.
Happy coding!
P.S. If you liked this post, subscribe to our new JavaScript Sorcery list for a monthly deep dive into more magical JavaScript tips and tricks.
P.P.S. If you need an APM for your Node.js app, go and check out the AppSignal APM for Node.js.
