# PostgreSQL and Drizzle CRUD

Use `postgres` directly for simple queries. Add Drizzle when the project needs a typed schema and migrations.

```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(),
});
```

## Connection and query

```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));
```

## Migrations and permissions

Node-oriented tools such as `drizzle-kit` may need local `node_modules`. Put generation and migration in a separate task or CI job, review SQL before applying it, and do not grant schema mutation to the production HTTP process.

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

Official references: [Deno Drizzle tutorial](https://docs.deno.com/examples/drizzle_tutorial/), [Postgres example](https://docs.deno.com/examples/postgres/), and [Drizzle PostgreSQL](https://orm.drizzle.team/docs/get-started-postgresql).