Prisma

What?

ORM (Object–Relational Mapping)
Client - Server(Prisma) - Database
- Database communication: communicate and interact with any type of database using same syntax
  - PostgreSQL - rdbms
  - MySQL - rdbms
  - Microsoft SQL Server - MSSQL - rdbms
  - CockroachDB - rdbms
  - SQLite - ORM
  - MongoDB - nosql, MERN
  - CockroachDB
- Server safe: a type-safe way of interacting with databases by generating SQL queries at runtime based on prisma/schema.prisma

What Is the Process?

Prisma Schema prisma/schema.prisma
- Data model 🔗 (opens in a new tab)
- clear schema language ⇒ express data relationship in human-readable way
- Or schema can also be automatically inferred from any existing DB
Schema ⇒ type definition
1. allowing perform typesafe CRUD operations with own DB model
Migrations as ts structure evolves
Visualize data
1. Prisma studio ⇒ npx prisma studio ⇒ view and manage all tables and rows in browser

Why?

Why Prima?

Keeps database schema in sync
and maintaining existing data
code is significantly more concise than write in raw SQL (Structured Query Language)

Why Shouldn't?

competitor
- DrizzleORM

How?

How to Setup the Project?

Quickstart with TypeScript & SQLite (prisma.io) (opens in a new tab)
- Start from scratch with relational databases (15 min) | typescript-postgresql (prisma.io) (opens in a new tab) MySQL - rdbms / PostgreSQL - rdbms / Microsoft SQL Server - MSSQL - rdbms…
```
npm init -y ## create package.json
npm install prisma typescript ts-node @types/node --save-dev
npm i --save-dev nodemon
```
  - nodemon ⇒ refresh server every time we make changes
  1. npx tsc --init create tsconfig.json (npx tsc --init) 🔗 (opens in a new tab)
  2. npx prisma invoke Prisma CLI
  3. npx prisma init creating prisma/schema.prisma (setup Prisma project & setup database)
    1. npx prisma init --datasource-provider SQLite
    2. npx prisma init --datasource-provider postgresql init basic file and use Postgres by default
  4. setup database URL and the .env.local - security
    - .env
      - find DATABASE_URL=”postgresql://...” from
        
        Railway.app
  5. npm i @prisma/client install Prisma Client
    1. Instantiate client ⇒ client.js
  6. modify prisma/schema.prisma
  - migrate: npx prisma migrate dev --name init ⇒ generating
    - check table created in
      - Railway.app
- Start from scratch with MongoDB (15 min) | typescript-mongodb (prisma.io) (opens in a new tab) MongoDB - nosql, MERN

How to use Prisma?

Define/create schema prisma/schema.prisma
/update database structure can be handled automatically
- npx prisma migrate
  - or npx prisma migrate dev --name init ## give the name of init
  - npm prisma migrate dev --name test2
- migration file will interact with database and make all changes
  - /prisma/migrations/init/migration.sql
Install client
1. npx i @prisma/client
interact with the DB on the server
- npx prisma generate ## go through all the steps generate based on whatever provider we want
import Prisma in Clien

t
setup package.json scripts
- “devStart”: “nodemon script.ts” ## npm run devStart

READING DATA

// npm run devStart ## to run the script.ts
// run `npx prisma generate`
import { PrismaClient } from "@prisma/client";
const prisma = new PrismaClient(); // manage different db for you !! only use one instance of it
// const prisma = new PrismaClient({log:["query"]}); // log the query in the terminal
 
