DEV Community

loading...
Cover image for Interact with Relational Databases using Prisma

Interact with Relational Databases using Prisma

Francisco Mendes
I don't know about you but I'm just getting started. 🍩
・5 min read

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.

Prisma is not just an ORM, you as a developer have a whole range of tools that truly help you in your daily needs.

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
Enter fullscreen mode Exit fullscreen mode

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

npx prisma init
Enter fullscreen mode Exit fullscreen mode

Now your Prisma schema should look like this:

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

generator client {
  provider = "prisma-client-js"
}
Enter fullscreen mode Exit fullscreen mode

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"
}
Enter fullscreen mode Exit fullscreen mode

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.
}
Enter fullscreen mode Exit fullscreen mode

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.
}
Enter fullscreen mode Exit fullscreen mode

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
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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 });
});
Enter fullscreen mode Exit fullscreen mode

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 });
});
Enter fullscreen mode Exit fullscreen mode

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 });
});
Enter fullscreen mode Exit fullscreen mode

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 });
});
Enter fullscreen mode Exit fullscreen mode

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 });
});
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

If you would like to test the example in this article, just go to this link to clone the github repository.

Conclusion

As always, I hope I was brief in explaining things and that I didn't confuse you. Have a great day! 🧐 🀩

Discussion (2)

Collapse
sherrydays profile image
Sherry

Thanks for this

Collapse
franciscomendes10866 profile image
Francisco Mendes Author

I hope you liked it! 😊