version: "3.8"

# Service definition
services:
  mysql:
    # Dockerfile
    build: ./docker/mysql
    # Image Names
    image: mysql:custom
    # Container Name
    container_name: mysql
    # Environment
    env_file:
      - .env
    # Mount host directory(host:container)
    volumes:
      - ./docker/mysql/data:/var/lib/mysql
    # Command
    # Port forwarding(host:container)
    ports:
      - 3306:3306
    # Network
    networks:
      - prisma-test
    # Continue container startup
    stdin_open: true
    tty: true
    # Restart
    restart: always

  phpmyadmin:
    # Image Name
    image: phpmyadmin:5.2
    # Container Name
    container_name: phpmyadmin
    # Container dependencies
    depends_on:
      - mysql
    # Environment
    env_file:
      - .env
    # Port forwarding(host:container)
    ports:
      - 8080:80
    # Network
    networks:
      - prisma-test
    # Continue container startup
    stdin_open: true
    tty: true
    # Restart
    restart: always

  prisma:
    # Dockerfile
    build: ./docker/prisma
    # Image Names
    image: prisma:custom
    # Container Name
    container_name: prisma
    # Environment
    env_file:
      - .env
    # Mount host directory(host:container)
    volumes:
      - ./app:/work/app
    # Current Directory
    working_dir: /work
    # Port forwarding(host:container)
    ports:
      - 8000:8000
      - 5555:5555
    # Network
    networks:
      - prisma-test
    # Continue container startup
    stdin_open: true
    tty: true
    # Container dependencies
    depends_on:
      - phpmyadmin
    # Restart
    restart: always

# Network definition
networks:
  prisma-test:
    external: true

# Common
TZ="Asia/Tokyo"

# MySQL
MYSQL_ROOT_PASSWORD="SysAdm!n"
MYSQL_DATABASE="db"
MYSQL_USER="mysql"
MYSQL_PASSWORD="SysAdm!n"

# phpMyAdmin
PMA_ARBITRARY="1"
PMA_HOSTS="mysql"
PMA_USER="root"
PMA_PASSWORD="SysAdm!n"

# Prisma
PRISMA_DB_URL="mysql://root:SysAdm!n@mysql:3306/db"

# Base Image
FROM --platform=linux/amd64 mysql:8.2

# Copy my.conf file
ADD ./my.cnf /etc/mysql/my.cnf
RUN chmod 644 /etc/mysql/my.cnf

[mysqld]
character_set_server = utf8mb4
collation-server = utf8mb4_0900_bin

# mysqlオプションの設定
[mysql]
# 文字コードの設定
default-character-set = utf8mb4

# mysqlクライアントツールの設定
[client]
# 文字コードの設定
default-character-set = utf8mb4

# Base Image
FROM --platform=linux/amd64 node:20.9-alpine

# Working Directory
WORKDIR /work/app

# Install
RUN npm i -D prisma @prisma/client typescript ts-node @types/node && \
  corepack enable && \
  corepack prepare pnpm@latest --activate

# Port
EXPOSE 8000 5555

# Dockerネットワークを作成
$ docker network create prisma-test
$ docker network ls

# コンテナをビルド
$ docker-compose build

# コンテナを起動
$ docker-compose up -d

# コンテナ一覧を表示
$ docker-compose ps

# コンテナを停止
$ docker-compose down

# MySQLコンテナに接続
$ docker-compose exec mysql /bin/bash

# PostgreSQL接続
# root/SysAdm!n
$ mysql -u root -p

# タイムゾーン確認
$ show variables like '%time_zone%';

# 文字コード確認
$ show variables like '%char%';

# 照合順序確認
$ show variables like 'col%';

# DB一覧を表示
$ show databases;

# MySQL切断
$ exit

# コンテナから切断
$ exit

# 開発作業コンテナに接続
$ docker-compose exec prisma /bin/sh

# 各種バージョン確認
$ node --version
$ npm --version
$ pnpm --version

