@typesql/drizzle

Write TypeSQL queries against your Drizzle schema, and run them on your Drizzle database. The schema is converted in the type checker: nothing is generated.

Install

pnpm add @typesql/core @typesql/drizzle drizzle-orm

Requires drizzle-orm 0.45 or later, before 1.0: on node-postgres or postgres.js for PostgreSQL, any Drizzle SQLite driver, or mysql2, PlanetScale or TiDB Cloud serverless for MySQL and MariaDB.

Usage

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

export const users = pgTable("users", {
  id: serial().primaryKey(),
  email: text().notNull(),
  displayName: text("display_name"),
});

export const posts = pgTable("posts", {
  id: serial().primaryKey(),
  authorId: integer("author_id").notNull().references(() => users.id),
  title: text().notNull(),
});

import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
import { createSql } from "@typesql/drizzle";
import { postgres } from "@typesql/core/postgres";
import * as schema from "./schema";

const db = drizzle(new Pool(), { schema });
const sql = createSql(postgres, db);

const rows = await sql(
  `SELECT u.email, u.display_name, p.title
   FROM users u JOIN posts p ON p.author_id = u.id
   WHERE u.id = ${sql.param(1)}`,
).execute();

Good to know:

• SQL uses the database names (display_name), not the TypeScript keys (displayName).
• .$defaultFn() and .$default() run in Drizzle, not in Postgres, so a SQL INSERT must set those columns.
• Drizzle's types don't record withTimezone, so timestamp() columns are timestamp. Pin a column with pgType from this package: pgType(timestamp("at", { withTimezone: true }), "timestamptz").

SQLite

For a SQLite database, pass sqlite from @typesql/core/sqlite instead: createSql(sqlite, db). The schema is converted to SQLite types; see SQLite.

MySQL and MariaDB

For a drizzle-orm/mysql-core schema on drizzle-orm/mysql2, pass mysql (or mariadb) from @typesql/core/mysql: createSql(mysql, db). Queries run on the database's mysql2 pool or serverless client, so rows are typed the way that driver returns them (on PlanetScale or TiDB Cloud, pass ServerlessTypeMap as the second type argument). Drizzle's types don't record unsigned or the text size; pin those columns with mysqlType: mysqlType(int("n", { unsigned: true }), "int unsigned"). See MySQL and MariaDB.

Documentation

See From Kysely, Drizzle or Prisma for how the schema is converted, running queries and overrides, and Limits for what the conversion can't tell.

License

MIT