Welcome to LyonJS #68

Talk #1: Prisma as my ORM for PostgreSQL

Welcome to LyonJS #68

Talk #1: Prisma as my ORM for PostgreSQL

Project context

👉 Kick off a new project 🇺🇸
👉 Only one developer at first.
👉 Previous experience with MongoDB and Mongoose.
👉 3 years without really seeing any SQL. The idea of "not leaving my comfort zone"

"Should you use Prisma?" 🤔

https://www.prisma.io/docs/concepts/overview/should-you-use-prisma

Prisma, what is it?

Prisma belongs to the ORM family. (Object–relational mapping)

Express, Koa, hapi, fastify...
REST, GraphQL

SQLite
MySQL
PostgreSQL
SQL Server Preview
MongoDB Early Access

Interact with your database

👉 Native driver

await client.query(
  `SELECT *
   FROM users
   WHERE id = $1`,
  ['123']
)
await client.query(
  `SELECT *
   FROM users
   WHERE id = $1`,
  ['123']
)

👉 Query builder

knex

Star

interface User {
  id: number;
  name: string;
  age: number;
}

knex<User>('users')
  .where('id', '123')
  .first();
interface User {
  id: number;
  name: string;
  age: number;
}

knex<User>('users')
  .where('id', '123')
  .first();

👉 ORM

Star

Star

Star

Star

Star

Star

Prisma schema

// schema.prisma

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

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

model User {
  user_id        String         @id
  email          String         @unique
  full_name      String
  bank_accounts  BankAccount[]
  created_at     DateTime       @default(now())
  updated_at     DateTime       @updatedAt

  @@map(name: "users") // Table name in your DB
}
// schema.prisma

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

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

model User {
  user_id        String         @id
  email          String         @unique
  full_name      String
  bank_accounts  BankAccount[]
  created_at     DateTime       @default(now())
  updated_at     DateTime       @updatedAt

  @@map(name: "users") // Table name in your DB
}

Both ways

I've got my Prisma schema I want my structure in my database

prisma db push ✅

I've got my structure in my database I want my Prisma schema

prisma db pull ✅

Comparison with TypeORM

Annotations on top of class attributes

// User.ts

@Entity()
export class User {

  @PrimaryGeneratedColumn("uuid")
  user_id: number;

  @Column()
  firstName: string;

  @Column()
  lastName: string;

  @Column()
  age: number;

}
// User.ts

@Entity()
export class User {

  @PrimaryGeneratedColumn("uuid")
  user_id: number;

  @Column()
  firstName: string;

  @Column()
  lastName: string;

  @Column()
  age: number;

}

"Object–relational impedance mismatch"? 🤯

https://www.prisma.io/docs/ ... /is-prisma-an-orm

Generate Prisma client

server
 > dist
 > node_modules
 > prisma
     schema.prisma
 > src
 > test
   .env
   .env.local
   .gitignore
   .npmrc
   .nvmrc
   .prettierrc.yml
   package.json
   package-lock.json
   README.md
   tsconfig.json
server
 > dist
 > node_modules
 > prisma
     schema.prisma
 > src
 > test
   .env
   .env.local
   .gitignore
   .npmrc
   .nvmrc
   .prettierrc.yml
   package.json
   package-lock.json
   README.md
   tsconfig.json

Give me the Prisma JavaScript client for this schema:

prisma generate

server
 > dist
 > node_modules
   > .bin
   > .prisma
     > client
         index.d.ts
         index.js
         ...
   > ...
 > prisma
     schema.prisma
 > src
 > test
   ...
server
 > dist
 > node_modules
   > .bin
   > .prisma
     > client
         index.d.ts
         index.js
         ...
   > ...
 > prisma
     schema.prisma
 > src
 > test
   ...

Two generated files

index.js

👉 findFirst()
👉 findMany()
👉 updateOne()
👉 etc…

'helper' functions:

To write your queries,
Will always return simple JavaScript objects (POJOs).

