Backend Basics for Frontend Engineers: Dive into SQL and APIs with NodeJS, Prisma, and PostgreSQL
Tags: prisma, fullstack, webdev, javascript
Published: 2021-05-31 20:10:14

**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/.

![Installation page](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5o2j3u76vjp820ap0ra7.PNG)

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:

![PSQL Shell](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/evdeqzc0npzdzqjg1w6f.PNG)

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](https://www.prisma.io/), 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:

```js
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 = ...`
```js
const { PrismaClient } = require('@prisma/client')
const prisma = new PrismaClient()
```

Next, create a POST route:
```js
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*.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dckes57f7g97oqu5pg7u.PNG)

Prisma will suggest fields for the owner model in the `data` argument field, like this:
```js
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:

```js
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:
```js
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!
![Sample POST Request](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8itvqq7rf9qesjl5vgyq.PNG)

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:
```js
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!

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kmj3pb667ge088t2cc4r.png)

🎉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: 
```js
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](https://www.prisma.io/docs/concepts/components/prisma-schema/relations), [data types](https://www.prisma.io/docs/concepts/components/prisma-schema/data-model), [tradeoffs of Prisma](https://www.prisma.io/docs/concepts/overview/should-you-use-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](https://twitter.com/ajones55555) for more updates and other content. Feel free to DM me for questions!*