PostgreSQL with Dockerコンテナ

# Docker env
POSTGRES_DATABASE=postgres
POSTGRES_USER=postgres
POSTGRES_PASSWORD=password
POSTGRES_ROOT_PASSWORD=root
 

volumes:
  data-volume:
    name: chat-app-data
    driver: local
services:
  chat-app-data:
    container_name: chat-app
    image: postgres:latest
    volumes:
      - data-volume:/var/lib/postgresql/data
    ports:
      - "5432:5432"
    environment:
      - POSTGRES_DATABASE=${POSTGRES_DATABASE}
      - POSTGRES_USER=${POSTGRES_USER}
      - POSTGRES_PASSWORD=${POSTGRES_PASSWORD}
      - POSTGRES_ROOT_PASSWORD=${POSTGRES_ROOT_PASSWORD}
 

docker compose up -d

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  shadowDatabaseUrl = env("SHADOW_DATABASE_URL") // Only needed when using a cloud provider that doesn't support the creation of new databases, like Heroku. Learn more: https://pris.ly/d/migrate-shadow
}
 
generator client {
  provider        = "prisma-client-js"
}
 
model Account {
  id                 String  @id @default(cuid())
  userId             String
  type               String
  provider           String
  providerAccountId  String
  refresh_token      String?  @db.Text
  access_token       String?  @db.Text
  expires_at         Int?
  token_type         String?
  scope              String?
  id_token           String?  @db.Text
  session_state      String?
 
  user User @relation(fields: [userId], references: [id], onDelete: Cascade)
 
  @@unique([provider, providerAccountId])
}
 
model Session {
  id           String   @id @default(cuid())
  sessionToken String   @unique
  userId       String
  expires      DateTime
  user         User     @relation(fields: [userId], references: [id], onDelete: Cascade)
}
 
model User {
  id            String    @id @default(cuid())
  name          String?
  email         String?   @unique
  emailVerified DateTime?
  image         String?
  accounts      Account[]
  sessions      Session[]
 
  Members       Member[]
  Messages      Message[]
 
  createdAt     DateTime  @default(now())
  updatedAt     DateTime  @updatedAt
 
}
 
model VerificationToken {
  identifier String
  token      String   @unique
  expires    DateTime
 
  @@unique([identifier, token])
}
 
model Server {
  id         String   @id @default(uuid())
  name        String
  description String?
  image       String? @db.Text
 
  members Member[]
  channels Channel[]
 
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
 
}
 
model Channel {
  id         String   @id @default(uuid())
  serverId   String
  name       String
  description String?
  type       String
  createdAt  DateTime @default(now())
  updatedAt  DateTime @updatedAt
 
  server Server @relation(fields: [serverId], references: [id], onDelete: Cascade)
  messages Message[]
}
 
model Message {
  id         String   @id @default(uuid())
  channelId  String
  userId     String
  content    String?  @db.VarChar(4000)
  type       String
  url        String?  @db.Text
  spoiler    Boolean
 
  createdAt  DateTime @default(now())
  updatedAt  DateTime @updatedAt
 
  channel Channel @relation(fields: [channelId], references: [id], onDelete: Cascade)
  user    User    @relation(fields: [userId], references: [id], onDelete: Cascade)
 
}
 
model Member {
  id         String   @id @default(uuid())
  userId     String
  serverId   String
  role       String
  joinedAt   DateTime
  lastActive DateTime
 
  user   User   @relation(fields: [userId], references: [id], onDelete: Cascade)
  server Server @relation(fields: [serverId], references: [id], onDelete: Cascade)
 
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
 
  @@unique([userId, serverId])
}

pnpx prisma generate
pnpx prisma db push

pnpx prisma studio

docker exec -it chat-app /bin/bash
psql -d postgres -U postgres
 
#もしくは直接
docker exec -it chat-app psql -U postgres postgres

postgres=# \d
               List of relations
 Schema |       Name        | Type  |  Owner
--------+-------------------+-------+----------
 public | Account           | table | postgres
 public | Channel           | table | postgres
 public | Member            | table | postgres
 public | Message           | table | postgres
 public | Server            | table | postgres
 public | Session           | table | postgres
 public | User              | table | postgres
 public | VerificationToken | table | postgres
(8 rows)
 
#シングルクォートだとNG
postgres=# \d "Message"
                                Table "public.Message"
  Column   |              Type              | Collation | Nullable |      Default
-----------+--------------------------------+-----------+----------+-------------------
 id        | text                           |           | not null |
 channelId | text                           |           | not null |
 userId    | text                           |           | not null |
 content   | character varying(4000)        |           |          |
 type      | text                           |           | not null |
 url       | text                           |           |          |
 spoiler   | boolean                        |           | not null |
 createdAt | timestamp(3) without time zone |           | not null | CURRENT_TIMESTAMP
 updatedAt | timestamp(3) without time zone |           | not null |
Indexes:
    "Message_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "Message_channelId_fkey" FOREIGN KEY ("channelId") REFERENCES "Channel"(id) ON UPDATE CASCADE ON DELETE CASCADE
    "Message_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"(id) ON UPDATE CASCADE ON DELETE CASCADE
 
#ダブルクォートで括らないと小文字として認識される
postgres=# \d Account
Did not find any relation named "Account".
 
#selectでも同様
postgres=# select * from User;
   user
----------
 postgres
(1 row)
 
# Userを取る場合はダブルクォートで括る
postgres=# select * from "User";
 
# コマンド履歴
postgres=# \s