Prisma ORM

Prerequisite

https://hub.docker.com/_/postgres

$ docker run -d --name postgres \
     -e POSTGRES_USER=johndoe \
     -e POSTGRES_PASSWORD=randompassword \
     -e POSTGRES_DB=mydb \
     -p 5432:5432 \
     postgres

$ docker ps
CONTAINER ID   IMAGE      COMMAND                  CREATED              STATUS              PORTS                    NAMES
9a46c2ed5b96   postgres   "docker-entrypoint.s…"   About a minute ago   Up About a minute   0.0.0.0:5432->5432/tcp   postgres

Install

https://www.prisma.io/docs/getting-started/setup-prisma/start-from-scratch/relational-databases-typescript-postgres

$ npx prisma init

$ tree -a
.
├── .env
└── prisma
    └── schema.prisma
    
$ cat .env
DATABASE_URL="postgresql://johndoe:randompassword@localhost:5432/mydb?schema=public"

$ cat prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

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

Migrate

https://www.prisma.io/docs/getting-started/setup-prisma/start-from-scratch/relational-databases/using-prisma-migrate-typescript-postgres

prisma/schema.prisma

model Post {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  title     String   @db.VarChar(255)
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
}

model Profile {
  id     Int     @id @default(autoincrement())
  bio    String?
  user   User    @relation(fields: [userId], references: [id])
  userId Int     @unique
}

model User {
  id      Int      @id @default(autoincrement())
  email   String   @unique
  name    String?
  posts   Post[]
  profile Profile?
}

$ npx prisma migrate dev --name init                                               
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "mydb", schema "public" at "localhost:5432"

Applying migration `20221120014351_init`

The following migration(s) have been created and applied from new schema changes:

migrations/
  └─ 20221120014351_init/
    └─ migration.sql

Your database is now in sync with your schema.

Running generate... (Use --skip-generate to skip the generators)

added 2 packages, and audited 3 packages in 3s

found 0 vulnerabilities

added 2 packages, and audited 5 packages in 5s

found 0 vulnerabilities

✔ Generated Prisma Client (4.6.1 | library) to ./node_modules/@prisma/client in 53ms

$ tree -a
.
├── .env
└── prisma
    ├── migrations
    │   ├── 20221120014351_init
    │   │   └── migration.sql
    │   └── migration_lock.toml
    └── schema.prisma

$ cat prisma/migrations/20221120014351_init/migration.sql
-- CreateTable
CREATE TABLE "Post" (
    "id" SERIAL NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,
    "title" VARCHAR(255) NOT NULL,
    "content" TEXT,
    "published" BOOLEAN NOT NULL DEFAULT false,
    "authorId" INTEGER NOT NULL,

    CONSTRAINT "Post_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "Profile" (
    "id" SERIAL NOT NULL,
    "bio" TEXT,
    "userId" INTEGER NOT NULL,

    CONSTRAINT "Profile_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "User" (
    "id" SERIAL NOT NULL,
    "email" TEXT NOT NULL,
    "name" TEXT,

    CONSTRAINT "User_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "Profile_userId_key" ON "Profile"("userId");

-- CreateIndex
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");

-- AddForeignKey
ALTER TABLE "Post" ADD CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "Profile" ADD CONSTRAINT "Profile_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

$ docker exec -it postgres bash

root@f1b317acf8c6:/# psql -U johndoe -d mydb
psql (15.1 (Debian 15.1-1.pgdg110+1))
Type "help" for help.

mydb=# \d
                List of relations
 Schema |        Name        |   Type   |  Owner  
--------+--------------------+----------+---------
 public | Post               | table    | johndoe
 public | Post_id_seq        | sequence | johndoe
 public | Profile            | table    | johndoe
 public | Profile_id_seq     | sequence | johndoe
 public | User               | table    | johndoe
 public | User_id_seq        | sequence | johndoe
 public | _prisma_migrations | table    | johndoe
(7 rows)