Welcome to LyonJS #68

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"

Prisma, what is it?

Interact with your database

👉 Native driver

pg

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

👉 Query builder

knex

Star

14.4k

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

Sequelize

Star

24.9k

92 / 100

TypeORM

Star

25.5k

89 / 100

MikroORM

Star

3.5k

83 / 100

Prisma

Star

15.9k

98 / 100

Bookshelf.js

Star

6.2k

74 / 100

Objection.js

Star

6.3k

86 / 100

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

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;

}

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

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

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?

• 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

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
   ...

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

Prisma studio

prisma studio

A minimalist admin UI to navigate throughout your data.

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?