title

dbSwitcher

techMetaTitles

techMetaDescriptions

SQL views

postgres

mysql

name	value
mysql	Create SQL views with Prisma and MySQL

name	value
postgres	Create SQL views with Prisma and PostgreSQL)

name	value
mysql	Learn how to set up MySQL views with Prisma and your database by following the step-by-step instructions in this practical guide.

name	value
postgres	Find out how to configure PostgreSQL views with Prisma and your database by following the step-by-step instructions in this practical guide.

This page explains how to create a view in your PostgreSQL database.

This page explains how to create a view in your MySQL database.

In this guide, you will:

Create two tables where one references the other via a foreign key.
Create a view named Draft.
Introspect your database to reflect the foreign key relation between the two tables in the Prisma schema.
Manually update the Prisma schema to include the view as a model.
Generate Prisma Client and write a simple Node.js script to read data from the view.

The workaround described in this guide is by no means a best practice, it instead fills in some gaps for missing functionality in Prisma. It will not work with Prisma Migrate or introspection.

There is an issue currently open which aims to add support for database views in Prisma.

Subscribe to the issue for updates on progress and timelines.

Prerequisites

In order to follow this guide, you need:

a PostgreSQL database server running
the createdb command line utility
the psql command line client for PostgreSQL
the Node.js runtime for JavaScript installed on your machine

a MySQL database server running
the mysql command line client for MySQL
Node.js installed on your machine

Limitations

Be aware of the following limitations when using views with Prisma:

You must manually add each view as a model to the Prisma schema right now. Introspection does not add views to the schema currently.
Views must include a unique column - such as an ID.
The generated Prisma Client will include queries such create, delete, and update, even though you cannot perform these queries on a view.
Models created for views will be deleted from your schema the next time you run an introspection. This is because they do not correspond to the tables in the database.
Prisma Migrate will treat these views as tables and try to create them. If you have view models, you cannot use Prisma Migrate with them.

1. Create a new database and project directory

To create a new database:

Create a project directory where you can put the files you'll create throughout this guide:
```
mkdir sql-views-demo
cd sql-views-demo
```
Next, make sure that your database server is running. Then execute the following command in your terminal to create a new database called SqlViews:

<SwitchTech technologies={['*', 'postgres']}>
```
createdb SqlViews
```
<SwitchTech technologies={['*', 'mysql']}>
```
mysql -e 'CREATE DATABASE `SqlViews`;'
```
Validate that the database was created by running the following command which lists all tables (relations) in your database (right now there are none):

<SwitchTech technologies={['*', 'postgres']}>
```
psql -d SqlViews -c "\dt"
```
<SwitchTech technologies={['*', 'mysql']}>
```
mysql -e 'SHOW TABLES in `SqlViews`;'
```

2. Create two tables with a foreign key

In this section, you'll create two tables where one references the other via a foreign key in the SqlViews database.

Create a new file named sql-views-tables.sql and add the following code to it:

CREATE TABLE `SqlViews`.`User` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(256),
  `email` VARCHAR(256) UNIQUE
);

CREATE TABLE `SqlViews`.`Post` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(256),
  `content` VARCHAR(256),
  `published` BOOLEAN,
  `authorId` INT,
  CONSTRAINT `author` FOREIGN KEY (`authorId`) REFERENCES `User`(`id`)
);

CREATE TABLE "public"."User" (
  id SERIAL PRIMARY KEY,
  email TEXT UNIQUE,
  name TEXT
);

CREATE TABLE "public"."Post" (
  id SERIAL PRIMARY KEY,
  title TEXT,
  content TEXT,
  published BOOLEAN,
  "authorId" INTEGER,
  CONSTRAINT author FOREIGN KEY ("authorId") REFERENCES "public"."User" (id)
);

Run the SQL statement against your database to create the two tables:

<SwitchTech technologies={['*', 'postgres']}>
```
psql SqlViews <\ sql-views-tables.sql
```
<SwitchTech technologies={['*', 'mysql']}>
```
mysql <\ sql-views-tables.sql
```
Run the following command to validate that the tables were created:

