The Info Docx: Introduction to Prisma ORM With Express & PostgreSQL

Introduction

Prisma is an Object-Relational Mapping (ORM) tool that allows developers to work with a database using a familiar programming language. It can be used with various databases, including PostgreSQL, and can be integrated into web applications built with popular frameworks such as Express.

Prisma with Express + PostgreSQL

To use Prisma with an Express app and a PostgreSQL database, you will need to install the necessary dependencies and set up your database.

Initialization

First, make sure you have Node.js and PostgreSQL installed on your machine. Then, create a new Node.js project and install the required dependencies:

npm init -y
npm install express @prisma/client graphql

Next, create a new PostgreSQL database and add a prisma schema to it. You can do this using the PostgreSQL command-line interface or a tool like pgAdmin.

Once your database is set up, you can generate a Prisma client using the following command:

npx prisma init

This will create a prisma directory in your project and prompt you to select a template for your Prisma client. Select the Express template and choose PostgreSQL as the database.

Database Schema

Next, you will need to define your database schema using Prisma's GraphQL-based schema language. Create a file called prisma in the prisma directory and define your models and their relationships:

model User {
  id        Int     @id @default(autoincrement())
  createdAt DateTime @default(now())
  email     String  @unique
  name      String
  posts     Post[]
}

model Post {
  id        Int     @id @default(autoincrement())
  createdAt DateTime @default(now())
  title     String
  content   String
  author    User    @relation(fields: [authorId], references: [id])
  authorId  Int
}

This defines a User model with an id, createdAt, email, and name field, and a Post model with an id, createdAt, title, content, and authorId field. The User model also has a one-to-many relationship with the Post model, meaning a user can have multiple posts.

CRUD Operations

Once you have defined your schema, you can use the Prisma client to create, read, update, and delete (CRUD) data in your database.

To create a new user, for example, you can use the prisma.user.create() method:

const newUser = await prisma.user.create({
  data: {
    email: 'john@example.com',
    name: 'John',
  },
});

To query for all users, you can use the prisma.user.findMany() method:

const users = await prisma.user.findMany();

You can also use the Prisma client to perform more complex queries, such as filtering and sorting, using GraphQL syntax. For example, to query for all users with the name "John", sorted by their email in ascending order, you can use the following code:

 const users = await prisma.user.findMany({
  where: {
    name: 'John',
  },
  orderBy: {
    email: 'asc',
  },
});

You can also use the Prisma client to update and delete data. To update a user, you can use the prisma.user.update() method:

 const updatedUser = await prisma.user.update({
  where: {
    id: 1,
  },
  data: {
    name: 'Jane',
  },
});

To delete a user, you can use the prisma.user.delete() method:

 const deletedUser = await prisma.user.delete({
  where: {
    id: 1,
  },
});

Once you have set up your Prisma client and defined your schema, you can use it to perform CRUD operations on your database from within your Express app. This can make it easier to work with your database and ensure that your data is consistent and well-structured.

Here is a example express app for you to get started with.

schema.prisma file

model User {
  id        Int     @id @default(autoincrement())
  createdAt DateTime @default(now())
  email     String  @unique
  name      String
}

app.js file

const express = require('express');

const { PrismaClient } = require('@prisma/client');

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

app.get('/users', async (req, res) => {
  // Query for all users
  const users = await prisma.user.findMany();
  res.json(users);
});

app.post('/users', async (req, res) => {
  // Create a new user
  const newUser = await prisma.user.create({
    data: {
      email: req.body.email,
      name: req.body.name,
    },
  });
  res.json(newUser);
});

app.put('/users/:id', async (req, res) => {
  // Update an existing user
  const updatedUser = await prisma.user.update({
    where: {
      id: parseInt(req.params.id),
    },
    data: {
      name: req.body.name,
    },
  });
  res.json(updatedUser);
});

app.delete('/users/:id', async (req, res) => {
  // Delete an existing user
  const deletedUser = await prisma.user.delete({
    where: {
      id: parseInt(req.params.id),
    },
  });
  res.json(deletedUser);
});

app.listen(3000, () => {
  console.log('Server is running on port 3000');
});

Conclusion

In summary, Prisma is a powerful ORM tool that can help you work with a database in a familiar programming language. By setting up Prisma with an Express app and a PostgreSQL database, you can easily perform CRUD operations and take advantage of its advanced querying capabilities.

Tags: Prisma, ORM, Express, PostgreSQL, Node.js, GraphQL, CRUD, database schema, one-to-many relationship, GraphQL syntax, data consistency, data structure

Introduction to Prisma ORM With Express & PostgreSQL