Introspection

You can introspect your database using the Prisma CLI in order to generate the data model in your Prisma schema. The data model is needed to generate Prisma Client.

Introspection is often used to generate an initial version of the data model when adding Prisma to an existing project.

However, it can also be used repeatedly in an application. This is most commonly the case when you're not using Prisma Migrate but perform schema migrations using plain SQL or another migration tool. In that case, you also need to re-introspect your database and subsequently re-generate Prisma Client to reflect the schema changes in your Prisma Client API.

What does introspection do?

Introspection has one main function: Populate your Prisma schema with a data model that reflects the current database schema.

Introspect your database with Prisma

Here's an overview of its main functions:

You can learn more about how Prisma maps types from the database to the types available in the Prisma schema on the respective docs page for the data source connector:

The prisma introspect command

You can introspect your database using the prisma introspect command of the Prisma CLI. Note that using this command requires your connection URL to be set in your Prisma schema!

Here's a high-level overview of the steps that prisma introspect performs internally:

  1. Read the connection URL from the datasource configuration in the Prisma schema
  2. Open database connection
  3. Introspect database schema (i.e. read tables, columns and other structures ...)
  4. Transform database schema into Prisma data model
  5. Write data model into Prisma schema or update existing schema

Introspection with an existing schema

In version 2.6.0 and later, running prisma introspect with an existing schema merges manual changes made to the schema with changes made in the database. Introspection maintains the following manual changes:

  • Order of model blocks
  • Order of enum blocks
  • Comments
  • @map and @@map attributes
  • @updatedAt
  • @default(cuid()) (cuid() is a Prisma-level function)
  • @default(uuid()) (uuid() is a Prisma-level function)
  • Custom @relation names

The following properties of the schema are determined by the database:

  • Order of fields within model blocks
  • Order of values within enum blocks

Note: All enum blocks are listed below model blocks.

Force overwrite

To overwrite manual changes, generate a schema based solely on the introspected database and ignore any existing schema file, add the --force flag to the introspect command:

$npx prisma introspect --force

Use cases include:

  • You want to start from scratch with a schema generated from the underlying database
  • You have an invalid schema and must use --force to make introspection work

Introspection workflow

The typical workflow for projects that are not using Prisma Migrate, but instead use plain SQL or another migration tool looks as follows:

  1. Change the database schema (e.g. using plain SQL)
  2. Run prisma introspect to update the Prisma schema
  3. Run prisma generate to update Prisma Client
  4. Use the updated Prisma Client in your application

Note that as you evolve the application, this process can be repeated for an indefinite number of times.

Introspection workflow

Rules and conventions

Prisma employs a number of conventions for translating a database schema into a Prisma data model:

Model, field and enum names

Field, model and enum names (identifiers) must start with a letter and generally must only contain underscores, letters and digits. You can find the naming rules and conventions for each of these identifiers on the respective docs page:

The general rule for identifiers is that they need to adhere to this regular expression:

[A-Za-z][A-Za-z0-9_]*

Invalid characters are being sanitized during introspection:

  • If they appear before a letter in an identifier, they get dropped.
  • If they appear after the first letter, they get replaced by an underscore.

Additionally, the transformed name is mapped to the database using @map or @@map to retain the original name.

Consider the following table as an example:

CREATE TABLE "42User" (
_id SERIAL PRIMARY KEY,
_name VARCHAR(255),
two$two INTEGER
);

Because the leading 42 in the table name as well as the leading underscores and the $ on the columns are forbidden in Prisma, introspection adds the @map and @@map attributes so that these names adhere to Prisma's naming conventions:

model User {
id Int @default(autoincrement()) @id @map("_id")
name String? @map("_name")
two_two Int? @map("two$two")
@@map("42User")
}

If sanitization results in duplicate identifiers, no immediate error handling is in place. You get the error later and can manually fix it. Consider the case of the following two tables:

CREATE TABLE "42User" (
_id SERIAL PRIMARY KEY
);
CREATE TABLE "24User" (
_id SERIAL PRIMARY KEY
);

This would result in the following introspection result:

model User {
id Int @default(autoincrement()) @id @map("_id")
@@map("42User")
}
model User {
id Int @default(autoincrement()) @id @map("_id")
@@map("24User")
}

