How to use Prisma's type system
This guide introduces Prisma's type system and explains how to introspect existing native types in your database, and how to use types when you apply schema changes to your database with Prisma Migrate or db push
.
How does Prisma's type system work?
Prisma uses types to define the kind of data that a field can hold. To make it easy to get started, Prisma provides a small number of core scalar types that should cover most default use cases. For example, take the following blog post model:
schema.prisma
1datasource db {2 provider = "postgresql"3 url = env("DATABASE_URL")4}56model Post {7 id Int @id8 title String9 createdAt DateTime10}
The title
field of the Post
model uses the String
scalar type, while the createdAt
field uses the DateTime
scalar type.
Databases also have their own type system, which defines the type of value that a column can hold. Most databases provide a large number of data types to allow fine-grained control over exactly what a column can store. For example, a database might provide inbuilt support for multiple sizes of integers, or for XML data. The names of these types vary between databases. For example, in PostgreSQL the column type for booleans is boolean
, whereas in MySQL the tinyint(1)
type is typically used.
In the blog post example above, we are using the PostgreSQL connector. This is specified in the datasource
block of the Prisma schema.
Default type mappings
To allow you to get started with our core scalar types, Prisma provides default type mappings that map each scalar type to a default type in the underlying database. For example:
- by default Prisma's
String
type gets mapped to PostgreSQL'stext
type and MySQL'svarchar
type - by default Prisma's
DateTime
type gets mapped to PostgreSQL'stimestamp(3)
type and SQL Server'sdatetime2
type
See Prisma's database connector pages for the default type mappings for a given database. For example, this table gives the default type mappings for PostgreSQL.
To see the default type mappings for all databases for a specific given Prisma type, see the model field scalar types section of the Prisma schema reference. For example, this table gives the default type mappings for the Float
scalar type.
Native type mappings
Sometimes you may need to use a more specific database type that is not one of the default type mappings for your Prisma type. For this purpose, Prisma provides native type attributes to refine the core scalar types. For example, in the createdAt
field of your Post
model above you may want to use a date-only column in your underlying PostgreSQL database, by using the date
type instead of the default type mapping of timestamp(3)
. To do this, add a @db.Date
native type attribute to the createdAt
field:
schema.prisma
1model Post {2 id Int @id3 title String4 createdAt DateTime @db.Date5}
Native type mappings allow you to express all the types in your database. However, you do not need to use them if the Prisma defaults satisfy your needs. This leads to a shorter, more readable Prisma schema for common use cases.
How to introspect database types
When you introspect an existing database, Prisma will take the database type of each table column and represent it in your Prisma schema using the correct Prisma type for the corresponding model field. If the database type is not the default database type for that Prisma scalar type, Prisma will also add a native type attribute.
As an example, take a User
table in a PostgreSQL database, with:
- an
id
column with a data type ofserial
- a
name
column with a data type oftext
- an
isActive
column with a data type ofboolean
You can create this with the following SQL command:
CREATE TABLE "public"."User" (id serial PRIMARY KEY NOT NULL,name text NOT NULL,"isActive" boolean NOT NULL);
Introspect your database with the following command run from the root directory of your project:
$npx prisma db pull
You will get the following Prisma schema:
schema.prisma
1model User {2 id Int @id @default(autoincrement())3 name String4 isActive Boolean5}
The id
, name
and isActive
columns in the database are mapped respectively to the Int
, String
and Boolean
Prisma types. The database types are the default database types for these Prisma types, so Prisma does not add any native type attributes.
Now add a createdAt
column to your database with a data type of date
by running the following SQL command:
ALTER TABLE "public"."User"ADD COLUMN "createdAt" date NOT NULL;
Introspect your database again:
$npx prisma db pull
Your Prisma schema now includes the new createdAt
field with a Prisma type of DateTime
. The createdAt
field also has a @db.Date
native type attribute, because PostgreSQL's date
is not the default type for the DateTime
type:
schema.prisma
1model User {2 id Int @id @default(autoincrement())3 name String4 isActive Boolean+ createdAt DateTime @db.Date6}
How to use types when you apply schema changes to your database
When you apply schema changes to your database using Prisma Migrate or db push
, Prisma will use both the Prisma scalar type of each field and any native attribute it has to determine the correct database type for the corresponding column in the database.
As an example, create a Prisma schema with the following Post
model:
schema.prisma
1model Post {2 id Int @id3 title String4 createdAt DateTime5 updatedAt DateTime @db.Date6}
This Post
model has:
- an
id
field with a Prisma type ofInt
- a
title
field with a Prisma type ofString
- a
createdAt
field with a Prisma type ofDateTime
- an
updatedAt
field with a Prisma type ofDateTime
and a@db.Date
native type attribute
Now apply these changes to an empty PostgreSQL database with the following command, run from the root directory of your project:
$npx prisma db push
You will see that the database has a newly created Post
table, with:
- an
id
column with a database type ofinteger
- a
title
column with a database type oftext
- a
createdAt
column with a database type oftimestamp(3)
- an
updatedAt
column with a database type ofdate
Notice that the @db.Date
native type attribute modifies the database type of the updatedAt
column to date
, rather than the default of timestamp(3)
.
More on using Prisma's type system
For further reference information on using Prisma's type system, see the following resources:
- The database connector page for each database provider has a type mapping section with a table of default type mappings between Prisma types and database types, and a table of database types with their corresponding native type attribute in Prisma. For example, the type mapping section for PostgreSQL is here.
- The model field scalar types section of the Prisma schema reference has a subsection for each Prisma scalar type. This includes a table of default mappings for that Prisma type in each database, and a table for each database listing the corresponding database types and their native type attributes in Prisma. For example, the entry for the
String
Prisma type is here.