Drizzle V1

import { drizzle } from "drizzle-orm/postgres-js";
import { relations } from "./relations";

const db = drizzle({
  client: getQueryClient(),
  relations,
  casing: "snake_case",
});

// src/db/relations/index.ts
import { defineRelations } from "drizzle-orm";
import * as schema from "@/db/schema";

export const relations = defineRelations(schema, (r) => ({
  // 每个表的关系定义
  users: {
    posts: r.many.posts(),
  },
  posts: {
    author: r.one.users({
      from: r.posts.authorId,
      to: r.users.id,
    }),
  },
}));

// src/db/relations/index.ts
import { defineRelations } from "drizzle-orm";
import * as schema from "@/db/schema";
import { userRolesRelations } from "./admin/user-roles";

export const relations = defineRelations(schema, (r) => ({
  ...userRolesRelations(r),
}));

// src/db/relations/admin/user-roles.ts
import type { ExtractTablesFromSchema, RelationsBuilder } from "drizzle-orm";
import type * as schema from "@/db/schema";

type Schema = ExtractTablesFromSchema<typeof schema>;

export const userRolesRelations = (r: RelationsBuilder<Schema>) => ({
  systemUsers: {
    roles: r.many.systemRoles({
      from: r.systemUsers.id.through(r.systemUserRoles.userId),
      to: r.systemRoles.id.through(r.systemUserRoles.roleId),
    }),
    enabledRoles: r.many.systemRoles({
      from: r.systemUsers.id.through(r.systemUserRoles.userId),
      to: r.systemRoles.id.through(r.systemUserRoles.roleId),
      where: { status: Status.ENABLED },
    }),
  },
  systemRoles: {
    users: r.many.systemUsers(),
  },
});

// one: 指定 from → to 映射
posts: {
  author: r.one.users({
    from: r.posts.authorId,
    to: r.users.id,
  }),
},

// many: 可以只定义 many 侧（无需在对端定义 one）
users: {
  posts: r.many.posts({
    from: r.users.id,
    to: r.posts.authorId,
  }),
},

users: {
  groups: r.many.groups({
    from: r.users.id.through(r.usersToGroups.userId),
    to: r.groups.id.through(r.usersToGroups.groupId),
  }),
},
groups: {
  participants: r.many.users(), // 反向可省略 from/to
},

systemUsers: {
  enabledRoles: r.many.systemRoles({
    from: r.systemUsers.id.through(r.systemUserRoles.userId),
    to: r.systemRoles.id.through(r.systemUserRoles.roleId),
    where: { status: Status.ENABLED },
  }),
},

posts: {
  author: r.one.users({
    from: r.posts.authorId,
    to: r.users.id,
    optional: false, // author 不会是 null
  }),
},

posts: {
  author: r.one.users({
    from: r.posts.authorId,
    to: r.users.id,
    alias: "author_post",
  }),
  reviewer: r.one.users({
    from: r.posts.reviewerId,
    to: r.users.id,
    alias: "reviewer_post",
  }),
},

// 简单等值
db.query.users.findFirst({
  where: { id: userId },
});

// 多条件（AND）
db.query.users.findMany({
  where: { status: Status.ENABLED, username: "admin" },
});

// 操作符
db.query.users.findMany({
  where: {
    id: { gt: 10 },
    name: { like: "M%" },
  },
});

// 按关系过滤（v1 新增！）
db.query.users.findMany({
  where: {
    id: { gt: 10 },
    posts: {
      content: { like: "M%" },
    },
  },
});

// 关系存在性过滤：获取至少有 1 篇 post 的用户
db.query.users.findMany({
  with: { posts: true },
  where: { posts: true },
});

where: {
  // 逻辑组合
  OR: [],        // 或
  AND: [],       // 与
  NOT: {},       // 非

  // RAW SQL
  RAW: (table) => sql`${table.id} = 1`,

  // 按关系过滤
  [relation]: {},  // 如 posts: { content: { like: "M%" } }

  // 列操作符
  [column]: {
    eq: 1,                     // =
    ne: 1,                     // !=
    gt: 1,                     // >
    gte: 1,                    // >=
    lt: 1,                     // <
    lte: 1,                    // <=
    in: [1, 2],                // IN
    notIn: [1, 2],             // NOT IN
    like: "M%",                // LIKE
    ilike: "m%",               // ILIKE（不区分大小写）
    notLike: "M%",             // NOT LIKE
    notIlike: "m%",            // NOT ILIKE
    isNull: true,              // IS NULL
    isNotNull: true,           // IS NOT NULL
    arrayOverlaps: [1, 2],     // 数组重叠
    arrayContained: [1, 2],    // 数组被包含
    arrayContains: [1, 2],     // 数组包含

    // 列级逻辑组合
    OR: [],
    AND: [],
    NOT: {},
  },
}

