SQL to Prisma Schema

Prisma describes a database in a single schema.prisma file of models, fields and relations. When the database already exists, or the design starts as SQL, writing those models by hand is slow and it is easy to get a relation or a default wrong. This converter reads CREATE TABLE statements and writes the Prisma schema for them.

What the parser understands

It reads CREATE TABLE statements written for PostgreSQL, MySQL or SQLite, including quoted and backticked names, SERIAL and IDENTITY columns, AUTO_INCREMENT, inline and table-level PRIMARY KEY, UNIQUE and FOREIGN KEY constraints, ON DELETE actions and default values. PostgreSQL enums declared with CREATE TYPE … AS ENUM and MySQL ENUM(...) columns become Prisma enums, and CREATE INDEX statements become @@index lines.

Types and defaults

Each SQL type maps to a Prisma scalar: integers to Int or BigInt, NUMERIC to Decimal, text types to String, BOOLEAN to Boolean, timestamps to DateTime and JSON to Json. Where the database type carries detail Prisma would otherwise lose, it is kept as a native type attribute such as @db.VarChar(255) or @db.Decimal(10, 2). Defaults are translated too: auto-incrementing keys get @default(autoincrement()), now() and CURRENT_TIMESTAMP become @default(now()), and literal values are carried over.

Names: models, fields and @map

Tables are usually plural and snake_case, while Prisma models are conventionally singular PascalCase and fields camelCase. With the naming options on, order_items becomes model OrderItem with @@map("order_items"), and user_id becomes userId with @map("user_id"). The database keeps its names, so migrations and other applications are not affected; only your TypeScript code sees the friendlier ones.

Relations in both directions

A foreign key produces three things: the scalar column, a relation field that names the fields and references, including onDelete, and a list field on the other model so you can navigate back. Composite primary keys, typical of join tables, become @@id([...]).

A worked example

The Sample has four tables. The orders table turns into a model with id BigInt @id @default(autoincrement()), userId Int @map("user_id"), status OrderStatus @default(pending), total Decimal @db.Decimal(10, 2) and placedAt DateTime @default(now()) @map("placed_at"). It also gains user User @relation(fields: [userId], references: [id], onDelete: Cascade), a list of OrderItem, an @@index([userId]) from the separate CREATE INDEX statement and @@map("orders").

What Prisma cannot describe

CHECK constraints, triggers, views, stored functions and partial indexes have no place in a Prisma schema and are skipped, though the database keeps enforcing them. Review relation field names when one table references another twice, and run prisma migrate diff against the real database before applying anything.

Choosing the provider

The provider setting fills in the datasource block and decides which native type attributes are written, because each database spells types differently: @db.VarChar and @db.Timestamptz exist for PostgreSQL, @db.DateTime(0) for MySQL, and SQLite has almost none. Pick the database the schema will actually run on, not the one the SQL was originally written for. If you are moving from MySQL to PostgreSQL, convert with the new provider and compare the native types the output proposes with your data, especially for TINYINT(1) booleans, unsigned integers and DATETIME columns without a time zone.

After converting

Save the output as prisma/schema.prisma, set the connection string in DATABASE_URL, and run prisma generate to build the client. For an existing database, prisma migrate diff --from-schema-datasource prisma/schema.prisma --to-schema-datamodel prisma/schema.prisma --script should print nothing when the schema matches; any SQL it prints shows a difference worth reading before you baseline migrations.

Related tools

• SQL to Drizzle & TypeORM writes the same schema for other TypeScript ORMs.
• ER Diagram Maker draws the tables and relationships.
• SQL Formatter tidies the input statements.
• JSON to SQL creates tables from sample JSON records.