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}
5
6model Post {
7  id        Int      @id
8  title     String
9  createdAt DateTime
10}

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.

schema.prisma
1model Post {
2  id        Int      @id
3  title     String
4  createdAt DateTime @db.Date
5}

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 of serial
• a name column with a data type of text
• an isActive column with a data type of boolean

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     String
4  isActive Boolean
5}

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      String
4  isActive  Boolean
+  createdAt DateTime @db.Date
6}

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      @id
3  title     String
4  createdAt DateTime
5  updatedAt DateTime @db.Date
6}

This Post model has:

• an id field with a Prisma type of Int
• a title field with a Prisma type of String
• a createdAt field with a Prisma type of DateTime
• an updatedAt field with a Prisma type of DateTime 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 of integer
• a title column with a database type of text
• a createdAt column with a database type of timestamp(3)
• an updatedAt column with a database type of date

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.