Backend Basics for Frontend Engineers: Dive into SQL and APIs with NodeJS, Prisma, and PostgreSQL

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:

1. connect our database and JS server
2. generate tables in our database for us
3. 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:

1. Installed PostgreSQL
2. Created a database for your project
3. Created database tables from a Schema using Prisma
4. Wrote an express server REST API
5. 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!

Comments

[Joseph Odunsi]

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() instead

[Ruheni Alex]

Hi 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 🙂

[Joseph Odunsi]

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.

[Justin Etighe]

Skip both and use MikroORM.

[guruprasath-s]

Great information. If it is graphQL, again in graphQL schema, prima call can be used instead of direct SQL queries?