One Schema for all (Zod, PostgreSQL, Sqlite)

[m64s]

Is there a way to create just one schema to use for all of these? (Zod, PostgreSQL, Sqlite)

[wesleysmyth]

Not with a single schema definition — Drizzle intentionally uses separate table builders for each dialect (pgTable, sqliteTable, mysqlTable) because their column types and capabilities differ (e.g., PostgreSQL has jsonb, uuid, serial; SQLite has integer primary keys with autoincrement).

However, you can share validation schemas and TypeScript types across both:

1. Shared types via $inferSelect / $inferInsert

// pg-schema.ts
export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: text('name').notNull(),
  email: text('email').notNull(),
});

export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;

// sqlite-schema.ts
export const users = sqliteTable('users', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull(),
});

// Same TypeScript type shape — both conform to User

2. Shared Zod schemas

Use drizzle-zod (or the newer drizzle-orm/zod in v1) to generate Zod schemas from either dialect's table definition. The Zod schemas are dialect-agnostic:

import { createSelectSchema, createInsertSchema } from 'drizzle-zod';

export const selectUserSchema = createSelectSchema(users);
export const insertUserSchema = createInsertSchema(users);

3. If you truly need one definition

Define your "source of truth" as a plain TypeScript interface or Zod schema, then derive both Drizzle schemas from it. The Drizzle schemas become thin adapters over your shared contract. This is more boilerplate but keeps validation logic in one place.

[m64s]

Thank you for your reply.

Does Drizzle have any plan to provide a way to create a unified schema for all databases, such as Prisma?

In example something like this

import { Table, integer, text, timestamp } from "drizzle-orm/sql";

instead of from "drizzle-orm/pg-core" and from "drizzle-orm/sqlite"

like a general way

[wesleysmyth]

Good question. Based on Drizzle's design philosophy and public roadmap, a Prisma-style unified schema isn't planned.

Drizzle is intentionally SQL-first — it wants you to think in terms of the actual database you're targeting, not an abstraction layer over it. The rationale is that PostgreSQL, SQLite, and MySQL have meaningfully different capabilities:

• PostgreSQL has jsonb, uuid, serial, enum, arrays, full-text search, RETURNING *
• SQLite has integer primary keys with autoincrement, no native uuid, no enum, limited ALTER TABLE
• MySQL has AUTO_INCREMENT, TINYINT for booleans, different JSON handling

A unified Table builder would either need to drop to the lowest common denominator (losing features like jsonb), or have dialect-specific escape hatches everywhere — at which point it's not really unified.

That said, the practical workaround for your use case is to define a shared contract and derive dialect schemas from it:

// shared/schema.ts — your source of truth
import { z } from 'zod';

export const UserSchema = z.object({
  id: z.string().uuid(),
  name: z.string().min(1),
  email: z.string().email(),
});

export type User = z.infer<typeof UserSchema>;

// db/pg-schema.ts
import { pgTable, uuid, text } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: text('name').notNull(),
  email: text('email').notNull(),
});

// db/sqlite-schema.ts
import { sqliteTable, text } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: text('id').primaryKey(), // no native uuid in SQLite
  name: text('name').notNull(),
  email: text('email').notNull(),
});

The Zod schema validates at the application boundary (API input, form data), and the Drizzle schemas handle the database layer. Types stay in sync via $inferSelect / $inferInsert and the shared User type.

It's more boilerplate than Prisma's approach, but it's a deliberate tradeoff for SQL fidelity.