<SwitchTech technologies={['*', 'postgres']}>
```
psql -d SqlViews -c "\dt"
```
<SwitchTech technologies={['*', 'mysql']}>
```
mysql -e 'SHOW TABLES in `SqlViews`;'
```

You just created two tables named User and Post in the database. The Post table references the User table via the foreign key defined on the authorId column.

3. Create a view named `Draft`

In this section you will create a view named Draft. The Draft view represents a query that returns the post title and author email of all posts that have not been published. To create a view:

Create a new file named sql-views-draft.sql and add the following code to it:

CREATE VIEW "Draft" AS
    SELECT "published", "title", "email", "Post"."id"
    FROM "Post", "User"
    WHERE "published" = false AND "Post"."authorId" = "User"."id";

CREATE VIEW Draft AS
    SELECT published, title, email, Post.id
    FROM Post, User
    WHERE published = false AND Post.authorId = User.id;

Run the SQL statement against your database to create the view:

<SwitchTech technologies={['*', 'postgres']}>
```
psql SqlViews <\ sql-views-draft.sql
```
<SwitchTech technologies={['*', 'mysql']}>
```
mysql <\ sql-views-draft.sql
```

Run the following command to validate that the view was created:

psql -d SqlViews -c "\dv"

mysql -e SHOW FULL TABLES in `SqlViews` WHERE TABLE_TYPE LIKE 'VIEW';'

You should see the following list of views:

       List of relations
 Schema |  Name  | Type |  Owner
 --------+--------+------+----------
 public | Draft  | view | postgres

 +----------------+------------+
 | Tables_in_mydb | Table_type |
 +----------------+------------+
 | Drafts         | VIEW       |
 +----------------+------------+

4. Introspect your database with Prisma

Deprecation warning
From Prisma 3.0.0 onwards, the prisma introspect command will be deprecated and replaced with the prisma db pull command.

In this section you'll introspect your database to generate the Prisma models for the tables that you created.

Note: You will manually add the Draft view to the Prisma schema in a later step.

Set up a new Node.js project and add the prisma CLI as a development dependency:
```
npm init -y
npm install prisma --save-dev
```

Create a new file named schema.prisma and add the following code to it:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

In order to introspect your database, you need to tell Prisma how to connect to it. You do so by configuring a datasource in your Prisma schema. Create a new file named .env and set your database connection URL as the DATABASE_URL environment variable:

<SwitchTech technologies={['*', 'postgres']}>
```
DATABASE_URL=postgresql://__USER__:__PASSWORD__@__HOST__:__PORT__/SqlViews
```
<SwitchTech technologies={['*', 'mysql']}>
```
DATABASE_URL=mysql://USER:PASSWORD@HOST:PORT/DATABASE
```
In the above code snippet, you need to replace the uppercase placeholders with your own connection details. For example, if your database is running locally it could look like this:

<SwitchTech technologies={['*', 'postgres']}>
```
DATABASE_URL=postgresql://janedoe:mypassword@localhost:5432/SqlViews
```
<SwitchTech technologies={['*', 'mysql']}>
```
DATABASE_URL=mysql://janedoe:mypassword@localhost:3306/mydb
```
The database connection URL is set via an environment variable. The Prisma CLI automatically supports the dotenv format which automatically picks up environment variables defined in a file named .env.

With both the schema.prisma and .env files in place, run Prisma's introspection with the following command:

npx prisma introspect

This command introspects your database and for each table adds a Prisma model to the Prisma schema:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model Post {
  authorId  Int?
  content   String?
  id        Int     @default(autoincrement()) @id
  published Boolean @default(false)
  title     String
  User      User?   @relation(fields: [authorId], references: [id])
}

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

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

model Post {
  authorId  Int?
  content   String?
  id        Int     @default(autoincrement()) @id
  published Boolean @default(false)
  title     String
  User      User?   @relation(fields: [authorId], references: [id])
}

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

5. Manually add the `Draft` view to the Prisma schema

You must manually add views to the Prisma schema.

Add a Draft model to the schema as shown:

Note: The name of your view is case sensitive - if you created a view named draft in the database, you must create a model named draft in the Prisma schema.

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

