Working with PostgreSQL using TypeScript ORM: Prisma

• Step 1: Create a project directory and navigate into it.

  mkdir prisma-postgres
  cd prisma-postgres

• Step 2: Initialize a TypeScript project

npm init -y
npm install typescript ts-node @types/node --save-dev

• Step 3: Install the Prisma CLI and configure PostgreSQL as your database

npx tsc --init
npm install prisma --save-dev
npx prisma init --datasource-provider postgresql

Log into Supabase and create a new project

Get the connection string from the project settings

Choose Node.js

Swap the DATABASE_URL in .env to your own connection string

DATABASE_URL="YOUR_CONNECTION_STRING"

Prisma schema provides an intuitive way to model data. Add your own Prisma data model to your Prisma schema in prisma/schema.prisma:

Add your own data model

The data model I provided below represents a system for managing news articles (News) about athletes (Athlete) and their profiles (Profile). An athlete can have multiple news articles associated with them, and each athlete has a profile containing various details such as bio, age, team, height, weight, college, and draft information.

The final schema.prisma file would look something like this

To map your data model to the database schema, you need to use the prisma migrate CLI commands:

npx prisma migrate dev --name init

If you have successfully connected, you will receive a message stating, "Your database is now synchronized with your schema."

In this case, you should see three data tables (Athlete, News, Profile) generated on Supabase.

Checkout more detail on Prisma schema documentation

Install the @prisma/client package using npm

npm install @prisma/client

Write your first query

Add the content of the main function to read all Athlete records from the database and print the result

Let's walk through the code:

• Initializes a Prisma client
• Executes a query to retrieve all users from the "athlete" table
• Logs the results to the console
• Disconnects from the Prisma client
• If an error occurs, it logs the error, disconnects from the client, and exits the process

Execute query.ts

npx ts-node query.ts // Output: [], Since there's nothing in the database

Write data into database

Here, we swap out the previous query and try to write data into database.

Let's walk through the code:

• Creates a new athlete with associated news and profile data
• Retrieves all athletes with their news and profile information
• Logs the results to the console.

Execute the code and the output should look similar to this:

Hooray! You can also check the data on supabase 🎉

Medium

• Prisma Client Documentation
• Database Setup Documentation
• Setup PostgreSQL Database on Supabase