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?
answer
- the connector picks the column
- text, timestamp(3), decimal(65,30)
- prefix is the datasource name
- column changes, TypeScript type does not
basics
~10 sWithout 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 sPrisma'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 linesdatasource 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
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.
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.
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.
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.