Introspection
Introspect your database with Prisma
For the purpose of this guide, we'll use a demo SQL schema with three tables:
CREATE TABLE "public"."User" (id SERIAL PRIMARY KEY NOT NULL,name VARCHAR(255),email VARCHAR(255) UNIQUE NOT NULL);CREATE TABLE "public"."Post" (id SERIAL PRIMARY KEY NOT NULL,title VARCHAR(255) NOT NULL,"createdAt" TIMESTAMP NOT NULL DEFAULT now(),content TEXT,published BOOLEAN NOT NULL DEFAULT false,"authorId" INTEGER NOT NULL,FOREIGN KEY ("authorId") REFERENCES "public"."User"(id));CREATE TABLE "public"."Profile" (id SERIAL PRIMARY KEY NOT NULL,bio TEXT,"userId" INTEGER UNIQUE NOT NULL,FOREIGN KEY ("userId") REFERENCES "public"."User"(id));
Note: Some fields are written in double-quotes to ensure PostgreSQL uses proper casing. If no double-quotes were used, PostgreSQL would just read everything as lowercase characters.
User
Column name | Type | Primary key | Foreign key | Required | Default |
---|---|---|---|---|---|
id | SERIAL | ✔️ | No | ✔️ | autoincrementing |
name | VARCHAR(255) | No | No | No | - |
email | VARCHAR(255) | No | No | ✔️ | - |
Post
Column name | Type | Primary key | Foreign key | Required | Default |
---|---|---|---|---|---|
id | SERIAL | ✔️ | No | ✔️ | autoincrementing |
createdAt | TIMESTAMP | No | No | ✔️ | now() |
title | VARCHAR(255) | No | No | ✔️ | - |
content | TEXT | No | No | No | - |
published | BOOLEAN | No | No | ✔️ | false |
authorId | INTEGER | No | ✔️ | ✔️ | - |
Profile
Column name | Type | Primary key | Foreign key | Required | Default |
---|---|---|---|---|---|
id | SERIAL | ✔️ | No | ✔️ | autoincrementing |
bio | TEXT | No | No | No | - |
userId | INTEGER | No | ✔️ | ✔️ | - |
For the purpose of this guide, we'll use a demo SQL schema with three tables:
CREATE TABLE User (id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,name VARCHAR(255),email VARCHAR(255) UNIQUE NOT NULL);CREATE TABLE Post (id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,title VARCHAR(255) NOT NULL,createdAt TIMESTAMP NOT NULL DEFAULT now(),content TEXT,published BOOLEAN NOT NULL DEFAULT false,authorId INTEGER NOT NULL,FOREIGN KEY (authorId) REFERENCES User(id));CREATE TABLE Profile (id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,bio TEXT,userId INTEGER UNIQUE NOT NULL,FOREIGN KEY (userId) REFERENCES User(id));
User
Column name | Type | Primary key | Foreign key | Required | Default |
---|---|---|---|---|---|
id | INTEGER | ✔️ | No | ✔️ | autoincrementing |
name | VARCHAR(255) | No | No | No | - |
email | VARCHAR(255) | No | No | ✔️ | - |
Post
Column name | Type | Primary key | Foreign key | Required | Default |
---|---|---|---|---|---|
id | INTEGER | ✔️ | No | ✔️ | autoincrementing |
createdAt | DATETIME(3) | No | No | ✔️ | now() |
title | VARCHAR(255) | No | No | ✔️ | - |
content | TEXT | No | No | No | - |
published | BOOLEAN | No | No | ✔️ | false |
authorId | INTEGER | No | ✔️ | ✔️ | false |
Profile
Column name | Type | Primary key | Foreign key | Required | Default |
---|---|---|---|---|---|
id | INTEGER | ✔️ | No | ✔️ | autoincrementing |
bio | TEXT | No | No | No | - |
userId | INTEGER | No | ✔️ | ✔️ | - |
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;
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 | ✔️ | - |
For the purpose of this guide, we'll use a demo SQL schema with three tables:
CREATE TABLE [dbo].[Post] ([id] INT NOT NULL IDENTITY(1,1),[createdAt] DATETIME2 NOT NULL CONSTRAINT [Post_createdAt_df] DEFAULT CURRENT_TIMESTAMP,[updatedAt] DATETIME2 NOT NULL,[title] VARCHAR(255) NOT NULL,[content] NVARCHAR(1000),[published] BIT NOT NULL CONSTRAINT [Post_published_df] DEFAULT 0,[authorId] INT NOT NULL,CONSTRAINT [Post_pkey] PRIMARY KEY ([id]));CREATE TABLE [dbo].[Profile] ([id] INT NOT NULL IDENTITY(1,1),[bio] NVARCHAR(1000),[userId] INT NOT NULL,CONSTRAINT [Profile_pkey] PRIMARY KEY ([id]),CONSTRAINT [Profile_userId_key] UNIQUE ([userId]));CREATE TABLE [dbo].[User] ([id] INT NOT NULL IDENTITY(1,1),[email] NVARCHAR(1000) NOT NULL,[name] NVARCHAR(1000),CONSTRAINT [User_pkey] PRIMARY KEY ([id]),CONSTRAINT [User_email_key] UNIQUE ([email]));ALTER TABLE [dbo].[Post] ADD CONSTRAINT [Post_authorId_fkey] FOREIGN KEY ([authorId]) REFERENCES [dbo].[User]([id]) ON DELETE NO ACTION ON UPDATE CASCADE;ALTER TABLE [dbo].[Profile] ADD CONSTRAINT [Profile_userId_fkey] FOREIGN KEY ([userId]) REFERENCES [dbo].[User]([id]) ON DELETE NO ACTION ON UPDATE CASCADE;
User
Column name | Type | Primary key | Foreign key | Required | Default |
---|---|---|---|---|---|
id | INT | ✔️ | No | ✔️ | autoincrementing |
name | NVARCHAR(1000) | No | No | No | - |
email | NVARCHAR(1000) | No | No | ✔️ | - |
Post
Column name | Type | Primary key | Foreign key | Required | Default |
---|---|---|---|---|---|
id | INT | ✔️ | No | ✔️ | autoincrementing |
createdAt | DATETIME2 | No | No | ✔️ | now() |
updatedAt | DATETIME2 | No | No | ✔️ | |
title | VARCHAR(255) | No | No | ✔️ | - |
content | NVARCHAR(1000) | No | No | No | - |
published | BIT | No | No | ✔️ | false |
authorId | INT | No | ✔️ | ✔️ | - |
Profile
Column name | Type | Primary key | Foreign key | Required | Default |
---|---|---|---|---|---|
id | INT | ✔️ | No | ✔️ | autoincrementing |
bio | NVARCHAR(1000) | No | No | No | - |
userId | INT | No | ✔️ | ✔️ | - |
For the purpose of this guide, we'll use a demo SQL schema with three tables:
CREATE TABLE "User" (id INT8 PRIMARY KEY DEFAULT unique_rowid(),name STRING(255),email STRING(255) UNIQUE NOT NULL);CREATE TABLE "Post" (id INT8 PRIMARY KEY DEFAULT unique_rowid(),title STRING(255) UNIQUE NOT NULL,"createdAt" TIMESTAMP NOT NULL DEFAULT now(),content STRING,published BOOLEAN NOT NULL DEFAULT false,"authorId" INT8 NOT NULL,FOREIGN KEY ("authorId") REFERENCES "User"(id));CREATE TABLE "Profile" (id INT8 PRIMARY KEY DEFAULT unique_rowid(),bio STRING,"userId" INT8 UNIQUE NOT NULL,FOREIGN KEY ("userId") REFERENCES "User"(id));
Note: Some fields are written in double quotes to ensure CockroachDB uses proper casing. If no double-quotes were used, CockroachDB would just read everything as lowercase characters.
User
Column name | Type | Primary key | Foreign key | Required | Default |
---|---|---|---|---|---|
id | INT8 | ✔️ | No | ✔️ | autoincrementing |
name | STRING(255) | No | No | No | - |
email | STRING(255) | No | No | ✔️ | - |
Post
Column name | Type | Primary key | Foreign key | Required | Default |
---|---|---|---|---|---|
id | INT8 | ✔️ | No | ✔️ | autoincrementing |
createdAt | TIMESTAMP | No | No | ✔️ | now() |
title | STRING(255) | No | No | ✔️ | - |
content | STRING | No | No | No | - |
published | BOOLEAN | No | No | ✔️ | false |
authorId | INT8 | No | ✔️ | ✔️ | - |
Profile
Column name | Type | Primary key | Foreign key | Required | Default |
---|---|---|---|---|---|
id | INT8 | ✔️ | No | ✔️ | autoincrementing |
bio | STRING | No | No | No | - |
userId | INT8 | 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:
$
This commands 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 file was updated:
The data model now looks similar to this (note that the fields on the models have been reordered for better readability):
prisma/schema.prisma
1model Post {2 id Int @id @default(autoincrement())3 title String @db.VarChar(255)4 createdAt DateTime @default(now()) @db.Timestamp(6)5 content String?6 published Boolean @default(false)7 authorId Int8 User User @relation(fields: [authorId], references: [id], onDelete: NoAction, onUpdate: NoAction)9}1011model Profile {12 id Int @id @default(autoincrement())13 bio String?14 userId Int @unique15 User User @relation(fields: [userId], references: [id], onDelete: NoAction, onUpdate: NoAction)16}1718model User {19 id Int @id @default(autoincrement())20 name String? @db.VarChar(255)21 email String @unique @db.VarChar(255)22 Post Post[]23 Profile Profile?24}
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.
Right now, there's a few minor "issues" with the data model:
- The
User
relation field is uppercased and therefore doesn't adhere to Prisma's naming conventions . To express more "semantics", it would also be nice if this field was calledauthor
to describe the relationship betweenUser
andPost
better. - The
Post
andProfile
relation fields onUser
as well as theUser
relation field onProfile
are all uppercased. To adhere to Prisma's naming conventions , both fields should be lowercased topost
,profile
anduser
. - Even after lowercasing, the
post
field onUser
is still slightly misnamed. That's because it actually refers to a list of posts – a better name therefore would be the plural form:posts
.
These changes are relevant for the generated Prisma Client API where using lowercased relation fields author
, posts
, profile
and user
will feel more natural and idiomatic to JavaScript/TypeScript developers. You can therefore configure your Prisma Client API.
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:
prisma/schema.prisma
1model Post {2 id Int @id @default(autoincrement())3 title String @db.VarChar(255)4 createdAt DateTime @default(now()) @db.Timestamp(6)5 content String?6 published Boolean @default(false)7 authorId Int✎ author User @relation(fields: [authorId], references: [id], onDelete: NoAction, onUpdate: NoAction)9}1011model Profile {12 id Int @id @default(autoincrement())13 bio String?14 userId Int @unique✎ user User @relation(fields: [userId], references: [id], onDelete: NoAction, onUpdate: NoAction)16}1718model User {19 id Int @id @default(autoincrement())20 name String? @db.VarChar(255)21 email String @unique @db.VarChar(255)✎ posts Post[]✎ profile Profile?24}
In this example, the database schema did follow the naming conventions for Prisma models (only the virtual relation fields that were generated from introspection did not adhere to them and needed adjustment). This optimizes the ergonomics of the generated Prisma Client API.
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.
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:
$
This commands 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 file was updated:
The data model now looks similar to this (note that the fields on the models have been reordered for better readability):
prisma/schema.prisma
1model Post {2 id Int @id @default(autoincrement())3 title String @db.VarChar(255)4 createdAt DateTime @default(now()) @db.Timestamp(0)5 content String? @db.Text6 published Boolean @default(false)7 authorId Int8 User User @relation(fields: [authorId], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "Post_ibfk_1")910 @@index([authorId], map: "authorId")11}1213model Profile {14 id Int @id @default(autoincrement())15 bio String? @db.Text16 userId Int @unique(map: "userId")17 User User @relation(fields: [userId], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "Profile_ibfk_1")18}1920model User {21 id Int @id @default(autoincrement())22 name String? @db.VarChar(255)23 email String @unique(map: "email") @db.VarChar(255)24 Post Post[]25 Profile Profile?26}
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.
Right now, there's a few minor "issues" with the data model:
- The
User
relation field is uppercased and therefore doesn't adhere to Prisma's naming conventions . To express more "semantics", it would also be nice if this field was calledauthor
to describe the relationship betweenUser
andPost
better. - The
Post
andProfile
relation fields onUser
as well as theUser
relation field onProfile
are all uppercased. To adhere to Prisma's naming conventions , both fields should be lowercased topost
,profile
anduser
. - Even after lowercasing, the
post
field onUser
is still slightly misnamed. That's because it actually refers to a list of posts – a better name therefore would be the plural form:posts
.
These changes are relevant for the generated Prisma Client API where using lowercased relation fields author
, posts
, profile
and user
will feel more natural and idiomatic to JavaScript/TypeScript developers. You can therefore configure your Prisma Client API.
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:
prisma/schema.prisma
1model Post {2 id Int @id @default(autoincrement())3 title String @db.VarChar(255)4 createdAt DateTime @default(now()) @db.Timestamp(0)5 content String? @db.Text6 published Boolean @default(false)7 authorId Int✎ author User @relation(fields: [authorId], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "Post_ibfk_1")910 @@index([authorId], map: "authorId")11}1213model Profile {14 id Int @id @default(autoincrement())15 bio String? @db.Text16 userId Int @unique(map: "userId")✎ user User @relation(fields: [userId], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "Profile_ibfk_1")18}1920model User {21 id Int @id @default(autoincrement())22 name String? @db.VarChar(255)23 email String @unique(map: "email") @db.VarChar(255)✎ posts Post[]✎ profile Profile?26}
In this example, the database schema did follow the naming conventions for Prisma models (only the virtual relation fields that were generated from introspection did not adhere to them and needed adjustment). This optimizes the ergonomics of the generated Prisma Client API.
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.
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:
$
This commands 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 file was updated:
The data model now looks similar to this (note that the fields on the models have been reordered for better readability):
prisma/schema.prisma
1model Post {2 id Int @id @default(autoincrement())3 title String @db.VarChar(255)4 createdAt DateTime @default(now()) @db.Timestamp(6)5 content String?6 published Boolean @default(false)7 authorId Int8 User User @relation(fields: [authorId], references: [id])9}1011model Profile {12 id Int @id @default(autoincrement())13 bio String?14 userId Int @unique15 User User @relation(fields: [userId], references: [id])16}1718model User {19 id Int @id @default(autoincrement())20 name String? @db.VarChar(255)21 email String @unique @db.VarChar(255)22 Post Post[]23 Profile Profile?24}
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.
Right now, there's a few minor "issues" with the data model:
- The
User
relation field is uppercased and therefore doesn't adhere to Prisma's naming conventions . To express more "semantics", it would also be nice if this field was calledauthor
to describe the relationship betweenUser
andPost
better. - The
Post
andProfile
relation fields onUser
as well as theUser
relation field onProfile
are all uppercased. To adhere to Prisma's naming conventions , both fields should be lowercased topost
,profile
anduser
. - Even after lowercasing, the
post
field onUser
is still slightly misnamed. That's because it actually refers to a list of posts – a better name therefore would be the plural form:posts
.
These changes are relevant for the generated Prisma Client API where using lowercased relation fields author
, posts
, profile
and user
will feel more natural and idiomatic to JavaScript/TypeScript developers. You can therefore configure your Prisma Client API.
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:
prisma/schema.prisma
1model Post {2 id Int @id @default(autoincrement())3 title String @db.VarChar(255)4 createdAt DateTime @default(now()) @db.Timestamp(6)5 content String?6 published Boolean @default(false)✎ author User @relation(fields: [authorId], references: [id])8 authorId Int9}1011model Profile {12 id Int @id @default(autoincrement())13 bio String?✎ user User @relation(fields: [userId], references: [id])15 userId Int @unique16}1718model User {19 id Int @id @default(autoincrement())20 email String @unique @db.VarChar(255)21 name String? @db.VarChar(255)✎ posts Post[]✎ profile Profile?24}
In this example, the database schema did follow the naming conventions for Prisma models (only the virtual relation fields that were generated from introspection did not adhere to them and needed adjustment). This optimizes the ergonomics of the generated Prisma Client API.
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.
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:
$
This commands 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 file was updated:
The data model now looks similar to this:
prisma/schema.prisma
1model Post {2 id Int @id @default(autoincrement())3 createdAt DateTime @default(now())4 updatedAt DateTime5 title String @db.VarChar(255)6 content String?7 published Boolean @default(false)8 authorId Int910 @@index([authorId])11}1213model Profile {14 id Int @id @default(autoincrement())15 bio String?16 userId Int @unique1718 @@index([userId])19}2021model User {22 id Int @id @default(autoincrement())23 email String @unique24 name String?25}
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
1model Post {2 id Int @id @default(autoincrement())3 createdAt DateTime @default(now())4 updatedAt DateTime5 title String @db.VarChar(255)6 content String?7 published Boolean @default(false)+ author User @relation(fields: [authorId], references: [id])9 authorId Int1011 @@index([authorId])12}1314model Profile {15 id Int @id @default(autoincrement())16 bio String?+ user User @relation(fields: [userId], references: [id])18 userId Int @unique1920 @@index([userId])21}2223model User {24 id Int @id @default(autoincrement())25 email String @unique26 name String?+ posts Post[]+ profile Profile?29}
After this, run introspection on your database for a second time:
$
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 models. This optimizes the ergonomics of the generated Prisma Client API.
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.
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:
$
This commands reads the environment variable used to define the url
in your schema.prisma
, DATABASE_URL
, that in our case is set 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 file was updated:
The data model now looks similar to this:
prisma/schema.prisma
1model Post {2 id BigInt @id @default(autoincrement())3 title String @unique @db.String(255)4 createdAt DateTime @default(now()) @db.Timestamp(6)5 content String?6 published Boolean @default(false)7 authorId BigInt8 User User @relation(fields: [authorId], references: [id], onDelete: NoAction, onUpdate: NoAction)9}1011model Profile {12 id BigInt @id @default(autoincrement())13 bio String?14 userId BigInt @unique15 User User @relation(fields: [userId], references: [id], onDelete: NoAction, onUpdate: NoAction)16}1718model User {19 id BigInt @id @default(autoincrement())20 name String? @db.String(255)21 email String @unique @db.String(255)22 Post Post[]23 Profile Profile?24}
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.
Right now, there's a few minor "issues" with the data model:
- The
User
relation field is uppercased and therefore doesn't adhere to Prisma's naming conventions . To express more "semantics", it would also be nice if this field was calledauthor
to describe the relationship betweenUser
andPost
better. - The
Post
andProfile
relation fields onUser
as well as theUser
relation field onProfile
are all uppercased. To adhere to Prisma's naming conventions , both fields should be lowercased topost
,profile
anduser
. - Even after lowercasing, the
post
field onUser
is still slightly misnamed. That's because it actually refers to a list of posts – a better name therefore would be the plural form:posts
.
These changes are relevant for the generated Prisma Client API where using lowercased relation fields author
, posts
, profile
and user
will feel more natural and idiomatic to JavaScript/TypeScript developers. You can therefore configure your Prisma Client API.
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:
prisma/schema.prisma
1model Post {2 id BigInt @id @default(autoincrement())3 title String @unique @db.String(255)4 createdAt DateTime @default(now()) @db.Timestamp(6)5 content String?6 published Boolean @default(false)7 authorId BigInt✎ author User @relation(fields: [authorId], references: [id], onDelete: NoAction, onUpdate: NoAction)9}1011model Profile {12 id BigInt @id @default(autoincrement())13 bio String?14 userId BigInt @unique✎ user User @relation(fields: [userId], references: [id], onDelete: NoAction, onUpdate: NoAction)16}1718model User {19 id BigInt @id @default(autoincrement())20 name String? @db.String(255)21 email String @unique @db.String(255)✎ posts Post[]✎ profile Profile?24}
In this example, the database schema did follow the naming conventions for Prisma models (only the virtual relation fields that were generated from introspection did not adhere to them and needed adjustment). This optimizes the ergonomics of the generated Prisma Client API.
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(sequence())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(sequence()) @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.