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 ORM 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.
Here's an overview of its main functions on SQL databases:
- Map tables in the database to Prisma models
- Map columns in the database to the fields of Prisma models
- Map indexes in the database to indexes in the Prisma schema
- Map database constraints to attributes or type modifiers in the Prisma schema
On MongoDB, the main functions are the following:
- Map collections in the database to Prisma models. Because a collection in MongoDB doesn't have a predefined structure, Prisma ORM samples the documents in the collection and derives the model structure accordingly (i.e. it maps the fields of the document to the fields of the Prisma model). If embedded types are detected in a collection, these will be mapped to composite types in the Prisma schema.
- Map indexes in the database to indexes in the Prisma schema, if the collection contains at least one document contains a field included in the index
You can learn more about how Prisma ORM 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 db pull
command
You can introspect your database using the prisma db pull
command of the Prisma CLI. Note that using this command requires your connection URL to be set in your Prisma schema datasource
.
Here's a high-level overview of the steps that prisma db pull
performs internally:
- Read the connection URL from the
datasource
configuration in the Prisma schema - Open a connection to the database
- Introspect database schema (i.e. read tables, columns and other structures ...)
- Transform database schema into Prisma schema data model
- Write data model into Prisma schema or update existing schema
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:
- Change the database schema (e.g. using plain SQL)
- Run
prisma db pull
to update the Prisma schema - Run
prisma generate
to update Prisma Client - 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.
Rules and conventions
Prisma ORM employs a number of conventions for translating a database schema into a data model in the Prisma schema:
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_]*
Sanitization of invalid characters
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 ORM, introspection adds the @map
and @@map
attributes so that these names adhere to Prisma ORM's naming conventions:
model User {
id Int @id @default(autoincrement()) @map("_id")
name String? @map("_name")
two_two Int? @map("two$two")
@@map("42User")
}
Duplicate Identifiers after Sanitization
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 @id @default(autoincrement()) @map("_id")
@@map("42User")
}
model User {
id Int @id @default(autoincrement()) @map("_id")
@@map("24User")
}
Trying to generate your Prisma Client with prisma generate
you would get the following error:
npx prisma generate
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 ORM translates foreign keys that are defined on your database tables into relations.
One-to-one relations
Prisma ORM 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 ORM translates this into the following data model:
model User {
id Int @id @default(autoincrement())
Profile Profile?
}
model Profile {
id Int @id @default(autoincrement())
user Int @unique
User User @relation(fields: [user], references: [id])
}
One-to-many relations
By default, Prisma ORM 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 @id @default(autoincrement())
Post Post[]
}
model Post {
id Int @id @default(autoincrement())
author Int
User User @relation(fields: [author], references: [id])
}