TiDB, Prisma 試す

# tiup をダウンロードしてインストール
curl --proto '=https' --tlsv1.2 -sSf https://tiup-mirrors.pingcap.com/install.sh | sh

source /Users/thesugar/.zshrc

tiup playground

git clone https://github.com/tidb-samples/tidb-nodejs-prisma-quickstart.git
cd tidb-nodejs-prisma-quickstart

yarn

cp .env.example .env 

DATABASE_URL=mysql://root:@127.0.0.1:4000/test

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

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

npx prisma migrate dev

~/tidb-nodejs-prisma-quickstart thesugar $ npx prisma migrate dev                                                                                                                                                                       [main]
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": MySQL database "test" at "127.0.0.1:4000"

Applying migration `20230828153207_init`

The following migration(s) have been applied:

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

Your database is now in sync with your schema.

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

// Define a Player model, which represents the `players` table in the database.
model Player {
  id        Int      @id @default(autoincrement())
  name      String   @unique(map: "uk_player_on_name") @db.VarChar(50)
  coins     Decimal  @default(0)
  goods     Int      @default(0)
  createdAt DateTime @default(now()) @map("created_at")
  profile   Profile?

  @@map("players")
}

// Define a Profile model, which represents the `profiles` table in the database.
model Profile {
  playerId  Int    @id @map("player_id")
  biography String @db.Text

  // Define a 1:1 relation between the `Player` and `Profile` models with Foreign Key constraints.
  player Player @relation(fields: [playerId], references: [id], onDelete: Cascade, map: "fk_profile_on_player_id")

  @@map("profiles")
}

// Step 1. Import the `@prisma/client` package, which is generated by `npx prisma generate` command.
import {Player, PrismaClient} from '@prisma/client';

/**
 * 🚪Main function.
 */
async function main(): Promise<void> {
  // Step 2. Create a new PrismaClient instance.
  const prisma = new PrismaClient();
  try {
    // Step 3. Use Prisma Client to perform some CRUD operations.

    const version = await getTiDBVersion(prisma);
    console.log(`🔌 Connected to TiDB cluster! (TiDB version: ${version})`);

    const newPlayer = await createPlayer(prisma, 'Alice',100, 100);
    console.log(`🆕 Created a new player with ID ${newPlayer.id}.`);

    const player = await getPlayerByID(prisma, newPlayer.id);
    if (!player) {
      throw new Error(`Cannot find player with ID ${newPlayer.id}.`);
    }
    console.log(`ℹ️ Got Player ${player.id}: Player { id: ${player.id}, coins: ${player.coins}, goods: ${player.goods} }`);

    const updatedPlayer = await updatePlayer(prisma, player.id, 50, 50);
    console.log(`🔢 Added 50 coins and 50 goods to player ${player.id}, now player ${updatedPlayer.id} has ${updatedPlayer.coins} coins and ${updatedPlayer.goods} goods.`);

    const deletedPlayer = await deletePlayerByID(prisma, player.id);
    console.log(`🚮 Player ${deletedPlayer.id} has been deleted.`);
  } finally {
    // Step 4. Disconnect Prisma Client.
    await prisma.$disconnect();
  }
}

void main();

/**
 * Get TiDB version.
 */
async function getTiDBVersion(prisma: PrismaClient): Promise<string> {
  const rows = await prisma.$queryRaw<{ version: string }[]>`SELECT version() AS version;`;
  return rows[0].version;
}

/**
 * 🆕 CREATE a new player.
 */
async function createPlayer(prisma: PrismaClient, name: string, coins: number, goods: number): Promise<Player> {
  return prisma.player.create({
    data: {
      name,
      coins,
      goods,
      createdAt: new Date(),
    }
  });
}

/**
 * ℹ️ READ player information by ID.
 */
async function getPlayerByID(prisma: PrismaClient, id: number): Promise<Player | null> {
  return prisma.player.findUnique({
    where: {
      id,
    }
  });
}

/**
 * 🔢 UPDATE player information by ID.
 */
async function updatePlayer(prisma: PrismaClient, id: number, incCoins: number, incGoods: number): Promise<Player> {
  return prisma.player.update({
    where: {
      id,
    },
    data: {
      coins: {
        increment: incCoins,
      },
      goods: {
        increment: incGoods,
      },
    }
  });
}

/**
 * 🚮 DELETE player information by ID.
 */
async function deletePlayerByID(prisma: PrismaClient, id: number): Promise<Player> {
  return prisma.player.delete({
    where: {
      id,
    }
  });
}