Convert SQL String Columns to Prisma String Fields

SQL String Types to Prisma

SQL databases offer multiple string types with different storage characteristics. Prisma uses a single String scalar type but supports native type annotations via the @db attribute to preserve database-specific semantics.

Example SQL

CREATE TABLE profiles (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(320) NOT NULL,
  bio TEXT,
  country_code CHAR(2) NOT NULL,
  website VARCHAR(2048)
);

Generated Prisma Schema

model Profile {
  id          Int     @id @default(autoincrement())
  username    String  @db.VarChar(50)
  email       String  @db.VarChar(320)
  bio         String? @db.Text
  countryCode String  @db.Char(2)
  website     String? @db.VarChar(2048)

  @@map("profiles")
}

Type Mapping Table

SQL type	Prisma type	Native annotation
VARCHAR(n)	String	@db.VarChar(n)
CHAR(n)	String	@db.Char(n)
TEXT	String	@db.Text
TINYTEXT	String	@db.TinyText (MySQL)
MEDIUMTEXT	String	@db.MediumText (MySQL)
LONGTEXT	String	@db.LongText (MySQL)
CITEXT	String	@db.Citext (PostgreSQL extension)

Why Native Type Annotations Matter

Without @db annotations, Prisma defaults to TEXT (PostgreSQL) or VARCHAR(191) (MySQL). Preserving the original SQL type via @db.VarChar(50) ensures that prisma db push and prisma migrate generate DDL that matches your intended schema exactly.

Length Validation

Prisma itself does not enforce string length at the application level — it relies on the database constraint. If you need runtime validation, combine Prisma with a library like Zod:

const ProfileInput = z.object({
  username: z.string().max(50),
  countryCode: z.string().length(2),
});

Case-Insensitive Strings

PostgreSQL's CITEXT extension provides case-insensitive text storage. The converter detects this and adds the @db.Citext annotation, which is useful for email and username fields where case should not matter.

Column Name Mapping

Snake_case column names like country_code are converted to camelCase countryCode with a @map("country_code") directive. This keeps your Prisma client API idiomatic TypeScript while preserving the original SQL column name.