typespec-drizzle-orm-generator

Generate a complete Drizzle ORM package from TypeSpec models. You define your domain in TypeSpec, the emitter gives you table schemas, relations, and typed query functions. Supports PostgreSQL (default) and SQLite dialects.

Install

npm install @kattebak/typespec-drizzle-orm-generator

Quick start

1. Define your models

import "@kattebak/typespec-drizzle-orm-generator";

using DrizzleEmitter;

@entity("Author", "bookstore")
@primaryKey("authors")
model Author {
  @pk @uuid("base36", true)
  authorId: string;

  name: string;
  bio?: string;

  @createdAt createdAt: utcDateTime;
  @updatedAt updatedAt: utcDateTime;
}

@entity("Book", "bookstore")
@primaryKey("books")
model Book {
  @pk @uuid("base36", true)
  bookId: string;

  @uuid("base36") @references(Author.authorId)
  authorId: string;

  title: string;
  @unique isbn?: string;
  @minValue(1) @maxValue(5) rating: int32;

  @createdAt createdAt: utcDateTime;
  @updatedAt updatedAt: utcDateTime;
}

2. Configure and run the emitter

Add a tspconfig.yaml to your TypeSpec project:

emit:
  - "@kattebak/typespec-drizzle-orm-generator"

options:
  "@kattebak/typespec-drizzle-orm-generator":
    "package-name": "@myorg/bookstore-db"
    "package-version": "1.0.0"
    dialect: pg          # "pg" (default) or "sqlite"

Then compile:

npx tsp compile .

The emitter produces 6 files as a ready-to-use npm package:

| File | What's in it | | -------------- | ---------------------------------------------------------------------- | | schema.ts | Table definitions (pgTable/sqliteTable), enums, constraints | | relations.ts | defineRelations() with through() for many-to-many | | describe.ts | One typed query function per entity (fetch by PK with all relations) | | types.ts | base36Uuid custom type, DrizzleClient type alias | | index.ts | Barrel re-exports | | package.json | Package metadata with drizzle-orm dependency |

3. Use the generated code

Install the generated package in your application (e.g. from the emitter output directory or after publishing):

npm install ./tsp-output/@kattebak/typespec-drizzle-orm-generator

Then use it:

import { drizzle } from "drizzle-orm/node-postgres";
import { relations } from "@myorg/bookstore-db/relations.js";
import * as schema from "@myorg/bookstore-db/schema.js";
import { describeAuthor, describeBook } from "@myorg/bookstore-db/describe.js";

// Initialize the client with schema + relations
const db = drizzle({ connection: process.env.DATABASE_URL, schema, relations });

// Fetch an author with all their books in one query
const author = await describeAuthor(db, "abc123");
// author.name, author.books[0].title — fully typed

// Fetch a book with its author
const book = await describeBook(db, "xyz789");
// book.author.name, book.rating — fully typed

// Or use the schema directly for custom queries
import { authors } from "@myorg/bookstore-db/schema.js";
import { eq } from "drizzle-orm";

const result = await db.select().from(authors).where(eq(authors.name, "Tolkien"));

Generated output examples

schema.ts

import { check, integer, pgTable, text, timestamp } from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm";
import { base36Uuid } from "./types.js";

