Dev.to WebDev πŸ›  Dev πŸ‘ 0 πŸ“– 2 min read

Migrating Legacy PostgreSQL DDL to Drizzle ORM Without Losing NUMERIC Precision or Timezones

When migrating an existing PostgreSQL schema to TypeScript (drizzle-orm/pg-core or Prisma), drizzle-kit introspect works well if you have direct network access to the database. In locked-down environments where all you h

When migrating an existing PostgreSQL schema to TypeScript (drizzle-orm/pg-core or Prisma), drizzle-kit introspect works well if you have direct network access to the database. In locked-down environments where all you have is a schema.sql dump or a migration file, hand-translating 30+ columns quickly introduces subtle runtime bugs.

Here are the three type-mapping traps that bite teams most often, along with a local browser converter we built so internal DDL never has to be pasted into a server-backed tool.

1. NUMERIC(p, s) and BIGINT Quietly Overflowing number

JavaScript's IEEE-754 number only guarantees exact integers up to Number.MAX_SAFE_INTEGER (9,007,199,254,740,991).

If you map a ledger column amount NUMERIC(19, 4) or a Snowflake ID BIGINT to a plain JS number mode, values silently round in transit:

import { pgTable, bigint, numeric, timestamp, uuid } from "drizzle-orm/pg-core";

export const settlements = pgTable("settlements", {
  // Always specify mode: "bigint" (or "number" ONLY if guaranteed < 2^53)
  id: bigint("id", { mode: "bigint" }).primaryKey(),
  tenantId: uuid("tenant_id").notNull(),
  // Drizzle returns numeric() as string by default to preserve exact decimal scale
  grossAmount: numeric("gross_amount", { precision: 19, scale: 4 }).notNull(),
  settledAt: timestamp("settled_at", { withTimezone: true, mode: "date" }).notNull(),
});

Notice withTimezone: true on timestamp. Omitting it generates TIMESTAMP WITHOUT TIME ZONE, causing pg drivers to interpret UTC strings against the container's local timezone offset.

2. Composite Indexes & ON DELETE Referential Actions

When translating CREATE INDEX CONCURRENTLY idx_orders_tenant_created ON orders (tenant_id, created_at DESC), keeping column order intact inside the table's third callback argument prevents accidental schema drift on the next drizzle-kit generate:

import { index, pgTable, timestamp, uuid } from "drizzle-orm/pg-core";

export const orders = pgTable(
  "orders",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    tenantId: uuid("tenant_id").notNull(),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
  },
  (table) => [
    index("idx_orders_tenant_created").on(table.tenantId, table.createdAt),
  ]
);

3. Converting DDL, JSON Payloads & OpenAPI Specs Locally in Browser RAM

In many companies, pasting internal CREATE TABLE definitions, openapi.yaml specs, or sample customer JSON responses into web converters or external LLM chats isn't permitted because those tools send the payload to a backend API.

To handle these conversions offline in the browser tab (0 bytes sent over the network, verifiable in DevTools -> Network):

If you run into a dialect-specific PostgreSQL or MySQL constraint syntax that doesn't map cleanly, feel free to share a sanitized snippet in the comments.

πŸ“° Read the original article on Dev.to WebDev

Originally published by Dev.to WebDev. Aggregated on AIWithGhost for educational purposes β€” full credit and traffic to the original publisher.