Introspection > Prisma Schema, mapping level

[matthewmueller]

This is a brain dump on how I think we should improve this workflow and include capabilities. This will be slightly lengthy, but I'm opting for a complete workflow rather than showing slices.

Given the following blog schema:

SQL Schema for Postgres

```sql create extension if not exists citext; create table if not exists blogs ( id serial primary key not null, website text not null unique, created_at timestamp not null default now() ); create table if not exists users ( id serial primary key not null, email citext not null unique, location point not null, first_name text not null, last_name text not null, created_at timestamp not null default now() ); create table if not exists posts ( id serial primary key not null, blog_id int references blogs (id) on delete cascade on update cascade, author_id int references users (id) on delete cascade on update cascade, title text not null, created_at timestamp not null default now() ); create table if not exists comments ( id serial primary key not null, post_id int references posts (id) on delete cascade on update cascade, comment text not null, created_at timestamp not null default now() ); ```

A couple things to note about the SQL schema:

• This is a common SQL schema format (plural tables, snake_case, lowercase)
• Use of postgres extension citext for users (email)
• Use of point type for users (location)

If I run prisma2 init and go through the init flow, I end up with a Prisma Schema like this:

Our current prisma schema after introspection

```groovy generator photon { provider = "photonjs" } datasource db { provider = "postgresql" url = "postgresql://m@localhost:5432/prisma-blog?schema=public" } model Blog { id Int @id createdAt DateTime @map("created_at") posts Post[] website String @unique @@map("blogs") } model Comment { id Int @id comment String createdAt DateTime @map("created_at") post Post? @map("post_id") @@map("comments") } model Migrate { version Int @id @@map("migrate") } model Post { id Int @id author User? @map("author_id") blog Blog? @map("blog_id") comments Comment[] createdAt DateTime @map("created_at") title String @@map("posts") } model User { id Int @id createdAt DateTime @map("created_at") firstName String @map("first_name") lastName String @map("last_name") posts Post[] @@map("users") } ```

1st suggestion: No mapping.

When we introspect we print out as we see it. I think this will look cleaner. This will be the default, but it can be reconfigured.

Example Prisma Schema without Mapping (disregard syntax highlighting)

```groovy generator photon { provider = "photonjs" } datasource db { provider = "postgresql" url = "postgresql://m@localhost:5432/prisma-blog?schema=public" } model blogs { id Int @id created_at DateTime posts posts[] website String @unique } model comments { id Int @id comment String created_at DateTime post_id posts? } model migrate { version Int @id } model posts { id Int @id author_id users? blog_id blogs? comments comments[] created_at DateTime title String } model users { id Int @id created_at DateTime first_name String last_name String posts posts[] } ```

2nd Suggestion retain all information from introspection

We've currently lost information. Right now our introspection flow doesn't print out users (email) or users (point). We have 2 options to retain this information. I prefer option 2.

1. Add type mapping as attributes

```groovy generator photon { provider = "photonjs" } datasource db { provider = "postgresql" url = "postgresql://m@localhost:5432/prisma-blog?schema=public" } model Blog { id Int @id createdAt DateTime @map("created_at") posts Post[] website String @unique @@map("blogs") } model Comment { id Int @id comment String createdAt DateTime @map("created_at") post Post? @map("post_id") @@map("comments") } model Migrate { version Int @id @@map("migrate") } model Post { id Int @id author User? @map("author_id") blog Blog? @map("blog_id") comments Comment[] createdAt DateTime @map("created_at") title String @@map("posts") } model User { id Int @id email String @dbType("citext") location String @dbType("point") createdAt DateTime @map("created_at") firstName String @map("first_name") lastName String @map("last_name") posts Post[] @@map("users") } ```

2. Add custom types

```groovy generator photon { provider = "photonjs" } datasource pg { provider = "postgresql" url = "postgresql://m@localhost:5432/prisma-blog?schema=public" } model Blog { id Int @id createdAt DateTime @map("created_at") posts Post[] website String @unique @@map("blogs") } model Comment { id Int @id comment String createdAt DateTime @map("created_at") post Post? @map("post_id") @@map("comments") } model Migrate { version Int @id @@map("migrate") } model Post { id Int @id author User? @map("author_id") blog Blog? @map("blog_id") comments Comment[] createdAt DateTime @map("created_at") title String @@map("posts") } model User { id Int @id email pg.Citext location pg.Point createdAt DateTime @map("created_at") firstName String @map("first_name") lastName String @map("last_name") posts Post[] @@map("users") } ```

Putting everything together, as we add capabilities, this is how I'd like to see our Prisma schema after introspecting a non-trivial Postgres database:

My proposed Prisma Schema after introspection

```groovy generator photon { provider = "photonjs" } datasource pg { provider = "postgresql" url = "postgresql://m@localhost:5432/prisma-blog?schema=public" } model blogs { id pg.Int @id created_at pg.Timestamp posts posts[] website pg.Text @unique } model comments { id pg.Int @id comment pg.Text created_at pg.Timestamp post_id posts? } model migrate { version pg.Int @id } model posts { id pg.Int @id author_id users? blog_id blogs? comments comments[] created_at pg.Timestamp title pg.Text } model users { id pg.Int @id created_at pg.Timestamp email pg.Citext location pg.Point first_name pg.Text last_name pg.Text posts posts[] } ```

Some things I don't necessarily like:

• The excessive use of pg., but I prefer this over losing information. A reasonable middleground is to assign our core types to a single datatype of the datasource and we use them there (e.g. Text maps to String). We could also stick pg. on the table to denote this table is fully backed in Postgres (e.g. pg.users)
• I also don't love that models aren't capitalized and singular, but I think we can provide this option during introspection, and I think it beats @@map() everywhere.

[matthewmueller]

cc/ @schickling @sorenbs