db.query.users.findMany({
  orderBy: { id: "asc" },
});

// 多字段排序
db.query.users.findMany({
  orderBy: { createdAt: "desc", name: "asc" },
});

// 自定义 SQL 排序
db.query.posts.findMany({
  orderBy: (t) => sql`${t.id} asc`,
});

// 关系内排序
db.query.posts.findMany({
  orderBy: { id: "asc" },
  with: {
    comments: {
      orderBy: { id: "desc" },
    },
  },
});

// 加载全部列
db.query.users.findFirst({
  where: { id: userId },
  with: { roles: true },
});

// 选择部分列
db.query.users.findFirst({
  where: { id: userId },
  with: {
    roles: {
      columns: { id: true, name: true },
    },
  },
});

// 预定义过滤关系
db.query.users.findFirst({
  where: { id: userId },
  with: { enabledRoles: { columns: { id: true } } },
});

db.query.users.findFirst({
  where: { id: userId },
  columns: { id: true, username: true, avatar: true },
});

// v1 新增：关系内也支持 offset
db.query.posts.findMany({
  limit: 5,
  offset: 2,
  with: {
    comments: {
      offset: 3,
      limit: 3,
    },
  },
});

import { sql } from "drizzle-orm";

// 简单 extras
db.query.users.findMany({
  extras: {
    loweredName: sql`lower(${users.name})`,
  },
});

// 回调语法
db.query.users.findMany({
  extras: {
    loweredName: (users, { sql }) => sql`lower(${users.name})`,
  },
});

// 嵌套关系中也支持 extras
db.query.posts.findMany({
  extras: {
    contentLength: (table, { sql }) => sql<number>`length(${table.content})`,
  },
  with: {
    comments: {
      extras: {
        commentSize: (table, { sql }) => sql<number>`length(${table.content})`,
      },
    },
  },
});

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

// 获取用户及其 post 总数
db.query.users.findMany({
  with: { posts: true },
  extras: {
    totalPostsCount: (table) => db.$count(posts, eq(posts.authorId, table.id)),
  },
});

import { sql } from "drizzle-orm";

// where 中使用 placeholder
const prepared = db.query.users.findMany({
  where: { id: { eq: sql.placeholder("id") } },
  with: {
    posts: {
      where: { id: 1 },
    },
  },
}).prepare("query_name");

const result = await prepared.execute({ id: 1 });

// limit/offset 中使用 placeholder
const prepared2 = db.query.users.findMany({
  limit: sql.placeholder("uLimit"),
  offset: sql.placeholder("uOffset"),
  where: {
    OR: [{ id: { eq: sql.placeholder("id") } }, { id: 3 }],
  },
  with: {
    posts: {
      where: { id: { eq: sql.placeholder("pid") } },
      limit: sql.placeholder("pLimit"),
    },
  },
}).prepare("query_name");

const result2 = await prepared2.execute({
  pLimit: 1, uLimit: 3, uOffset: 1, id: 2, pid: 6,
});

// ✅ 正确
const db = drizzle(url, { relations: { ...relations, ...part } })
// ❌ 错误
const db = drizzle(url, { relations: { ...part, ...relations } })

export const mainPart = defineRelationsPart(schema); // 空 main，用于推断所有表

// 旧版：必须嵌套查询连接表再映射
const response = await db.query.users.findMany({
  with: {
    usersToGroups: {
      columns: {},
      with: { group: true },
    },
  },
});
// 还需要手动 map: response.map(u => ({ ...u, groups: u.usersToGroups.map(utg => utg.group) }))

// 新版：直接查多对多，无需映射
const response = await db.query.users.findMany({
  with: { groups: true },
});

// 1. src/db/relations/{tier}/{feature}.ts
import type { ExtractTablesFromSchema, RelationsBuilder } from "drizzle-orm";
import type * as schema from "@/db/schema";

type Schema = ExtractTablesFromSchema<typeof schema>;

export const {feature}Relations = (r: RelationsBuilder<Schema>) => ({
  {parentTable}: {
    {relName}: r.many.{childTable}({
      from: r.{parentTable}.id,
      to: r.{childTable}.{parentTable}Id,
    }),
  },
  {childTable}: {
    {parentRef}: r.one.{parentTable}({
      from: r.{childTable}.{parentTable}Id,
      to: r.{parentTable}.id,
    }),
  },
});

// 2. src/db/relations/index.ts
import { {feature}Relations } from "./{tier}/{feature}";

export const relations = defineRelations(schema, (r) => ({
  ...userRolesRelations(r),
  ...{feature}Relations(r),  // 新增
}));

pnpm drizzle-kit pull