Introspection

Introspect your database with Prisma ORM

For the purpose of this guide, we'll use a demo SQL schema with three tables:

CREATE TABLE `Post` (
  `id` int NOT NULL AUTO_INCREMENT,
  `createdAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` datetime(3) NOT NULL,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `content` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `published` tinyint(1) NOT NULL DEFAULT '0',
  `authorId` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `Post_authorId_idx` (`authorId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `Profile` (
  `id` int NOT NULL AUTO_INCREMENT,
  `bio` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `userId` int NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `Profile_userId_key` (`userId`),
  KEY `Profile_userId_idx` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `User` (
  `id` int NOT NULL AUTO_INCREMENT,
  `email` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `User_email_key` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Expand for a graphical overview of the tables

Post

Column name	Type	Primary key	Foreign key	Required	Default
`id`	`int`	✔️	No	✔️	autoincrementing
`createdAt`	`datetime(3)`	No	No	✔️	`now()`
`updatedAt`	`datetime(3)`	No	No	✔️
`title`	`varchar(255)`	No	No	✔️	-
`content`	`varchar(191)`	No	No	No	-
`published`	`tinyint(1)`	No	No	✔️	`false`
`authorId`	`int`	No	No	✔️	-

Profile

Column name	Type	Primary key	Foreign key	Required	Default
`id`	`int`	✔️	No	✔️	autoincrementing
`bio`	`varchar(191)`	No	No	No	-
`userId`	`int`	No	No	✔️	-

User

Column name	Type	Primary key	Foreign key	Required	Default
`id`	`int`	✔️	No	✔️	autoincrementing
`name`	`varchar(191)`	No	No	No	-
`email`	`varchar(191)`	No	No	✔️	-

As a next step, you will introspect your database. The result of the introspection will be a data model inside your Prisma schema.

Run the following command to introspect your database:

npx prisma db pull

This command reads the DATABASE_URL environment variable that's defined in .env and connects to your database. Once the connection is established, it introspects the database (i.e. it reads the database schema). It then translates the database schema from SQL into a Prisma data model.

After the introspection is complete, your Prisma schema is updated:

Introspect your database

The data model now looks similar to this:

prisma/schema.prisma

model Post {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime
  title     String   @db.VarChar(255)
  content   String?
  published Boolean  @default(false)
  authorId  Int

  @@index([authorId])
}

model Profile {
  id     Int     @id @default(autoincrement())
  bio    String?
  userId Int     @unique

  @@index([userId])
}

model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String?
}

info

Refer to the Prisma schema reference for detailed information about the schema definition.

Prisma's data model is a declarative representation of your database schema and serves as the foundation for the generated Prisma Client library. Your Prisma Client instance will expose queries that are tailored to these models.

You will then need to add in any missing relations between your data using relation fields:

prisma/schema.prisma

model Post {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime
  title     String   @db.VarChar(255)
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int

  @@index([authorId])
}

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

  @@index([userId])
}

model User {
  id      Int      @id @default(autoincrement())
  email   String   @unique
  name    String?
  posts   Post[]
  profile Profile?
}

After this, run introspection on your database for a second time:

npx prisma db pull

Prisma Migrate will now keep the manually added relation fields.

Because relation fields are virtual (i.e. they do not directly manifest in the database), you can manually rename them in your Prisma schema without touching the database.

In this example, the database schema follows the naming conventions for Prisma ORM models. This optimizes the ergonomics of the generated Prisma Client API.

Using custom model and field names

Sometimes though, you may want to make additional changes to the names of the columns and tables that are exposed in the Prisma Client API. A common example is to translate snake_case notation which is often used in database schemas into PascalCase and camelCase notations which feel more natural for JavaScript/TypeScript developers.

Assume you obtained the following model from introspection that's based on snake_case notation:

model my_user {
  user_id    Int     @id @default(autoincrement())
  first_name String?
  last_name  String  @unique
}

If you generated a Prisma Client API for this model, it would pick up the snake_case notation in its API:

const user = await prisma.my_user.create({
  data: {
    first_name: 'Alice',
    last_name: 'Smith',
  },
})

If you don't want to use the table and column names from your database in your Prisma Client API, you can configure them with @map and @@map:

model MyUser {
  userId    Int     @id @default(autoincrement()) @map("user_id")
  firstName String? @map("first_name")
  lastName  String  @unique @map("last_name")

  @@map("my_user")
}

With this approach, you can name your model and its fields whatever you like and use the @map (for field names) and @@map (for models names) to point to the underlying tables and columns. Your Prisma Client API now looks as follows:

const user = await prisma.myUser.create({
  data: {
    firstName: 'Alice',
    lastName: 'Smith',
  },
})

Learn more about this on the Configuring your Prisma Client API page.

Introspect your database with Prisma ORM​

Introspect your database with Prisma ORM