In this case, you must manually change the name of one of the two generated User models because duplicate model names are not allowed in the Prisma schema.

Order of fields

Introspection lists model fields in the same order as the corresponding table columns in the database.

Order of attributes

Introspection adds attributes in the following order (this order is mirrored by prisma format):

  • Block level: @@id, @@unique, @@index, @@map
  • Field level : @id, @unique, @default, @updatedAt, @map, @relation

Relations

Prisma translates foreign keys that are defined on your database tables into relations.

One-to-one relations

Prisma adds a one-to-one relation to your data model when the foreign key on a table has a UNIQUE constraint, e.g.:

CREATE TABLE "User" (
id SERIAL PRIMARY KEY
);
CREATE TABLE "Profile" (
id SERIAL PRIMARY KEY,
"user" integer NOT NULL UNIQUE,
FOREIGN KEY ("user") REFERENCES "User"(id)
);

Prisma translates this into the following data model:

model User {
id Int @default(autoincrement()) @id
Profile Profile?
}
model Profile {
id Int @default(autoincrement()) @id
user Int @unique
User User @relation(fields: [user], references: [id])
}

One-to-many relations

By default, Prisma adds a one-to-many relation to your data model for a foreign key it finds in your database schema:

CREATE TABLE "User" (
id SERIAL PRIMARY KEY
);
CREATE TABLE "Post" (
id SERIAL PRIMARY KEY,
"author" integer NOT NULL,
FOREIGN KEY ("author") REFERENCES "User"(id)
);

These tables are transformed into the following models:

model User {
id Int @default(autoincrement()) @id
Post Post[]
}
model Post {
id Int @default(autoincrement()) @id
author Int
User User @relation(fields: [author], references: [id])
}

Many-to-many relations

Many-to-many relations are commonly represented as relation tables in relational databases.

Prisma supports two ways for defining many-to-many relations in the Prisma schema:

Implicit many-to-many relations are recognized if they adhere to Prisma's conventions for relation tables. Otherwise the relation table is rendered in the Prisma schema as a model (therefore making it an explicit many-to-many relation).

This topic is covered extensively on the docs page about Relations.

Disambiguating relations

Prisma generally omits the name argument on the @relation attribute if it's not needed. Consider the UserPost example from the previous section. The @relation attribute only has the references argument, name is omitted because it's not needed in this case:

model Post {
id Int @default(autoincrement()) @id
author Int
User User @relation(fields: [author], references: [id])
}

It would be needed if there were two foreign keys defined on the Post table:

CREATE TABLE "User" (
id SERIAL PRIMARY KEY
);
CREATE TABLE "Post" (
id SERIAL PRIMARY KEY,
"author" integer NOT NULL,
"favoritedBy" INTEGER,
FOREIGN KEY ("author") REFERENCES "User"(id),
FOREIGN KEY ("favoritedBy") REFERENCES "User"(id)
);

In this case, Prisma needs to disambiguate the relation using a dedicated relation name:

model Post {
id Int @default(autoincrement()) @id
author Int
favoritedBy Int?
User_Post_authorToUser User @relation("Post_authorToUser", fields: [author], references: [id])
User_Post_favoritedByToUser User? @relation("Post_favoritedByToUser", fields: [favoritedBy], references: [id])
}
model User {
id Int @default(autoincrement()) @id
Post_Post_authorToUser Post[] @relation("Post_authorToUser")
Post_Post_favoritedByToUser Post[] @relation("Post_favoritedByToUser")
}

Note that you can rename the Prisma-level relation field to anything you like so that it looks friendlier in the generated Prisma Client API.

Introspecting only a subset of your database schema

Introspecting only a subset of your database schema is not yet officially supported by Prisma.

However, you can achieve this by creating a new database user that only has access to the tables which you'd like to see represented in your Prisma schema, and then perform the introspection using that user. The introspection will then only include the tables the new user has access to.

If your goal is to exclude certain models from the Prisma Client generation, you can also manually delete the models that should be excluded from your Prisma schema. Note however that they will be added again when you run prisma introspect.

Edit this page on GitHub