Utiliser Drizzle ORM avec ClickHouse Managed Postgres

# Runtime queries go through PgBouncer (port 6432)
DATABASE_URL="postgresql://postgres:<PASSWORD>@your-instance.pg.clickhouse.cloud:6432/guide_drizzle?sslmode=verify-full&sslrootcert=ca-certificate.pem"
# drizzle-kit migrations connect directly to Postgres (port 5432)
DIRECT_DATABASE_URL="postgresql://postgres:<PASSWORD>@your-instance.pg.clickhouse.cloud:5432/guide_drizzle?sslmode=verify-full&sslrootcert=ca-certificate.pem"

import { boolean, integer, pgTable, text, timestamp } from "drizzle-orm/pg-core";

export const todos = pgTable("todos", {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  title: text().notNull(),
  done: boolean().notNull().default(false),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
});

import "dotenv/config";
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "postgresql",
  schema: "./src/db/schema.ts",
  out: "./drizzle",
  dbCredentials: {
    // Migrations use the direct connection (port 5432)
    url: process.env.DIRECT_DATABASE_URL!,
  },
});

import "dotenv/config";
import { drizzle } from "drizzle-orm/node-postgres";

// The app uses the PgBouncer connection (port 6432)
export const db = drizzle(process.env.DATABASE_URL!);

import { createServer } from "node:http";
import { desc, eq, not } from "drizzle-orm";
import { db } from "./db";
import { todos } from "./db/schema";

const server = createServer(async (req, res) => {
  const url = new URL(req.url ?? "/", "http://localhost");
  const id = Number(url.pathname.split("/")[2]);
  let body: unknown;

  try {
    if (req.method === "GET" && url.pathname === "/todos") {
      // Read
      body = await db.select().from(todos).orderBy(desc(todos.id));
    } else if (req.method === "POST" && url.pathname === "/todos") {
      // Create
      const title = url.searchParams.get("title") ?? "Untitled";
      [body] = await db.insert(todos).values({ title }).returning();
    } else if (req.method === "PATCH" && id) {
      // Update: toggle the done flag
      [body] = await db
        .update(todos)
        .set({ done: not(todos.done) })
        .where(eq(todos.id, id))
        .returning();
    } else if (req.method === "DELETE" && id) {
      // Delete
      [body] = await db.delete(todos).where(eq(todos.id, id)).returning();
    } else {
      res.writeHead(404).end();
      return;
    }
    res.writeHead(200, { "Content-Type": "application/json" });
    res.end(JSON.stringify(body, null, 2) + "\n");
  } catch (err) {
    console.error(err);
    res.writeHead(500).end("Database error\n");
  }
});

server.listen(3101, () => console.log("Listening on http://localhost:3101/todos"));