Interact with Relational Databases using Prisma

Today I'm going to talk about an ORM that I love a lot. The development experience is simply amazing and cannot be compared to other alternatives.

One of the things I find extraordinary about this ORM that sometimes gives a little headache to other alternatives is how intuitive and simple the migration process becomes.

I think we've all used ORM's where we thought "this doesn't seem like such a natural process", but with Prisma it's all so sweet. But this is just my opinion.

However, today I won't explain in depth each of the wonders Prisma keeps in her treasure chest. The intent of today's article is to create a simple CRUD using Prisma as our ORM.

One of the things you will notice is that we will have very little boilerplate and the code will be cleaner and easier to read compared to other alternatives.

Let's code

Let's start by installing our dependencies:

npm install fastify prisma

Then let's run the following command to create to configure our Prisma project (creating our Prisma schema):

npx prisma init

Now your Prisma schema should look like this:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

However, my provider in this article will change it to sqlite and my database url will look like this:

datasource db {
  provider = "sqlite"
  url      = "file:./dev.db"
}

generator client {
  provider = "prisma-client-js"
}

As you may have noticed, we just made the configuration of the connection to the database with a few lines of code.

Now we can start working on our Model and the way to do it in Prisma is just delicious. Let's create a Model called Country:

datasource db {
  provider = "sqlite"
  url      = "file:./dev.db"
}

generator client {
  provider = "prisma-client-js"
}

model Country {
  // Stuff comes here.
}

In our model we will have fields with the timestamp of when a country was added and updated.

datasource db {
  provider = "sqlite"
  url      = "file:./dev.db"
}

generator client {
  provider = "prisma-client-js"
}

model Country {
  id         Int      @id @default(autoincrement())
  createdAt  DateTime @default(now())
  updatedAt  DateTime @updatedAt
  // More stuff comes here.
}

And we're going to have other fields, such as name, official language and population number.

datasource db {
  provider = "sqlite"
  url      = "file:./dev.db"
}

generator client {
  provider = "prisma-client-js"
}

model Country {
  id         Int      @id @default(autoincrement())
  createdAt  DateTime @default(now())
  updatedAt  DateTime @updatedAt
  name       String
  language   String
  population Int
}

Now with all this done we can perform our first migration, for that we will run the following command:

npx prisma migrate dev --name initial

With this, in the prisma folder, you should have created a file called dev.db.

Now we just need to install the Prisma client:

npm install @prisma/client

And now we can start creating our Api using Fastify framework. Let's start by creating the module that will be used to run our application.

Then we will import our app module (which has yet to be created) and we will create a function that will initialize our process.

However, if an error occurs during the initialization of the process, the process will be terminated.

// @src/main.js

import app from "./app.js";

const start = async () => {
  try {
    await app.listen(3333);
  } catch (err) {
    console.error(err);
    process.exit(1);
  }
};

start();

Now we can start working on our app module, which will hold all our application logic.

First let's import Fastify and Prisma Client, then just instantiate our PrismaClient.

// @src/app.js

import Fastify from "fastify";
import { PrismaClient } from "@prisma/client";

const app = Fastify();
const prisma = new PrismaClient();

// More stuff comes here

export default app;

Now let's create a route to fetch all the data we have in our database table. For this we will use Prisma's .findMany() method.

app.get("/country", async (request, reply) => {
  const countries = await prisma.country.findMany();
  return reply.send({ countries });
});

As you may have noticed, our table is empty. So let's insert some countries, for this to be done, we need to use Prisma's .create() method and pass the body of the http request.

app.post("/country", async (request, reply) => {
  const country = await prisma.country.create({ data: { ...request.body } });
  return reply.send({ country });
});

With a country added to the table, we now need to fetch just that country. So in our route we will have a single parameter, which in this case will be the id. Then we will use Prism's .findUnique() method.

app.get("/country/:id", async (request, reply) => {
  const { id } = request.params;
  const country = await prisma.country.findUnique({
    where: { id: Number(id) },
  });
  return reply.send({ country });
});

Now that we can get just the country we want, let's try to update it. For this we need to create a route that has the id parameter.

Lastly we will use the .update() method to which we will pass the id of the country we intend to update and the object with the updated data of the respective country.

app.put("/country/:id", async (request, reply) => {
  const { id } = request.params;
  const country = await prisma.country.update({
    where: { id: Number(id) },
    data: { ...request.body },
  });
  return reply.send({ country });
});

Last but not least, we can only delete a record from a country of our choice.

For this we will create a new route that has the parameter id. Finally we will use the .delete() method, to which we will pass the id of the country we want to delete.

app.delete("/country/:id", async (request, reply) => {
  const { id } = request.params;
  const country = await prisma.country.delete({ where: { id: Number(id) } });
  return reply.send({ country });
});

The final code for our app module should look like the following:

// @src/app.js

import Fastify from "fastify";
import { PrismaClient } from "@prisma/client";

const app = Fastify();
const prisma = new PrismaClient();

app.get("/country", async (request, reply) => {
  const countries = await prisma.country.findMany();
  return reply.send({ countries });
});

app.post("/country", async (request, reply) => {
  const country = await prisma.country.create({ data: { ...request.body } });
  return reply.send({ country });
});

app.get("/country/:id", async (request, reply) => {
  const { id } = request.params;
  const country = await prisma.country.findUnique({
    where: { id: Number(id) },
  });
  return reply.send({ country });
});

app.put("/country/:id", async (request, reply) => {
  const { id } = request.params;
  const country = await prisma.country.update({
    where: { id: Number(id) },
    data: { ...request.body },
  });
  return reply.send({ country });
});

app.delete("/country/:id", async (request, reply) => {
  const { id } = request.params;
  const country = await prisma.country.delete({ where: { id: Number(id) } });
  return reply.send({ country });
});

export default app;