Drizzle ORM - DrizzleORM v0.11.0 发布

DrizzleORM v0.11.0 发布

Jul 20, 2022

DrizzleORM - 是一个开源的 TypeScript ORM，支持 PostgreSQL，并且在几周内将支持 MySQL 和 SQLite。我们决定是时候向公众分享它了。

使用 drizzle，您在代码中拥有一个完全类型化的 SQL schema，这在多个主要方面为您带来了好处，我稍后将详细介绍。

// 在数据库中声明枚举
export const popularityEnum = createEnum({ alias: 'popularity', values: ['unknown', 'known', 'popular'] });

export class CountriesTable extends PgTable<CountriesTable> {
  id = this.serial("id").primaryKey();
  name = this.varchar("name", { size: 256 })

  // 声明索引
  nameIndex = this.uniqueIndex(this.name)

  public tableName(): string {
    return 'countries';
  }
}

export class CitiesTable extends PgTable<CitiesTable> {
  id = this.serial("id").primaryKey();
  name = this.varchar("name", { size: 256 })
  countryId = this.int("country_id").foreignKey(CountriesTable, (country) => country.id)

  // 在表中声明枚举列
  popularity = this.type(popularityEnum, "popularity")

  public tableName(): string {
    return 'cities';
  }
}

这是一个快速入门示例，演示如何连接到数据库并执行您的第一个类型化查询。

import { drizzle, PgTable } from 'drizzle-orm'

export class UsersTable extends PgTable<UsersTable> {
  public id = this.serial('id').primaryKey();
  public fullName = this.text('full_name');
  public phone = this.varchar('phone', { size: 256 });

  public tableName(): string {
    return 'users';
  }
}
export type User = InferType<UsersTable>

const db = await drizzle.connect("postgres://user:password@host:port/db");
const usersTable = new UsersTable(db);

const users: User[] = await usersTable.select().execute();

这是如何使用 WHERE 语句进行过滤、运行部分选择查询、使用 limit/offset 和 orderBy 的示例。

await table.select().where(
  eq(table.id, 42)
).execute();

// 您可以使用 eq(...) 或 or(...) 组合过滤条件
await table.select().where(
  and([eq(table.id, 42), eq(table.name, "Dan")])
).execute();

await table.select().where(
  or([eq(table.id, 42), eq(table.id, 1)])
).execute();

// 部分选择
const result = await table.select({
     mapped1: table.id,
     mapped2: table.name,
}).execute();
const { mapped1, mapped2 } = result[0];

// 限制偏移量和排序
await table.select().limit(10).offset(10).execute()
await table.select().orderBy((table) => table.name, Order.ASC)
await table.select().orderBy((table) => table.name, Order.DESC)

这是如何运行 插入、更新 和 删除 的示例。

const result = await usersTable.insert({
  name: "Andrew",
  createdAt: new Date(),
}).execute();

const result = await usersTable.insertMany([{
  name: "Andrew",
  createdAt: new Date(),
}, {
  name: "Dan",
  createdAt: new Date(),
}]).execute();

await usersTable.update()
  .where(eq(usersTable.name, 'Dan'))
  .set({ name: 'Mr. Dan' })
  .execute();

await usersTable.delete()
  .where(eq(usersTable.name, 'Dan'))
  .execute();

我们 ORM 中最强大的功能之一是完全类型化的连接，编译器不会让您出错。

const usersTable = new UsersTable(db);
const citiesTable = new CitiesTable(db);

const result = await citiesTable.select()
  .leftJoin(usersTable, (cities, users) => eq(cities.userId, users.id))
  .where((cities, users) => eq(cities.id, 1))
  .execute();

const citiesWithUsers: { city: City, user: User }[] = result.map((city, user) => ({ city, user }));

这是一个 多对多 关系的示例。

export class UsersTable extends PgTable<UsersTable> {
  id = this.serial("id").primaryKey();
  name = this.varchar("name");
}

export class ChatGroupsTable extends PgTable<ChatGroupsTable> {
  id = this.serial("id").primaryKey();
}

export class ManyToManyTable extends PgTable<ManyToManyTable> {
  userId = this.int('user_id').foreignKey(UsersTable, (table) => table.id, { onDelete: 'CASCADE' });
  groupId = this.int('group_id').foreignKey(ChatGroupsTable, (table) => table.id, { onDelete: 'CASCADE' });
}

...
const usersTable = new UsersTable(db);
const chatGroupsTable = new ChatGroupsTable(db);
const manyToManyTable = new ManyToManyTable(db);

// 查询用户组 ID 为 1 的用户及所有参与者
const usersWithUserGroups = await manyToManyTable.select()
  .leftJoin(usersTable, (manyToMany, users) => eq(manyToManyTable.userId, users.id))
  .leftJoin(chatGroupsTable, (manyToMany, _users, chatGroups) => eq(manyToManyTable.groupId, chatGroups.id))
  .where((manyToMany, _users, userGroups) => eq(userGroups.id, 1))
  .execute();

最后但并非最不重要的是迁移。我们实现了一种 CLI 工具，用于自动生成迁移，这会处理重命名和删除，并提示您解决。

针对以下 TypeScript schema，示例代码如下：

import { PgTable } from "drizzle-orm";

export class UsersTable extends PgTable<UsersTable> {
  public id = this.serial("id").primaryKey();
  public fullName = this.varchar("full_name", { size: 256 });

  public fullNameIndex = this.index(this.fullName);

  public tableName(): string {
    return "users";
  }
}

export class AuthOtpTable extends PgTable<AuthOtpTable> {
  public id = this.serial("id").primaryKey();
  public phone = this.varchar("phone", { size: 256 });
  public userId = this.int("user_id").foreignKey(UsersTable, (t) => t.id);

  public tableName(): string {
    return "auth_otp";
  }
}

-- SQL 迁移
CREATE TABLE IF NOT EXISTS auth_otp (
    "id" SERIAL PRIMARY KEY,
    "phone" character varying(256),
    "user_id" INT
);

CREATE TABLE IF NOT EXISTS users (
    "id" SERIAL PRIMARY KEY,
    "full_name" character varying(256)
);

DO $$ BEGIN
 ALTER TABLE auth_otp ADD CONSTRAINT auth_otp_user_id_fkey FOREIGN KEY ("user_id") REFERENCES users(id);
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

CREATE INDEX IF NOT EXISTS users_full_name_index ON users (full_name);