# コンテナから切断
$ exit

$ npx prisma init --datasource-provider mysql

✔ Your Prisma schema was created at prisma/schema.prisma
  You can now open it in your favorite editor.

warn You already have a .gitignore file. Don't forget to add `.env` in it to not commit any private information.

Next steps:
1. Set the DATABASE_URL in the .env file to point to your existing database. If your database has no tables yet, read https://pris.ly/d/getting-started
2. Run npx prisma db pull to turn your database schema into a Prisma schema.
3. Run npx prisma generate to generate the Prisma Client. You can then start querying your database.

More information in our documentation:
https://pris.ly/d/getting-started

# Environment variables declared in this file are automatically made available to Prisma.
# See the documentation for more detail: https://pris.ly/d/prisma-schema#accessing-environment-variables-from-the-schema

# Prisma supports the native connection string format for PostgreSQL, MySQL, SQLite, SQL Server, MongoDB and CockroachDB.
# See the documentation for all the connection string options: https://pris.ly/d/connection-strings

# DATABASE_URL="mysql://johndoe:randompassword@localhost:3306/mydb"
DATABASE_URL="mysql://root:SysAdm!n@mysql:3306/db"

  "[prisma]": {
    "editor.defaultFormatter": "Prisma.prisma",
    "editor.insertSpaces": true,
    "editor.formatOnSave": true
  },

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

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

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

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

model Todo {
  id        Int     @id @default(autoincrement())
  title     String
  body      String?
  completed Boolean @default(false)
  userId    Int
  user      User    @relation(fields: [userId], references: [id])
}

$ prisma db pull

$ npx prisma migrate dev --name="init"

Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": MySQL database "db" at "mysql:3306"

Applying migration `20231121001202_init`

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

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

Your database is now in sync with your schema.

Running generate... (Use --skip-generate to skip the generators)
Warning: [Prisma auto-install on generate] Prisma could not find a package.json file in the inferred project root /workspaces/prisma-test/app. During the next step, when an auto-install of Prisma package(s) will be attempted, it will then be created by your package manager on the appropriate level if necessary.

added 2 packages in 2s

added 2 packages, and audited 5 packages in 3s

found 0 vulnerabilities

✔ Generated Prisma Client (v5.6.0) to ./node_modules/@prisma/client in 959ms

