This article is written for someone with 0 backend experience, so anyone should be able to learn from it!
Working on a full-stack project recently, I found some great tools that allow devs to easily generate database structures, handling much of the complexity for you. In this article, we will create a simple express
API using NodeJS, Prisma, and PostgreSQL (don't get scared by the list of technologies!)
SQL vs NoSQL
SQL and NoSQL are two types of databases.
SQL
SQL uses data tables, sort of like an Excel or Google Sheets spreadsheet. In relational databases, tables can be linked to each other through relation fields.
For example, let's consider a database storing info about cars and their owners. It could be structured like this:
owner
table
id | name | age | carId |
---|---|---|---|
1 | Steven | 24 | 143 |
2 | Bob | 41 | 144 |
3 | Sarah | 35 | 145 |
car
table
id | make | model | year | ownerId |
---|---|---|---|---|
143 | Chevrolet | Corvette | 2020 | 1 |
144 | Honda | Civic | 2010 | 2 |
145 | Hyundai | Palisade | 2017 | 3 |
In this example, the carId
column in the owner
table refers to the id
column of the car
table. The ownerId
column of the car
table refers to the id
column of the owner table. id
is the primary key in each table, and ownerId
and carId
are "foreign keys" because they refer to a different table's primary key.
NoSQL
NoSQL is a document-based type of database, where generally documents are similar to JSON objects. This structure might feel more familiar to frontend developers, but both types of databases have advantages and disadvantages. We'll use SQL in this tutorial, so I won't go into NoSQL too much here. You can read more about their differences online by searching for comparisons!
PostgreSQL and Database Installation
PostgreSQL (sometimes called Postgres) is an extremely popular relational database following SQL specs. It allows us to set up databases with tables and connect them with easily understandable relations.
First, we'll install Postgres so that you can set up databases on your computer. You can find the download for your OS here: https://www.postgresql.org/download/.
When you go through the download, stick with default settings. You will be asked for a password for the root user: no matter what you choose, ⚠make sure to write down the password you selected⚠. The default username is postgres
.
After the installation, you can log in to your local database instance and play with it on the command line! Search your computer for a program called psql
, which is a Terminal/CMD prompt application that lets you interact with your postgres database. Open the program and login by pressing Enter to use the defaults and then entering your password. You should see this when you're done:
Now we can test out some commands to get familiar with Postgres. First, type \l
and then Enter to list the existing databases on your machine. Postgres should come with a few created by default.
While I said we wouldn't write a single SQL query, I would recommend creating a separate database for this project. In PSQL, type CREATE DATABASE prismademo;
and press Enter. Then you can connect to the new database with \c prismademo
.
You can spend some time creating databases and tables if you want to - there are plenty of simple guides you can find by searching "how to create databases in psql", "how to create tables in psql", etc.
⚠Heads up! Most SQL queries you enter in PSQL need to end in a semicolon. Don't forget that part!⚠
Server Setup
In a normal terminal or CMD prompt (not psql), create a folder for your JavaScript code that we will use to create the API server. I named my folder prisma-demo
. cd
into that folder, and run npm init
. Follow through the steps of initializing your project. Open your project folder in your favorite IDE (if you use VS Code, you can type the command code .
in this directory to open VS Code here). You should see a package.json
with the info you just entered.
Prisma Integration
Install Prisma
We're finally at the keystone, Prisma, which will:
- connect our database and JS server
- generate tables in our database for us
- generate JavaScript functions and TypeScript types for interacting with our database (no worries if you don't use TypeScript, keep reading!)
First, do npm install prisma -D
to install Prisma.
Next, do npx prisma
to verify Prisma is installed, and then npx prisma init
. The second command will generate a Prisma schema file in prisma/schema.prisma
and a .env
with a sample database URL.
⚠ Next we should add the .env
to .gitignore
. Create a .gitignore
file with this content:
.env
node_modules
Now you can safely run git init
and commit your project if you want to.
Fix the database URL
Open your .env
and replace the sample URL with this one:
DATABASE_URL="postgresql://postgres:INSERTYOURPASSWORD@localhost:5432/prismademo?schema=public"
If you didn't create a prismademo
database earlier, replace that part with postgres
before ? in the url.
Fill in the Prisma Schema
Open schema.prisma
. Here, we can define data models, and then use the Prisma CLI to generate the required database tables and JavaScript functions based on the data models.
We'll use the car example from above. We'll say that each person can own multiple cars, and each car can only have one owner, meaning owners/cars have a one-to-many relationship. You can read about all of the possible types of relationships here: https://www.prisma.io/docs/concepts/components/prisma-schema/relations
Add these lines to your schema to set up the models. Note the relation: each owner has a list of cars, and each car can have one owner, specified by ownerId
. Note that it looks sort of similar to a GraphQL Schema!
model Owner {
// Prisma will automatically create an ID for each entry for us
id Int @id @default(autoincrement())
name String
age Int
// Each Owner can have multiple cars
Car Car[]
}
model Car {
id Int @id @default(autoincrement())
make String
model String
year Int
// Each Car can have only 1 owner
ownerId Int
owner Owner @relation(fields: [ownerId], references: [id])
}
We use the @id
annotation to signify that this is the primary key field for this model. @default(autoincrement())
means that we don't need to provide an ID when we create an owner or car, it will be automatically assigned to 1 + the last ID
.
Heads up! There's a great VS Code extension to help format and maintain this file: https://marketplace.visualstudio.com/items?itemName=Prisma.prisma
Migrate the Schema to the Database
Now that we've created the Prisma Schema, we need to run a command so that Prisma will actually create the tables for this schema in the database. Run npx prisma migrate dev --name "Create initial schema"
in your project directory terminal/cmd prompt. You can change the name text, but keep it descriptive! (Note: if the command freezes at "Running generate...
, stop the process and run npx prisma generate
.)
Go back to your db in PSQL. Now run \dt
, and you should see the tables created from our Prisma Schema! How cool is that? 😎
You can also run npx prisma studio
to view your database structure in an interactive web view. I would recommend trying this so that you can see the generated database visually!
When you run a migration or prisma generate
, Prisma also creates fully-typed JavaScript functions for us to interact with the database without writing any SQL manually! Let's see how we can use those functions to create an API.
REST API to Create an Owner and a Car
If you're a JS developer, you may already be familiar with this part. Express is an NPM package for writing servers.
Run npm i express body-parser
to install express and body-parser, which we'll use to read JSON input in the API.
Create a server.js
file in your project root folder. Add this content:
const express = require("express");
const app = express();
const bodyParser = require('body-parser')
const port = 3030;
const jsonParser = bodyParser.json()
app.listen(port, () => {
console.log(`Example app listening at http://localhost:${port}`)
})
First, we'll create a REST API route to create a user and a car at the same time and link them together.
Add the Prisma Client import and instantiation to your server.js
file, outside of any route, underneath const port = ...
const { PrismaClient } = require('@prisma/client')
const prisma = new PrismaClient()
Next, create a POST route:
app.post('/owner', jsonParser, async (req, res) => {
})
When a web-app or REST client sends a POST request to this express server with the URL we specified, the server will run this function.
We'll use the functions which Prisma generated to create an owner and a car in our database.
The Prisma create functions follow the form prisma.<MODEL NAME>.create
, so in our case we'll use prisma.owner.create(...)
. If you're following along, you'll see that our IDE knows that our model is called owner
and autosuggests it, because Prisma has generated these functions from our schema.
Prisma will suggest fields for the owner model in the data
argument field, like this:
app.post("/owner", jsonParser, async (req, res) => {
const body = req.body
const newOwner = await prisma.owner.create({
data: {
name: body.name,
age: body.age
}
})
}
Another great feature of Prisma is that we can create the Car database entry and link it to this Owner in the same function call. This is how:
const newOwner = await prisma.owner.create({
data: {
name: body.name,
age: body.age,
Car: {
create: {
make: body.car.make,
model: body.car.model,
year: body.car.year,
},
},
},
});
Lastly, we'll add try/catch for error handling, and a response:
app.post("/owner", jsonParser, async (req, res) => {
const body = req.body
try {
const newOwner = await prisma.owner.create({
data: {
name: body.name,
age: body.age,
Car: {
create: {
make: body.car.make,
model: body.car.model,
year: body.car.year,
},
},
},
});
res.status(200).json({owner: newOwner})
} catch (e) {
console.log(e);
res.status(503).json({error: "Failed to create Owner and Car."})
}
});
Something to Notice: We didn't specify ownerId in the car JSON, or carID in the owner JSON. Since we are creating these with one nested in the other in one function call, Prisma will link the IDs for us! This is enabled by the @relation
fields we added in the schema.
Testing it Out
Run node server.js
in your project directory to run your server. You can then use any HTTP Client to test this API route. For example, you can use cURL, Postman, or a NodeJS file with node-fetch
.
No matter how you test it, run a request similar to the following POST request, and you should get the given response!
If you also want to get the car information in the response, you can add the include
field in your prisma.owner.create
call to tell Prisma to include the connected Car object:
const newOwner = await prisma.owner.create({
data: {
...
},
include: {
Car: true
}
});
Restart the Node server after adding this field, and re-run the request to see the created car!
🎉Congratulations! At this point, you've set up a database, created tables from a schema, and added a REST API to store information in the database.
View your Database
Back in the psql
terminal, you can run TABLE "Car";
to print out the Car table. You should see output in tabular form like this:
prismademo=# TABLE "Car";
id | make | model | year | ownerId
----+-----------+----------+------+---------
1 | Chevrolet | Corvette | 2020 | 1
2 | Chevrolet | Camaro | 2020 | 2
(2 rows)
You can also view the data in Prisma Studio by running npx prisma studio
again!
Add a Route to GET Stored Info
Lastly, we'll add a REST API route to GET a car, so your frontend can read car data. The requested Car ID will be part of the URL.
The find___
functions in Prisma allow us to use a filter to get database entries meeting a certain condition. We'll use prisma.car.findFirst(...)
to find the first car with the given ID.
Underneath the POST route we added, add:
app.get("/car/:carId", async (req, res) => {
try {
const car = await prisma.car.findFirst({
where: {
id: Number(req.params.carId)
},
include: {
owner: true
}
})
res.status(200).json({ car });
} catch (e) {
console.log(e);
res.status(503).json({ error: "Failed to get car." });
}
});
We name the URL parameter carId
using the :carId
in the URL. We don't need bodyParser
because we don't need to parse POSTed JSON, like we did when we created the owner/car.
We use where
to tell Prisma to get the car with the matching ID. You'll notice autosuggest is supported here too. We use Number(...)
to convert the URL parameter from a string to a number, because the ID is an Int according to our schema. Finally, we use include
to get the owner details with the car details.
Restart your server. You should be able to go to http://localhost:3030/car/1
in your web browser or HTTP client to view the first car you created!
Recap and Taking it Further!
That was a lot of information, very fast. Great work so far!
To sum it up, in this tutorial you:
- Installed PostgreSQL
- Created a database for your project
- Created database tables from a Schema using Prisma
- Wrote an
express
server REST API - Added routes to the API to create/store and get database entries.
Some ideas for taking this further:
- Try adding new models in the schema (maybe a Dealership, or create a model for the Make instead of just astring)
- New types of relations, or new field types. Maybe a
purchasedAt
date field on the car, or allow a car to have more than one owner with a many-to-many relationship. - Add more API functionality. For example, you can add a route to add another car to an existing owner, or delete a car from an owner.
I would definitely recommend checking out the Prisma documentation, especially different types of relations, data types, tradeoffs of Prisma, and more: https://www.prisma.io/docs/
You can also view my source code here: https://github.com/aej11a/prisma-demo
Thanks for reading! Follow me here or on Twitter for more updates and other content. Feel free to DM me for questions!
Top comments (5)
Nice. I just started looking into ORMs. Still debating whether to use sequelize or prisma.
Also, bodyparser is not necessary if you're using the latest version of express.
Use
express.json()
insteadHi Joseph 👋🏾
Alex here from the Prisma team.
I would encourage you to try out both ORMs and share your overall experience. My opinion is going to be biased but I would encourage you to try out Prisma. Some of the benefits Prisma offers are query auto-completion, type-safety when composing your queries and intuitive data modeling.
If you get stuck, feel free to reach out 🙂
Well, I'm glad to tell you that I chose Prisma.
Sequelize is good too but I lost it when I got to defining associations. It's really confusing to me. After going through the Prisma docs, I was able to understand how to define associations.
So far, Prisma works well for me and the VSCode extension makes it easier to work with.
Skip both and use MikroORM.
Great information. If it is graphQL, again in graphQL schema, prima call can be used instead of direct SQL queries?