skip to content

In Prisma 7 on PostgreSQL, what column type does a plain String, DateTime or Decimal field get, and when do you add a @db attribute?

level: middleimportance: should knowfreq 34%

answer

  1. the connector picks the column
  2. text, timestamp(3), decimal(65,30)
  3. prefix is the datasource name
  4. column changes, TypeScript type does not

basics

~10 s

Without a native type attribute, Prisma's PostgreSQL connector maps String to text, DateTime to timestamp(3) and Decimal to decimal(65,30). An attribute such as @db.VarChar(255), @db.Timestamptz(6) or @db.Decimal(10, 2) pins the exact column type instead.

solid answer

~40 s

Prisma's scalar types are portable, and the datasource `provider` chooses the column. On PostgreSQL the defaults are `String` to `text`, `Int` to `integer`, `BigInt` to `bigint`, `Float` to `double precision`, `Decimal` to `decimal(65,30)`, `DateTime` to `timestamp(3)` and `Json` to `jsonb`. A **native type attribute** overrides that: `@db.VarChar(255)` adds a length limit, `@db.Uuid` stores a real `uuid`, `@db.Timestamptz(6)` gives a time-zone-aware column with microseconds, and `@db.Decimal(10, 2)` fixes precision and scale for money. The prefix is the datasource block's name, so `datasource db` gives `@db.`. The attribute changes the column and the migration, not the generated TypeScript: a `@db.VarChar(255)` field is still `string`, and an over-long value fails at the database with `P2000`. Introspection writes a `@db` attribute only where the column differs from the default.

code

prisma · 12 lines
prisma
datasource db {
  provider = "postgresql"
}

model User {
  id       String   @id @default(uuid()) @db.Uuid
  email    String   @unique @db.VarChar(255)
  bio      String
  balance  Decimal  @db.Decimal(10, 2)
  joinedAt DateTime @default(now())
  seenAt   DateTime @db.Timestamptz(6)
}

go deeper

for a junior

Remember that String is text and DateTime is timestamp(3) on PostgreSQL unless a @db attribute says otherwise, and that @db.VarChar(255) is how a length limit is written.

for a middle

Explain that the connector maps scalar types to columns, that native types change the column but not the TypeScript type, and that the prefix follows the datasource name.

for a senior

Show you know where a native type bites in production: P2000 on over-long values, a narrowing ALTER failing on existing rows, and introspection writing @db only where a legacy column differs.

for a principal

Decide which fields a team must pin with native types (money, timestamps, keys) so schema reviews catch an accidental default mapping before it becomes a data migration.