// everything in prisma is async
async function main() {
  // ... you will write your Prisma Client queries here
 
  // delete all users in the database
  await prisma.user.deleteMany(); // How to delete everything form the database
  /*
    const user = await prisma.user.createMany({
        where: {
            AND: [
                { email: { startsWith: "alice"} },
                { email: { endsWith: "@test.com"} },
            ]
            OR: [],
            NOR: [],
            // queries
            name: { equals: "Alice" },
            name: { not: "Alice" },
            name: { in: ["Alice", "Kyle"] },
            name: { notIn: ["Alice", "Kyle"] },
            age: { lt: 20 }, // age less than 20    
            age: { lte: 20 }, // age less than or equal 20    
            age: { gt: 20 }, // age greater than 20    
            age: { gte: 20 }, // age greater than  or equal 20    
            email: { contains: "@test.com"}, 
            email: { endsWith: "@test.com"}, 
            email: { startsWith: "alice"}, 
 
        },
        distinct: ["name", "age"], // return one single Alice
        orderBy: {
            age: "asc", // "desc"
        }
        take: 2, // pagination
        skip: 1, // skip the first user Alice, get the next two
 
    })
        - cannot use select function
    
    const user = await prisma.user.findUnique({ 
        where: {
            email: "alice@test.com",
            age_name: { // this is generated by @@unique([age, name])
                age: 24,
                name: "Alice"
            }
        }
    })
        - find by unique keys
        - only return one
        - can use select and include function 
 
		// Find the first matching 
    const user = await prisma.user.findFirst({ 
        where: {
            name: "Alice"
            }
    })
        - only return one
 
    const user = await prisma.user.findMany()   // get all users
    const user = await prisma.user.findMany({
        where: {
            writtenPosts: {
                every: {
                    title: { startsWith: "Test" },
                    createdAt: new Date(),
                },
                none: {},
                some: {},
 
            }
        }
    })
    const user = await prisma.post.findMany({
        where:{
            author:{
                is: {
                    age: 24,
                }
                isNot: {}
            }
        }
    })
 
    */
 
  const user = await prisma.user.create({
    data: {
      name: "Alice",
      email: "alice@test.com",
      age: 24,
      userPreference: {
        // create function allows create a brand new user preference
        create: {
          emailUpdates: true,
        },
      },
    },
 
    /* CAN ONLY INCLUDE OR SELECT CANNOT DO BOTH */
 
    // include the userPreference model
    include: {
      userPreference: true,
    },
    // only return the name property
    select: {
      name: true,
      userPreference: { select: { id: true } }, // only the id of the userPreference
    },
  });
 
  console.log(user);
}
 
main()
  .catch((e) => {
    console.error(e.message); // catching any errors and printing them out
  })
  .finally(async () => {
    await prisma.$disconnect(); // finally disconnected
  });

UPDATE DATA

async function main() {
  // update first user
  // - can use include and select function
  const user = await prisma.user.update({
    where: {
      email: "alice@test.com", // take this
    },
    data: {
      email: "alice@test2.com", // change to this
    },
  });
 
  // updateMany: some as update but update all users
  const users = await prisma.user.updateMany({
    where: {
      name: "Alice", // take this
    },
    data: {
      name: "New Alice", // change all matching to this
    },
  });
  console.log(user);
}
 
const user = await prisma.user.update({
  where: {
    email: "alice@test.com", // take this
  },
  data: {
    email: "alice@test2.com", // change to this
    age: {
      increment: 1, // increment the int
      decrement: 1,
      multiply: 1,
      divide: 1,
    },
    // update user and give a new user preference with this information
    userPreference: {
      create: {
        emailUpdates: true,
      },
      connect: {
        id: "askdjflkasjdlfkjalskdjf",
      },
    },
  },
});

DELETE DATA

async function main() {
  // simiar to find
  // must be unique field
  const user = await prisma.user.delete({
    where: {
      email: "alice@test.com",
    },
  });
  console.log(user);
 
  // deleteMany
  const users = await prisma.user.deleteMany({
    where: {
      age: { gt: 20 },
    },
  });
 
  // delete all user in the database
  const users = await prisma.user.deleteMany();
}

How to format Prisma file?

npx prisma format
Plugin (can set in setting.json [prisma])

Docker React.js