SQL Schema 声明

你可以直接在 TypeScript 中声明你的 SQL Schema，可以在一个名为 schema.ts 的文件中声明，也可以分散在多个文件中，根据个人喜好选择，非常自由！

1 个文件

单独的文件

单独的文件夹

所有内容都在 1 个文件中：

📦 <project root>
└ 📂 src
└ 📂 db
   └ 📜 schema.ts

分散在多个文件中：

📦 <project root>
└ 📂 src
└ 📂 db
   └ 📂 schema
      ├ 📜 users.ts
      ├ 📜 countries.ts
      ├ 📜 cities.ts
      ├ 📜 products.ts
      ├ 📜 clients.ts
      ├ 📜 enums.ts
      └ 📜 etc.ts

在单独/特定于域的文件夹中：

📦 <project root>
└ 📂 src
 ├ 📂 get-user
 │  ├ 📜 user.ts
 │  └ 📜 handler.ts
 ├ 📂 get-city
 │  ├ 📜 city.ts
 │  └ 📜 handler.ts    
 ├  ...

你可以声明 tables、indexes、constraints、foreign keys 和 enums。

⚠️

如果使用的是 drizzle-kit SQL 迁移生成器，请注意强制 export 关键字。

PostgreSQL

MySQL

SQLite

import { integer, pgEnum, pgTable, serial, uniqueIndex, varchar } from 'drizzle-orm/pg-core';

// declaring enum in database
export const popularityEnum = pgEnum('popularity', ['unknown', 'known', 'popular']);

export const countries = pgTable('countries', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 256 }),
}, (countries) => {
  return {
    nameIndex: uniqueIndex('name_idx').on(countries.name),
  }
});

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

数据库和表的显式实体类型：

import { pgTable, serial, text, varchar } from 'drizzle-orm/pg-core';
import { drizzle } from 'drizzle-orm/node-postgres';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  fullName: text('full_name'),
  phone: varchar('phone', { length: 256 }),
});

export type User = typeof users.$inferSelect; // return type when queried
export type NewUser = typeof users.$inferInsert; // insert type
...

const db = drizzle(...);

const result: User[] = await db.select().from(users);

export async function insertUser(user: NewUser): Promise<User[]> {
  return db.insert(users).values(user).returning();
}

点击此处查看所有支持的 PostgreSQL 列类型。

import { int, mysqlEnum, mysqlTable, uniqueIndex, varchar, serial } from 'drizzle-orm/mysql-core';

// declaring enum in database
export const countries = mysqlTable('countries', {
  id: serial("id").primaryKey(),
  name: varchar('name', { length: 256 }),
}, (countries) => ({
  nameIndex: uniqueIndex('name_idx').on(countries.name),
}));

export const cities = mysqlTable('cities', {
  id: serial("id").primaryKey(),
  name: varchar('name', { length: 256 }),
  countryId: int('country_id').references(() => countries.id),
  popularity: mysqlEnum('popularity', ['unknown', 'known', 'popular']),
});

数据库和表的显式实体类型：

import { MySqlRawQueryResult, mysqlTable, serial, text, varchar } from 'drizzle-orm/mysql-core';
import mysql from 'mysql2/promise';
import { drizzle } from 'drizzle-orm/mysql2';

export const users = mysqlTable('users', {
  id: serial("id").primaryKey(),
  fullName: text('full_name'),
  phone: varchar('phone', { length: 256 }),
});

export type User = typeof users.$inferSelect; // return type when queried
export type NewUser = typeof users.$inferInsert; // insert type
...

// 初始化 mysql2 Pool 或 Client
const poolConnection = mysql.createPool({
    host:'localhost', 
    user: 'root',
    database: 'test'
});

export const db = drizzle(poolConnection);

const result: User[] = await db.select().from(users);

async function insertUser(user: NewUser): Promise<MySqlRawQueryResult> {
  return db.insert(users).values(user);
}

点击此处查看所有支持的 MySQL 列类型。

import { sqliteTable, text, integer, uniqueIndex } from 'drizzle-orm/sqlite-core';

export const countries = sqliteTable('countries', {
    id: integer('id').primaryKey(),
    name: text('name'),
  }, (countries) => ({
    nameIdx: uniqueIndex('nameIdx').on(countries.name),
  })
);

export const cities = sqliteTable('cities', {
  id: integer('id').primaryKey(),
  name: text('name'),
  countryId: integer('country_id').references(() => countries.id),
})

数据库和表的显式实体类型：

import { text, integer, sqliteTable } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: integer('id').primaryKey(),
  fullName: text('full_name'),
  phone: text('phone'),
})

export type User = typeof users.$inferSelect // return type when queried
export type InsertUser = typeof users.$inferInsert // insert type
...
import { drizzle } from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';

const sqlite = new Database('sqlite.db');
const db = drizzle(sqlite);

const result: User[] = db.select().from(users).all();

const insertUser = (user: InsertUser) => {
  return db.insert(users).values(user).run()
}

点击此处查看所有支持的 SQLite 列类型。

Become a Gold Sponsor