With the connection string handy, we can connect to the database. Again, we have dozens of options for how to connect to manipulate databases. Since PostgreSQL is a SQL-based database, it is handy to have some SQL skills. But, since explaining SQL is out of the scope of this tutorial, we propose to use an ORM-based solution, Prisma, delivering type-safe access to your database. First, please accept the following assignment and set it up on your computer:

Prisma

Prisma is a modern database toolkit that makes it easier to work with databases in your applications. It acts as a bridge between your Node.js (or TypeScript) code and your database, providing a type-safe way to interact with SQL databases. Prisma is often praised for its ease of use and for helping developers avoid common mistakes that come from writing raw SQL queries directly.

Key Features of Prisma

1. Type-Safe: Prisma generates a type-safe client based on your database schema, which means you get autocompletion and compile-time checks. Thus, when you change your database, it will notify you of all errors that change may raise in your code!

2. Intuitive: It simplifies data access with a straightforward query builder that's easy to understand and use.

3. Migrations: Prisma Migrate manages your database schema migrations in a version-controlled and collaborative way.

Prisma Schema

A Prisma schema defines your app's models and their relationships. Here’s an example of what a Prisma schema might look like:

model User {
    id        Int     @id @default(autoincrement())
    name      String
    email     String  @unique
    tasks     Task[]
}

enum Status {
    COMPLETED
    ACTIVE
}

model Task {
    id        String   @id @default(cuid())
    title     String
    state     Status   @default(ACTIVE)
    createdAt DateTime @default(now())
    updatedAt DateTime @updatedAt
    userId    String
    user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
}

This schema defines two models: User and Task. A user can have multiple posts, creating a one-to-many relationship between User and Post.

Traditional SQL vs. Prisma Approach

Here's how you might perform some basic operations with traditional SQL compared to the Prisma approach.

1. Insert a new Task

Traditional SQL:

INSERT INTO Task (title, userId) VALUES ('New Taks', '1');

Prisma:

await prisma.user.create({
  data: {
    title: 'New Task',
    userId: '1'
  }
});

but it also allow you to do even more readable queries doing the same thing as above

await db.user.update({
  where: {
    id: "1",
  },
  data: {
    Task: {
      create: {
        title: "1"
      }
    }
  }
});

2. Fetching tasks

Traditional SQL:

SELECT * FROM Tasks JOIN User ON Tasks.userId = Uses.id;

Prisma

await prisma.post.findMany({
  include: {
    author: true
  }
});

Similarly, Prisma handles deletions and updates. It has an amazing support from the community and a very thorough documentation. Overall, Prisma simplifies interactions with the database by providing an abstraction layer that helps you write fewer errors, get more done with less code, and enjoy type safety.