PostgreSQL and Drizzle CRUD

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

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

Schema

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

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

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.

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

Official references: Deno Drizzle tutorial, Postgres example, and Drizzle PostgreSQL.