model Post {
  authorId  Int?
  content   String?
  id        Int     @default(autoincrement()) @id
  published Boolean @default(false)
  title     String
  User      User?   @relation(fields: [authorId], references: [id])
}

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

model Draft {
  title     String
  id        Int     @unique
  email     String
  published Boolean
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model Post {
  authorId  Int?
  content   String?
  id        Int     @default(autoincrement()) @id
  published Boolean @default(false)
  title     String
  User      User?   @relation(fields: [authorId], references: [id])
}

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

model Draft {
  title     String
  id        Int     @unique
  email     String
  published Boolean
}

6. Generate Prisma Client

In this section, you will generate Prisma Client.

Add a generator block to your Prisma schema (typically added right below the datasource block):
```
generator client {
  provider = "prisma-client-js"
}
```
Run the following command to install and generate Prisma Client in your project:
```
npx prisma generate
```

Now you can use Prisma Client to send database queries in Node.js.

9. Validate the `Draft` view in a Node.js script

In the following section, you will use the drafts model property to return Post records that have not yet been published. To use the drafts model property:

Create a new file named index.js and add the following code to it:

const { PrismaClient } = require('@prisma/client')

const prisma = new PrismaClient({})

async function main() {
  const sarahPosts = await prisma.user.create({
    data: {
      name: 'Sarah',
      email: 'sarah@prisma.io',
      Post: {
        create: [
          { title: 'My first post', published: false },
          { title: 'All about databases', published: true },
          { title: 'Prisma Day 2020', published: false },
        ],
      },
    },
  })

  const emilyPosts = await prisma.user.create({
    data: {
      name: 'Emily',
      email: 'emily@prisma.io',
      Post: {
        create: [
          { title: 'My first post', published: false },
          { title: 'All about databases', published: true },
          { title: 'Prisma Day 2020', published: false },
        ],
      },
    },
  })

  const drafts = await prisma.draft.findMany({})

  console.log(drafts)

  const filteredDrafts = await prisma.draft.findMany({
    where: {
      email: 'sarah@prisma.io',
    },
  })

  console.log(filteredDrafts)
}

main()
  .catch((e) => {
    throw e
  })
  .finally(async () => {
    await prisma.$disconnect()
  })

This example:

Creates two User records with three Post records each
Returns all Draft records from the view
Returns all Draft records from the view where the author's email is emily@prisma.io

Run the code with the following command:

node index.js

The following output indicates that the view works as expected - the first query returns all drafts (Post records where published is false), and the second query returns drafts by emily@prisma.io only):

/* ALL DRAFTS */
;[
  {
    title: 'My first post',
    id: 37,
    email: 'sarah@prisma.io',
    published: false,
  },
  {
    title: 'Prisma Day 2020',
    id: 39,
    email: 'sarah@prisma.io',
    published: false,
  },
  {
    title: 'My first post',
    id: 40,
    email: 'emily@prisma.io',
    published: false,
  },
  {
    title: 'Prisma Day 2020',
    id: 42,
    email: 'emily@prisma.io',
    published: false,
  },
][
  /* FILTERED DRAFTS */
  ({
    title: 'My first post',
    id: 37,
    email: 'sarah@prisma.io',
    published: false,
  },
  {
    title: 'Prisma Day 2020',
    id: 39,
    email: 'sarah@prisma.io',
    published: false,
  })
]

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

08-sql-views.mdx

08-sql-views.mdx

Prerequisites

Limitations

1. Create a new database and project directory

2. Create two tables with a foreign key

3. Create a view named `Draft`

4. Introspect your database with Prisma

5. Manually add the `Draft` view to the Prisma schema

6. Generate Prisma Client

9. Validate the `Draft` view in a Node.js script

Files

08-sql-views.mdx

Latest commit

History

08-sql-views.mdx

File metadata and controls

Prerequisites

Limitations

1. Create a new database and project directory

2. Create two tables with a foreign key

3. Create a view named Draft

4. Introspect your database with Prisma

5. Manually add the Draft view to the Prisma schema

6. Generate Prisma Client

9. Validate the Draft view in a Node.js script

3. Create a view named `Draft`

5. Manually add the `Draft` view to the Prisma schema

9. Validate the `Draft` view in a Node.js script