## Portable types, connector-specific columns A Prisma schema field has a **scalar type** such as `String` or `DateTime`. That type is deliberately portable: the same schema line can target PostgreSQL, MySQL, SQL Server or SQLite. What the column becomes is decided by the **connector**, the value of `provider` in the `datasource` block. Each connector has a **default mapping**, and a **native type attribute** replaces the default for one field. On PostgreSQL the defaults and common overrides are: | Prisma type | Default column | Common `@db` overrides | | --- | --- | --- | | `String` | `text` | `@db.VarChar(n)`, `@db.Char(n)`, `@db.Uuid`, `@db.Citext` | | `Int` | `integer` | `@db.SmallInt` | | `BigInt` | `bigint` | none needed in most schemas | | `Float` | `double precision` | `@db.Real` | | `Decimal` | `decimal(65,30)` | `@db.Decimal(p, s)`, `@db.Money` | | `DateTime` | `timestamp(3)` | `@db.Timestamptz(n)`, `@db.Date`, `@db.Time(n)` | | `Json` | `jsonb` | `@db.Json` | The same `String` field gets `varchar(191)` on MySQL and `nvarchar(1000)` on SQL Server, which is why a schema moved between databases can change behaviour even though no line changed. ## How a native type attribute is written - The attribute is the datasource name, a dot and a PascalCase type: with `datasource db { ... }` it is `@db.VarChar(255)`. Rename the block to `datasource pg` and the attribute must become `@pg.VarChar(255)`; the validator rejects `@db.` with "The prefix db is invalid. It must be equal to the name of an existing datasource". - Arguments follow the database's own syntax: length for `VarChar(n)`, precision and scale for `Decimal(p, s)`, fractional-second digits for `Timestamptz(n)`. - The attribute must fit the field's scalar type. `id Int @db.VarChar(10)` is a validation error: "Native type VarChar is not compatible with declared field type Int, expected field type String." - Some types depend on the database itself: `@db.Citext` works only when the `citext` extension is enabled. ## When a team adds one 1. **A length limit the database should enforce**, such as `email String @db.VarChar(255)`. The default `text` has no limit. 2. **A real UUID column**: `id String @id @default(uuid()) @db.Uuid` stores 16 bytes in a `uuid` column instead of a 36-character `text`, and lets the database reject malformed values. 3. **Time-zone-aware timestamps**: `@db.Timestamptz(6)` when other systems read the column and expect `timestamptz`, or when microsecond precision matters. The default `timestamp(3)` keeps milliseconds and no zone. 4. **Exact money amounts**: `price Decimal @db.Decimal(10, 2)`. Plain `Decimal` gives `decimal(65,30)`, thirty digits of scale that no currency needs, and `Float` is binary floating point. 5. **Matching an existing database.** When you introspect a database with `prisma db pull`, Prisma writes a `@db` attribute only where a column's type differs from the connector's default, so a legacy `varchar(80)` column arrives as `String @db.VarChar(80)`. Choosing the right column type for money, timestamps or keys is a SQL design question covered with SQL data types; the Prisma-specific part is how the choice is spelled and where it takes effect. ## What the attribute does not change - **The client type.** Prisma Client maps by scalar type, so `String @db.VarChar(255)` is still `string`, `DateTime @db.Date` is still a JavaScript `Date`, and `Decimal @db.Decimal(10, 2)` is still a `Decimal` object. TypeScript cannot know the length limit. - **Where violations surface.** A 300-character email passes type checking and fails when the database rejects it; Prisma reports that as `P2000`, "The provided value for the column is too long for the column's type." Validate lengths in the application layer if users should see a friendly message. - **Existing data, automatically.** Adding or changing a `@db` attribute on a field that already exists is a schema change: the next migration carries an `ALTER` of the column type, and a narrowing change can fail on rows that no longer fit. ## Reading a schema line `seenAt DateTime @db.Timestamptz(6)` reads: a required field named `seenAt`, a JavaScript `Date` in the client, stored in a `timestamptz(6)` column. Remove the attribute and the same field becomes `timestamp(3)`. Interviewers ask this to check that a candidate knows the schema controls two layers (database columns and client types) and that native types touch only the first.

  • Why does Prisma 7 reject @db.VarChar(255) after the datasource block is renamed to pg?
    The native type prefix is the datasource block's name, not a fixed keyword. With `datasource pg`, every native type must be written `@pg.VarChar(255)`; the validator reports that the prefix `db` is invalid and suggests `pg.VarChar`. Most projects keep `datasource db`, which is why `@db.` looks fixed.
  • What happens at runtime when a value exceeds a @db.VarChar(255) column in Prisma 7?
    Nothing stops it in TypeScript, because the field is typed `string`. The database rejects the write and Prisma Client raises a known request error with code `P2000`, "The provided value for the column is too long for the column's type." Validate lengths before the write if the user should see a friendly message.

  • A Prisma String field always becomes varchar(255) on PostgreSQL.
  • Adding @db.VarChar(255) makes Prisma Client's type reject long strings at compile time.
  • The @db prefix is a fixed keyword, whatever the datasource block is called.
  • A plain Decimal field already stores money with two decimal places.
  • Prisma's DateTime maps to timestamptz on PostgreSQL by default.