We can still write SQL if necessary:

prismaClient.$queryRaw('<SQL>')

index.d.ts

All TypeScript types corresponding to our entities.

👉 BankAccount :

A row in our table.

👉 BankAccountCreateInput :

What we have to set when creating a bank_account.

👉 BankAccountWhereInput

What we can use in a 'where' condition.

Use Prisma client in your app

import express from 'express'
// 1- Import it
import { PrismaClient } from '@prisma/client' // = import from '.prisma/client

// 2- Instantiate it
const prisma = new PrismaClient()

const app = express()

app.get('/bank-accounts', async (req, res) => {
  // 3- Use it
  const bankAccounts = await prisma.bankAccount.findMany({
    where: { active: true },
    include: { user: true },
    orderBy: { created_at: 'desc' },
  })
  res.json(bankAccounts)
})
import express from 'express'
// 1- Import it
import { PrismaClient } from '@prisma/client' // = import from '.prisma/client

// 2- Instantiate it
const prisma = new PrismaClient()

const app = express()

app.get('/bank-accounts', async (req, res) => {
  // 3- Use it
  const bankAccounts = await prisma.bankAccount.findMany({
    where: { active: true },
    include: { user: true },
    orderBy: { created_at: 'desc' },
  })
  res.json(bankAccounts)
})

Some tests?

By completely mocking Prisma client.

👉 https://www.prisma.io/docs/guides/testing/unit-testing

By using a test database.

👉 https://www.prisma.io/docs/guides/testing/integration-testing

Set your test database URL:

DATABASE_URL=postgresql://test-user:plop@localhost:5432/freely-test

Clean all tables between each test.

TRUNCATE TABLE \"${dbSchemaName}\".\"${tablename}\" CASCADE;

More info here.

Prisma migrate

I'm working on a new feature, I make changes to my `schema.prisma` file:

prisma format > formats + runs some checks
prisma db push > updates local database structure + updates Prisma client

I'm ready to ship my new feature:

prisma migrate dev --name "Adding bank_accounts table"

server
 > dist
 > node_modules
 > prisma
   > migrations
     > 20210715144607_adding_bank_accounts_table
         migration.sql
   schema.prisma
 > src
   ...
server
 > dist
 > node_modules
 > prisma
   > migrations
     > 20210715144607_adding_bank_accounts_table
         migration.sql
   schema.prisma
 > src
   ...

I replicate database structure changes to other environments:

scripts: {
  "db-preprod-up": "dotenv -e .env.preprod -- npx prisma migrate deploy",
}
scripts: {
  "db-preprod-up": "dotenv -e .env.preprod -- npx prisma migrate deploy",
}

👉 Runs the SQL migration script + inserts a new row to `_prisma_migrations` table.

Prisma studio

prisma studio

A minimalist admin UI to navigate throughout your data.

→ Other option: TablePlus

What I like about Prisma

➕➕ TypeScript-first.

➕➕ Dynamic project.
A real team behind it, regular releases, huge community on Slack (~46,000 people), Prisma Day conference.
(Disclaimer: Like any other tool, we don’t know if it will still be that dynamic in a few years!)

➕➕ A wide and well-written documentation.

Miscellaneous

👉 May 2017: The tool was initially known as Graphcool.
👉 May 2018: $4.5M seed round + rebranding to Prisma.
👉 Early 2019: Prisma 2 release with wider support than just GraphQL.
👉 The "Prisma Query Engine" is implemented in Rust.
👉 High level Prisma roadmap is shared here: notion.so/Prisma-Roadmap...

Some more resources

Getting started with Prisma:
👉 https://www.prisma.io/docs/getting-started
Comparing SQL, query builders, and ORMs:
👉 https://www.prisma.io/dataguide/types/relational/comparing-sql-query-builders-and-orms
9 Best JS and TS ORMs: (mars 2021)
👉 https://www.sitepoint.com/javascript-typescript-orms/

Can I get a ROTI?

(Return On Time Invested)