Drizzle ORM 使用

orm.drizzle.team

为什么要用 Drizzle

sql-like:只要你会 SQL，你就可以看懂它的语法。其他 ORM 和数据框架往往会使您偏离/抽象出 SQL，这会导致双重学习曲线：需要同时了解 SQL 和框架的 API。

index.tsts

await db
    .select()
    .from(countries)
    .leftJoin(cities, eq(cities.countryId, countries.id))
    .where(eq(countries.id, 10))

schema.tsts

// manage your schema
export const countries = pgTable('countries', {
    id: serial('id').primaryKey(),
    name: varchar('name', { length: 256 }),
});

export const cities = pgTable('cities', {
    id: serial('id').primaryKey(),
    name: varchar('name', { length: 256 }),
    countryId: integer('country_id').references(() => countries.id),
});

migration.sqlsql

-- generate migrations
CREATE TABLE IF NOT EXISTS "countries" (
    "id" serial PRIMARY KEY NOT NULL,
    "name" varchar(256)
);

CREATE TABLE IF NOT EXISTS "cities" (
    "id" serial PRIMARY KEY NOT NULL,
    "name" varchar(256),
    "country_id" integer
);

ALTER TABLE "cities" ADD CONSTRAINT "cities_country_id_countries_id_fk" FOREIGN KEY ("country_id") REFERENCES "countries"("id") ON DELETE no action ON UPDATE no action;

可以在 serverless 环境中使用

怎么使用？

这里我使用 Nirto 和 SQLite 演示

Nitro 中使用文件路由，我们可以根据文件夹结构来生成指定路由

代码地址

github.com

创建 schema

env

记录 SQLite 文件名称，配置文件中使用

.envdotenv

DATABASE_URL=file:local.db

drizzle.config

drizzle 配置文件，说明 schema 和迁移文件所在位置

drizzle.config.tsts

import { defineConfig } from "drizzle-kit";

export default defineConfig({
    dialect: "sqlite",
    schema: "./db/schema/index.ts",
    out: "./db/migrations",
    dbCredentials: {
        url: process.env.DATABASE_URL,
    },
    verbose: true,
    strict: true,
    breakpoints: true,
});

schema

数据库模型

user.schema.tsts

import { InferSelectModel } from 'drizzle-orm'
import { int, sqliteTable, text } from "drizzle-orm/sqlite-core";

export const user = sqliteTable("user", {
    id: int("id").primaryKey({ autoIncrement: true }),
    username: text("username").notNull(),
    password: text("password").notNull(),
    email: text('email').unique().notNull(),
})

export type User = InferSelectModel<typeof user>

db

导出 db 函数，供外部使用

db/index.tsts

import { drizzle } from 'drizzle-orm/libsql/node';

import * as schema from "./schema/index";

const db = drizzle({
    connection: {
        url: process.env.DATABASE_URL,
        authToken: process.env.DATABASE_AUTH_TOKEN
    },
    schema,
});

export { db }

api 编写

增 users/index.post.ts

import { db } from "../../../db"
import { user } from "../../../db/schema"

export default defineEventHandler(async (event) => {
    const body = await readBody(event)

    const [inserted] = await db.insert(user).values(body).returning()

    return {
        inserted
    }
})

删 users/[id].delete.ts

import { eq } from "drizzle-orm"

import { db } from "../../../db"
import { user } from "../../../db/schema"

export default defineEventHandler(async (event) => {
    const id = await getRouterParam(event, 'id')

    if (!id) {
        throw createError({ statusCode: 400, message: 'id is required' })
    }

    const result = await db.delete(user).where(eq(user.id, +id))
    return {
        result
    }
})

改 users/[id].put.ts

import { eq } from "drizzle-orm"

import { db } from "../../../db"
import { user } from "../../../db/schema"

export default defineEventHandler(async (event) => {
    const body = await readBody(event)
    const id = getRouterParam(event, 'id')

    if (!id) {
        throw createError({ statusCode: 400, message: 'id is required' })
    }

    const [inserted] = await db.update(user).set(body).where(eq(user.id, +id)).returning()

    return {
        inserted
    }
})

查

根据id 查询 users/[id].get.ts

import { db } from "../../../db"

export default defineEventHandler(async (event) => {
    const id = getRouterParam(event, 'id')

    const user = await db.query.user.findFirst({
        where(fields, operators) {
            return operators.eq(fields.id, +id);
        },
    })

    return {
        user
    }
})

查询全部 users/index.get.ts

import { db } from "../../../db"

export default defineEventHandler(async (event) => {
    const user = await db.query.user.findMany()

    return {
        user
    }
})

drizzle-kit studio 的功能

查看数据

你可以使用 npx drizzle-kit studio 来在本地查看数据库表的情况，如下图所示

运行SQL语句

在线编写 drizzle

常用代码片段

为所有的 schema 添加创建｜编辑｜删除时间

global.schema.tsts

import { boolean, integer, pgTable, timestamp, varchar } from "drizzle-orm/pg-core";

export const timestamps = {
    createdAt: timestamp('created_at').notNull().defaultNow(),
    updatedAt: timestamp('updated_at'),
    deletedAt: timestamp('deleted_at'),
}

使用示例

users.schema.tsts

import { boolean, integer, pgTable, timestamp, varchar } from "drizzle-orm/pg-core";
import { createInsertSchema, createSelectSchema } from "drizzle-zod";

import { timestamps } from './global.schema.ts'

export const users = pgTable("users", {
    id: integer().primaryKey().generatedAlwaysAsIdentity(),
    name: varchar({ length: 255 }).notNull(),
    email: varchar({ length: 255 }).notNull().unique(),

    ...timestamps
});

posts.schema.tsts

import { boolean, integer, pgTable, text, timestamp, varchar } from "drizzle-orm/pg-core";
import { createInsertSchema, createSelectSchema } from "drizzle-zod";

import { timestamps } from './global.schema.ts'

export const posts = pgTable("posts", {
    id: integer().primaryKey().generatedAlwaysAsIdentity(),
    title: varchar({ length: 255 }).notNull(),
    content: text().notNull(),

    ...timestamps
});