export const authors = pgTable("authors", {
  authorId: base36Uuid("author_id").primaryKey().defaultRandom(),
  name: text("name").notNull(),
  bio: text("bio"),
  createdAt: timestamp("created_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
  updatedAt: timestamp("updated_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
});

export const books = pgTable(
  "books",
  {
    bookId: base36Uuid("book_id").primaryKey().defaultRandom(),
    authorId: base36Uuid("author_id")
      .notNull()
      .references(() => authors.authorId),
    title: text("title").notNull(),
    isbn: text("isbn").unique(),
    rating: integer("rating").notNull(),
    createdAt: timestamp("created_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
    updatedAt: timestamp("updated_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
  },
  (table) => [
    check(
      "books_rating_check",
      sql`${table.rating} >= 1 AND ${table.rating} <= 5`,
    ),
  ],
);

relations.ts

import { defineRelations } from "drizzle-orm";
import * as schema from "./schema.js";

export const relations = defineRelations(schema, (r) => ({
  authors: {
    books: r.many.books(),
  },
  books: {
    author: r.one.authors({
      from: r.books.authorId,
      to: r.authors.authorId,
    }),
  },
}));

describe.ts

import type { DrizzleClient } from "./types.js";
import * as schema from "./schema.js";

export type AuthorDescription = typeof schema.authors.$inferSelect & {
  books: (typeof schema.books.$inferSelect)[];
};

export const describeAuthor = (
  db: DrizzleClient,
  authorId: string,
): Promise<AuthorDescription | undefined> =>
  db.query.authors.findFirst({
    where: { authorId },
    with: { books: true },
  });

Decorators

Defining entities

@entity("Author", "bookstore")   // entity name + service grouping
@primaryKey("authors")            // SQL table name
model Author {
  @pk                             // marks field as primary key column
  @uuid("base36", true)           // UUID with base36 encoding, auto-generated
  authorId: string;

  @references(Author.authorId)    // foreign key to another entity
  authorId: string;

  @junction                       // on model: marks as many-to-many junction table

  @createdAt                      // DEFAULT NOW()
  createdAt: utcDateTime;

  @updatedAt                      // DEFAULT NOW() + BEFORE UPDATE trigger
  updatedAt: utcDateTime;
}

Adding constraints

@unique                                  // single-column UNIQUE
isbn?: string;

@minValue(1) @maxValue(5)                // CHECK (rating >= 1 AND rating <= 5)
rating: int32;

@check("price > 0")                      // arbitrary CHECK expression
price: float64;

// Model-level decorators:
@compositeUnique("uq_book_lang", [Edition.bookId, Edition.language])
@indexDef("idx_author_year", [Book.authorId, Book.publicationYear])
@indexDef("idx_isbn", [Book.isbn], true)  // unique index
@foreignKeyDef("fk_order", [cols...], [foreignCols...])  // composite FK

Many-to-many with junction tables

@entity("BookGenre", "bookstore")
@junction                                 // skip describe generation, enable through()
@primaryKey("book_genres")
model BookGenre {
  @pk @uuid("base36") @references(Book.bookId)
  bookId: string;

  @pk @uuid("base36") @references(Genre.genreId)
  genreId: string;
}

This generates many-to-many relations using Drizzle v2's through():

books: {
  genres: r.many.genres({
    from: r.books.bookId.through(r.bookGenres.bookId),
    to: r.genres.genreId.through(r.bookGenres.genreId),
  }),
}

Nullable foreign keys

Make a field optional to get a nullable FK. The describe type reflects this as | null:

@references(Translator.translatorId)
translatorId?: string;                    // nullable FK

export type EditionDescription = typeof schema.editions.$inferSelect & {
  translator: typeof schema.translators.$inferSelect | null;
};

Dialect option

Set dialect in your tspconfig.yaml to control the target database. Defaults to pg.

| Value | Table function | Import source | Enum support | | -------- | --------------- | ------------------------- | ------------ | | pg | pgTable | drizzle-orm/pg-core | pgEnum() | | sqlite | sqliteTable | drizzle-orm/sqlite-core | mapped to text() |

When using sqlite, types that don't exist natively in SQLite are mapped to compatible alternatives:

| TypeSpec type | PostgreSQL | SQLite | | ------------- | ----------------------------------- | --------------------------------------- | | boolean | boolean() | integer({ mode: "boolean" }) | | utcDateTime | timestamp({ withTimezone: true }) | integer({ mode: "timestamp" }) | | float64 | doublePrecision() | real() | | int64 | bigint({ mode: "number" }) | integer({ mode: "number" }) | | string (with length) | varchar({ length }) | text({ length }) | | TypeSpec enum | pgEnum() | text() (no native enums in SQLite) |

Type mapping (PostgreSQL)

| TypeSpec | Drizzle | PostgreSQL | | ---------------------- | ----------------------------------- | ------------------------- | | string | text() | text | | string (with length) | varchar({ length }) | varchar(n) | | int32 | integer() | integer | | int64 | bigint({ mode: "number" }) | bigint | | float32 | real() | real | | float64 | doublePrecision() | double precision | | boolean | boolean() | boolean | | utcDateTime | timestamp({ withTimezone: true }) | timestamptz | | string + @uuid | base36Uuid() | uuid | | TypeSpec enum | pgEnum() | CREATE TYPE ... AS ENUM |

Full example

See test/fixtures/bookstore.tsp for a complete 9-entity bookstore domain with authors, books, genres (many-to-many via junction), editions (3 FKs including nullable), reviews, tags, translators, and publishers.

Contributing

npm install
npm test            # type-check + 298 tests
npm run lint:fix    # auto-fix with Biome

Tests run against SQLite in-memory (no Postgres needed). See doc/ for the RFC and implementation plan.

License

MIT