# PostgreSQL 与 Drizzle CRUD

简单查询可以直接使用 `postgres`；当项目需要类型化 schema 与 migration 时再引入 Drizzle。

```bash
deno install npm:drizzle-orm npm:drizzle-kit npm:postgres
```

## Schema

```ts title="src/db/schema.ts"
import { integer, pgTable, text, timestamp } from "drizzle-orm/pg-core";

export const posts = pgTable("posts", {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  title: text().notNull(),
  body: text().notNull(),
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
});
```

## 连接与查询

```ts title="src/db/client.ts"
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema.ts";

const url = Deno.env.get("DATABASE_URL");
if (!url) throw new Error("DATABASE_URL is required");

const client = postgres(url, { max: 5 });
export const db = drizzle(client, { schema });
export const closeDb = () => client.end();
```

```ts
import { eq } from "drizzle-orm";
import { db } from "./client.ts";
import { posts } from "./schema.ts";

const [created] = await db.insert(posts)
  .values({ title: "Hello Deno", body: "First post" })
  .returning();

const result = await db.select().from(posts).where(eq(posts.id, created.id));
```

## Migration 与权限

`drizzle-kit` 这类 Node 工具可能需要本地 `node_modules`。把 generate/migrate 放到独立 task 或 CI job，审核 SQL 后再应用，不要给线上 HTTP 进程 schema 修改权限。

```bash
deno run --allow-env=DATABASE_URL --allow-net=db.example.com:5432 src/script.ts
```

官方参考：[Deno Drizzle tutorial](https://docs.deno.com/examples/drizzle_tutorial/)、[Postgres example](https://docs.deno.com/examples/postgres/)、[Drizzle PostgreSQL](https://orm.drizzle.team/docs/get-started-postgresql)。