drizzle-db-comments-generator

Generate database comments from Drizzle schema.

Drizzle does not support table and column comments yet (drizzle-team/drizzle-orm#886). This package lets you define comments in your Drizzle schema, and generates COMMENT ON statements as a drizzle-kit migration.

Features

• Define table, column and enum comments in your Drizzle schema with withComment().
• Generate a drizzle-kit custom migration with COMMENT ON statements, applied by drizzle-kit migrate as usual.
• Generate statements only for the comments changed since the last generation, including removed comments.
• Read the schema files and the migrations folder from your drizzle-kit config.
• Append the values of enums to the comments of enum columns (optional).

Requirements

• Node.js 22 or later
• drizzle-orm 0.45 and drizzle-kit 0.31
• PostgreSQL
• TypeScript 5.0 or later, if you use TypeScript

Drizzle v1 will be supported in a new major version after its stable release.

Installation

npm install --save-dev @onozaty/drizzle-db-comments-generator

Usage

Define comments

Wrap tables, column definitions and enums with withComment(). It returns what it takes as is, so the types of your tables and columns do not change.

import { withComment } from "@onozaty/drizzle-db-comments-generator";
import { pgTable, serial, text } from "drizzle-orm/pg-core";

export const customers = withComment(
  pgTable("customers", {
    customerId: withComment(serial("customer_id").primaryKey(), "Customer ID"),
    name: withComment(text("name").notNull(), "Customer Name"),
  }),
  "Customer",
);

It works with tables of any table function, and with column definitions anywhere in the method chain.

export const auth = pgSchema("auth");
export const sessions = withComment(auth.table("sessions", { token: text("token") }), "Login sessions");

// Enums (as COMMENT ON TYPE)
export const status = withComment(pgEnum("status", ["ACTIVE", "INACTIVE"]), "User status");

// Column definitions shared by several tables
export const timestamps = {
  createdAt: withComment(timestamp("created_at").defaultNow().notNull(), "Created at"),
};

Generate migrations

Run drizzle-db-comments-generator after drizzle-kit generate, then apply the migrations with drizzle-kit migrate.

npx drizzle-kit generate
npx drizzle-db-comments-generator
npx drizzle-kit migrate

drizzle-db-comments-generator creates a custom migration with drizzle-kit (drizzle-kit generate --custom), and writes the COMMENT ON statements into it. If no comment has changed, it creates no migration.

$ npx drizzle-db-comments-generator
Reading config file '/path/to/project/drizzle.config.ts'
Prepared empty file for your custom SQL migration!
[✓] Your SQL migration file ➜ drizzle/0001_update_comments.sql 🚀
Generated comment migration: drizzle/0001_update_comments.sql

It is convenient to run them together in your npm scripts.

{
  "scripts": {
    "db:generate": "drizzle-kit generate && drizzle-db-comments-generator"
  }
}

Example

From the following schema,

import { withComment } from "@onozaty/drizzle-db-comments-generator";
import { integer, pgEnum, pgTable, serial, text, timestamp } from "drizzle-orm/pg-core";

export const productType = withComment(pgEnum("product_type", ["BOOK", "TOY", "FASHION"]), "Product Type");

export const customers = withComment(
  pgTable("customers", {
    customerId: withComment(serial("customer_id").primaryKey(), "Customer ID"),
    name: withComment(text("name").notNull(), "Customer Name"),
    email: withComment(text("email").notNull().unique(), "e-mail"),
    createdAt: timestamp("created_at").defaultNow().notNull(),
  }),
  "Customer",
);

export const products = withComment(
  pgTable("products", {
    productId: withComment(serial("product_id").primaryKey(), "Product ID"),
    name: withComment(text("name").notNull(), "Product Name"),
    type: withComment(productType("type").notNull(), "Product Type"),
    price: withComment(integer("price").notNull(), "Price"),
  }),
  "Product",
);

the following migration is generated.

-- Generated by drizzle-db-comments-generator

-- "product_type"
COMMENT ON TYPE "product_type" IS 'Product Type';
--> statement-breakpoint

-- "customers"
COMMENT ON TABLE "customers" IS 'Customer';
--> statement-breakpoint
COMMENT ON COLUMN "customers"."customer_id" IS 'Customer ID';
--> statement-breakpoint
COMMENT ON COLUMN "customers"."name" IS 'Customer Name';
--> statement-breakpoint
COMMENT ON COLUMN "customers"."email" IS 'e-mail';
--> statement-breakpoint

-- "products"
COMMENT ON TABLE "products" IS 'Product';
--> statement-breakpoint
COMMENT ON COLUMN "products"."product_id" IS 'Product ID';
--> statement-breakpoint
COMMENT ON COLUMN "products"."name" IS 'Product Name';
--> statement-breakpoint
COMMENT ON COLUMN "products"."type" IS 'Product Type';
--> statement-breakpoint
COMMENT ON COLUMN "products"."price" IS 'Price';

When you then change the comment of name and remove the comment of email, only the differences are generated.

-- Generated by drizzle-db-comments-generator

-- "customers"
COMMENT ON COLUMN "customers"."name" IS 'Full name of the customer';
--> statement-breakpoint
COMMENT ON COLUMN "customers"."email" IS NULL;

CLI options

| Option | Description | |---|---| | --config <file> | The drizzle-kit config file. Defaults to drizzle.config.ts, drizzle.config.js or drizzle.config.json, the same as drizzle-kit. | | --name <name> | The name of the migration. Defaults to update_comments. | | --append-enum-values | Appends the values of enums to the comments of enum columns, such as enum: product_type(BOOK, TOY, FASHION). | | --dry-run | Prints the SQL without creating a migration. |

The schema files (schema), the migrations folder (out) and casing are read from the drizzle-kit config file.

How it works

The comments at the last generation are saved as comments-latest.json in the migrations folder, and only the changes from them are generated. Commit comments-latest.json together with your migrations.

• Added or changed comments are set with COMMENT ON ... IS '...'.
• Removed comments are removed with COMMENT ON ... IS NULL.
• Comments of dropped tables, columns and enums are left as they are, since they are dropped together with them.
• Only exported enums are covered, the same as drizzle-kit, which creates only exported enums.

License

MIT

Author

onozaty