-- CreateTable
CREATE TABLE `User` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `email` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NULL,

    UNIQUE INDEX `User_email_key`(`email`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `Todo` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(191) NOT NULL,
    `body` VARCHAR(191) NULL,
    `completed` BOOLEAN NOT NULL DEFAULT false,
    `userId` INTEGER NOT NULL,

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `Todo` ADD CONSTRAINT `Todo_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `User`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

$ npx prisma migrate dev --name="init" --create-only

Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": MySQL database "db" at "mysql:3306"

Prisma Migrate created the following migration without applying it 20231121005329_init

You can now edit it and apply it by running prisma migrate dev.

$ npx prisma migrate dev

Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": MySQL database "db" at "mysql:3306"

Applying migration `20231121005329_init`

The following migration(s) have been applied:

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

Your database is now in sync with your schema.

✔ Generated Prisma Client (v5.6.0) to ./node_modules/@prisma/client in 389ms

$ npx prisma generate

Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma

✔ Generated Prisma Client (v5.6.0) to ./node_modules/@prisma/client in 429ms

Start using Prisma Client in Node.js (See: https://pris.ly/d/client)

import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()

or start using Prisma Client at the edge (See: https://pris.ly/d/accelerate)

import { PrismaClient } from '@prisma/client/edge'
const prisma = new PrismaClient()

See other ways of importing Prisma Client: http://pris.ly/d/importing-client

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

const seed = async () => {
  const userData = [
    { email: 'alice@example.com', name: 'Alice' },
    { email: 'bob@example.com', name: 'Bob' },
  ];

  for (const user of userData) {
    await prisma.user.upsert({
      where: { email: user.email },
      update: {},
      create: user,
    });
  }
};

seed()
  .catch((error) => {
    console.error('Error: ', error);
    return;
  })
  .finally(async () => await prisma.$disconnect());

$ npx ts-node src/seed.ts

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

const create = async () => {
  const response = await prisma.user.create({
    data: {
      email: 'john@example.com',
      name: 'John',
      todos: {
        create: [
          { title: 'todo1', body: 'body1' },
          { title: 'todo2', body: 'body2' },
          { title: 'todo3', body: 'body3' },
        ],
      },
    },
  });

  console.log(response);
};

create()
  .catch((error) => {
    console.error('Error: ', error);
    return;
  })
  .finally(async () => await prisma.$disconnect());

$ npx ts-node src/create.ts
{ id: 4, email: 'john@example.com', name: 'John' }

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

const get = async () => {
  const response = await prisma.user.findUnique({
    where: {
      email: 'alice@example.com',
    },
  });

  console.log(response);
};

get()
  .catch((error) => {
    console.error('Error: ', error);
    return;
  })
  .finally(async () => await prisma.$disconnect());

$ npx ts-node src/get.ts
{ id: 1, email: 'alice@example.com', name: 'Alice' }

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

const update = async () => {
  const responseGet = await prisma.user.findUnique({
    where: {
      email: 'alice@example.com',
    },
  });

  console.log(responseGet);

  const responseUpdate = await prisma.user.update({
    where: { id: responseGet?.id },
    data: { email: 'update@example.com' },
  });

  console.log(responseUpdate);
};

update()
  .catch((error) => {
    console.error('Error: ', error);
    return;
  })
  .finally(async () => await prisma.$disconnect());

$ npx ts-node src/update.ts
{ id: 1, email: 'alice@example.com', name: 'Alice' }
{ id: 1, email: 'update@example.com', name: 'Alice' }

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

const gets = async () => {
  const response = await prisma.user.findMany({
    include: {
      todos: true,
    },
  });

  console.log(response);
};

gets()
  .catch((error) => {
    console.error('Error: ', error);
    return;
  })
  .finally(async () => await prisma.$disconnect());

$ npx ts-node src/gets.ts
[
  { id: 1, email: 'update@example.com', name: 'Alice', todos: [] },
  {
    id: 4,
    email: 'john@example.com',
    name: 'John',
    todos: [ [Object], [Object], [Object] ]
  }
]

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

const remove = async () => {
  const responseGet = await prisma.user.findUnique({
    where: {
      email: 'bob@example.com',
    },
  });

  console.log(responseGet);

  const responseDelete = await prisma.user.delete({
    where: { id: responseGet?.id },
  });

  console.log(responseDelete);
};

remove()
  .catch((error) => {
    console.error('Error: ', error);
    return;
  })
  .finally(async () => await prisma.$disconnect());

$ npx ts-node src/remove.ts
{ id: 2, email: 'bob@example.com', name: 'Bob' }
{ id: 2, email: 'bob@example.com', name: 'Bob' }

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

const transaction = async () => {
  try {
    await prisma.$transaction(async (prisma) => {
      const responseCreate = await prisma.todo.create({
        data: {
          title: 'title4',
          body: 'body4',
          userId: 1,
        },
      });

      console.log(responseCreate);

      const responseUpdate = await prisma.user.update({
        where: {
          id: 1,
        },
        data: {
          email: 'update2@example.com',
        },
      });

      console.log(responseUpdate);
    });
  } catch (e) {
    console.error(e);
  }
};

transaction()
  .catch((error) => {
    console.error('Error: ', error);
    return;
  })
  .finally(async () => await prisma.$disconnect());

$ npx ts-node src/transaction.ts
{ id: 7, title: 'title4', body: 'body4', completed: false, userId: 1 }
{ id: 1, email: 'update2@example.com', name: 'Alice' }