วิธีการใช้งาน Drizzle ORM

npm install drizzle-orm better-sqlite3

npm install -D drizzle-kit

// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./src/db/schema.ts", // ไฟล์ Schema ที่ระบุโครงสร้างของแต่ละ Table ใน Database
  out: "./drizzle",        // SQL ที่ generate ออกมาจะเก็บไว้ใน Folder ไหน
  dialect: "sqlite",       
  driver: "better-sqlite", // ระบุ Driver ที่ใช้ในการติดต่อกับ Database
  dbCredentials: {
    url: "./sqlite.db",    // Database จะถูกสร้างโดยอัตโนมัติถ้ายังไม่มีไฟล์นี้
  },
});

// src/db/schema.ts
import { sqliteTable, integer, text } from "drizzle-orm/sqlite-core";
import { relations } from "drizzle-orm";

export const users = sqliteTable("users", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  name: text("name").notNull(),
  email: text("email").notNull().unique(),
});

export const posts = sqliteTable("posts", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  title: text("title").notNull(),
  body: text("body"),
  authorId: integer("author_id")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
});

// User มีความสัมพันธ์แบบ One-To-Many กับ Post
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
}));

// src/db/index.ts
import Database from "better-sqlite3";
import { drizzle } from "drizzle-orm/better-sqlite3";
import * as schema from "./schema";

const sqlite = new Database("sqlite.db"); // จะสร้างไฟล์ sqlite.db ขึ้นมาใหม่ถ้ายังไม่มี
export const db = drizzle(sqlite, { schema });

{
  "scripts": {
    "db:generate": "drizzle-kit generate",
    "db:migrate": "drizzle-kit migrate",
    "db:studio": "drizzle-kit studio"
  }
}

npm run db:generate
npm run db:migrate
npm run db:studio   # เปิด UI จัดการ DB

// src/db/seed.ts
import { db } from "./index";
import { users, posts } from "./schema";

async function main() {
  console.log("Seeding database...");

  // ล้างข้อมูลเก่า (ถ้าต้องการเริ่มใหม่ทุกครั้ง)
  await db.delete(posts);
  await db.delete(users);

  // เพิ่ม users
  const insertedUsers = await db.insert(users).values([
    { name: "Alice", email: "alice@test.com" },
    { name: "Bob", email: "bob@test.com" },
    { name: "Charlie", email: "charlie@test.com" },
  ]).returning();

  // เพิ่ม posts
  await db.insert(posts).values([
    {
      title: "Hello World",
      body: "My first post using Drizzle + SQLite!",
      authorId: insertedUsers[0].id,
    },
    {
      title: "SQLite is fast",
      body: "Better-sqlite3 is great for local dev.",
      authorId: insertedUsers[1].id,
    },
    {
      title: "Relational Queries",
      body: "Drizzle makes joins super easy.",
      authorId: insertedUsers[2].id,
    },
  ]);

  console.log("Seeding completed!");
}

main()
  .then(() => process.exit(0))
  .catch((err) => {
    console.error(err);
    process.exit(1);
  });

{
  "scripts": {
    "db:seed": "ts-node src/db/seed.ts"
  }
}

{
  "scripts": {
    "db:seed": "tsx src/db/seed.ts"
  }

npm run db:seed

import { db } from "./db";
import { users, posts } from "./db/schema";
import { eq } from "drizzle-orm";

// Create
await db.insert(users).values({ name: "John", email: "john@test.com" });
await db.insert(posts).values({ title: "Hello", body: "SQLite + Drizzle", authorId: 4 });

// Get all User
const allUsers = await db.select().from(users);
const userWithPosts = await db.query.users.findMany({
  with: { posts: true },
});

// Update user
await db.update(users)
  .set({ name: "Jack" })
  .where(eq(users.id, 4));

// Delete post
await db.delete(posts)
  .where(eq(posts.id, 1));