Skip to main content

How to use Prisma ORM's type system

This guide introduces Prisma ORM'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 ORM's type system work?​

Prisma ORM uses types to define the kind of data that a field can hold. To make it easy to get started, Prisma ORM 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
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model Post {
  id        Int      @id
  title     String
  createdAt DateTime
}

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 ORM provides default type mappings that map each scalar type to a default type in the underlying database. For example:

  • by default Prisma ORM's String type gets mapped to PostgreSQL's text type and MySQL's varchar type
  • by default Prisma ORM's DateTime type gets mapped to PostgreSQL's timestamp(3) type and SQL Server's datetime2 type

See Prisma ORM'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 ORM 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 ORM type. For this purpose, Prisma ORM 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
model Post {
  id        Int      @id
  title     String
  createdAt DateTime @db.Date
}

Native type mappings allow you to express all the types in your database. However, you do not need to use them if the Prisma ORM 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 ORM will take the database type of each table column and represent it in your Prisma schema using the correct Prisma ORM type for the corresponding model field. If the database type is not the default database type for that Prisma ORM scalar type, Prisma ORM 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
model User {
  id       Int     @id @default(autoincrement())
  name     String
  isActive Boolean
}

The id, name and isActive columns in the database are mapped respectively to the Int, String and Boolean Prisma ORM types. The database types are the default database types for these Prisma ORM types, so Prisma ORM 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 ORM 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
model User {
  id        Int      @id @default(autoincrement())
  name      String
  isActive  Boolean
  createdAt DateTime @db.Date
}

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 ORM will use both the Prisma ORM 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
model Post {
  id        Int      @id
  title     String
  createdAt DateTime
  updatedAt DateTime @db.Date
}

This Post model has:

  • an id field with a Prisma ORM type of Int
  • a title field with a Prisma ORM type of String
  • a createdAt field with a Prisma ORM type of DateTime
  • an updatedAt field with a Prisma ORM 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 ORM's type system​

For further reference information on using Prisma ORM'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 ORM types and database types, and a table of database types with their corresponding native type attribute in Prisma ORM. 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 ORM scalar type. This includes a table of default mappings for that Prisma ORM type in each database, and a table for each database listing the corresponding database types and their native type attributes in Prisma ORM. For example, the entry for the String Prisma ORM type is here.