Overview

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.

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.

1. Create a new database and project directory

To create a new database:

  1. Create a project directory where you can put the files you'll create throughout this guide:

    $mkdir sql-views-demo
    $cd sql-views-demo
  2. Next, make sure that your database server is running. Then execute the following command in your terminal to create a new database called SqlViews:

$createdb SqlViews
1mysql -e 'CREATE DATABASE `SqlViews`;'
  1. Validate that the database was created by running the following command which lists all tables (relations) in your database (right now there are none):
$psql -d SqlViews -c "\dt"
1mysql -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.

  1. Create a new file called sql-views-tables.sql and add the following code to it:
1CREATE TABLE `SqlViews`.`User` (
2 `id` INT AUTO_INCREMENT PRIMARY KEY,
3 `name` VARCHAR(256)
4 `email` VARCHAR(256) UNIQUE
5);
6
7CREATE TABLE `SqlViews`.`Post` (
8 `id` INT AUTO_INCREMENT PRIMARY KEY,
9 `title` VARCHAR(256),
10 `content` VARCHAR(256),
11 `published` BOOLEAN
12 `authorId` INT,
13 CONSTRAINT `author` FOREIGN KEY (`authorId`) REFERENCES `User`(`id`)
14);
1CREATE TABLE "public"."User" (
2 id SERIAL PRIMARY KEY,
3 email TEXT UNIQUE
4 name TEXT
5);
6
7CREATE TABLE "public"."Post" (
8 id SERIAL PRIMARY KEY,
9 title TEXT,
10 content TEXT,
11 published BOOLEAN,
12 "authorId" INTEGER,
13 CONSTRAINT author FOREIGN KEY ("authorId") REFERENCES "public"."User" (id)
14);
  1. Run the SQL statement against your database to create the two tables:
$psql SqlViews <\ sql-views-tables.sql
$mysql <\ sql-views-tables.sql
  1. Run the following command to validate that the tables were created:
$psql -d SqlViews -c "\dt"
1mysql -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:

  1. Create a new file called sql-views-draft.sql and add the following code to it:
1CREATE VIEW "Draft" AS
2 SELECT "published", "title", "email", "Post"."id"
3 FROM "Post", "User"
4 WHERE "published" = false AND "Post"."authorId" = "User"."id";
1CREATE VIEW Drafts AS
2 SELECT published, title, email, Post.id
3 FROM Post, User
4 WHERE published = false AND Post.authorId = User.id;
  1. Run the SQL statement against your database to create the view:
$psql SqlViews <\ sql-views-draft.sql
$mysql <\ sql-views-draft.sql
  1. Run the following command to validate that the view was created:
$psql -d SqlViews -c "\dv"
1mysql -e SHOW FULL TABLES in `SqlViews` WHERE TABLE_TYPE LIKE 'VIEW';'

You should see the following list of views:

1 List of relations
2 Schema | Name | Type | Owner
3 --------+--------+------+----------
4 public | Draft | view | postgres
1 +----------------+------------+
2 | Tables_in_mydb | Table_type |
3 +----------------+------------+
4 | Drafts | VIEW |
5 +----------------+------------+

4. Introspect your database with Prisma

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.

  1. Set up a new Node.js project and add the prisma CLI as a development dependency:

    $npm init -y
    $npm install prisma --save-dev
  2. Create a new file called schema.prisma and add the following code to it:

1datasource postgresql {
2 provider = "postgresql"
3 url = env("DATABASE_URL")
4}
1datasource mysql {
2 provider = "mysql"
3 url = env("DATABASE_URL")
4}
  1. 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 called .env and set your database connection URL as the DATABASE_URL environment variable:
1DATABASE_URL=postgresql://__USER__:__PASSWORD__@__HOST__:__PORT__/SqlViews
1DATABASE_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:

1DATABASE_URL=postgresql://janedoe:mypassword@localhost:5432/SqlViews
1DATABASE_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 called .env.

  1. 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:

1 datasource postgresql {
2 provider = "postgresql"
3 url = env("DATABASE_URL")
4 }
5
6 model Post {
7 authorId Int?
8 content String?
9 id Int @default(autoincrement()) @id
10 published Boolean @default(false)
11 title String
12 User User? @relation(fields: [authorId], references: [id])
13 }
14
15 model User {
16 email String @unique
17 id Int @default(autoincrement()) @id
18 name String?
19 Post Post[]
20 }
1 datasource mysql {
2 provider = "mysql"
3 url = env("DATABASE_URL")
4 }
5
6 model Post {
7 authorId Int?
8 content String?
9 id Int @default(autoincrement()) @id
10 published Boolean @default(false)
11 title String
12 User User? @relation(fields: [authorId], references: [id])
13 }
14
15 model User {
16 email String @unique
17 id Int @default(autoincrement()) @id
18 name String?
19 Post Post[]
20 }

5. Manually add the Draft view to the Prisma schema

You must manually add views to the Prisma schema.

  1. 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.

1datasource mysql {
2 provider = "mysql"
3 url = env("DATABASE_URL")
4}
5
6model Post {
7 authorId Int?
8 content String?
9 id Int @default(autoincrement()) @id
10 published Boolean @default(false)
11 title String
12 User User? @relation(fields: [authorId], references: [id])
13}
14
15model User {
16 email String @unique
17 id Int @default(autoincrement()) @id
18 name String?
19 Post Post[]
20}
21
+ model Draft {
+ title String
+ id Int @unique
+ email String
+ published Boolean
+ }
1datasource postgresql {
2 provider = "postgresql"
3 url = env("DATABASE_URL")
4}
5
6model Post {
7 authorId Int?
8 content String?
9 id Int @default(autoincrement()) @id
10 published Boolean @default(false)
11 title String
12 User User? @relation(fields: [authorId], references: [id])
13}
14
15model User {
16 email String @unique
17 id Int @default(autoincrement()) @id
18 name String?
19 Post Post[]
20}
21
+ model Draft {
+ title String
+ id Int @unique
+ email String
+ published Boolean
+ }

6. Generate Prisma Client

In this section, you will generate Prisma Client.

  1. Add a generator block to your Prisma schema (typically added right below the datasource block):

    1generator client {
    2 provider = "prisma-client-js"
    3}
  2. 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:

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

    1const { PrismaClient } = require('@prisma/client')
    2
    3const prisma = new PrismaClient({})
    4
    5async function main() {
    6 const sarahPosts = await prisma.user.create({
    7 data: {
    8 name: 'Sarah',
    9 email: 'sarah@prisma.io',
    10 Post: {
    11 create: [
    12 { title: 'My first post', published: false },
    13 { title: 'All about databases', published: true },
    14 { title: 'Prisma Day 2020', published: false },
    15 ],
    16 },
    17 },
    18 })
    19
    20 const emilyPosts = await prisma.user.create({
    21 data: {
    22 name: 'Emily',
    23 email: 'emily@prisma.io',
    24 Post: {
    25 create: [
    26 { title: 'My first post', published: false },
    27 { title: 'All about databases', published: true },
    28 { title: 'Prisma Day 2020', published: false },
    29 ],
    30 },
    31 },
    32 })
    33
    34 const drafts = await prisma.draft.findMany({})
    35
    36 console.log(drafts)
    37
    38 const filteredDrafts = await prisma.draft.findMany({
    39 where: {
    40 email: 'sarah@prisma.io',
    41 },
    42 })
    43
    44 console.log(filteredDrafts)
    45}
    46
    47main()
    48 .catch(e => {
    49 throw e
    50 })
    51 .finally(async () => {
    52 await prisma.disconnect()
    53 })

    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
  2. Run the code with the following command:

    1node 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):

    1/* ALL DRAFTS */
    2[
    3 {
    4 "title": "My first post",
    5 "id": 37,
    6 "email": "sarah@prisma.io",
    7 "published": false
    8 },
    9 {
    10 "title": "Prisma Day 2020",
    11 "id": 39,
    12 "email": "sarah@prisma.io",
    13 "published": false
    14 },
    15 {
    16 "title": "My first post",
    17 "id": 40,
    18 "email": "emily@prisma.io",
    19 "published": false
    20 },
    21 {
    22 "title": "Prisma Day 2020",
    23 "id": 42,
    24 "email": "emily@prisma.io",
    25 "published": false
    26 }
    27][
    28 /* FILTERED DRAFTS */
    29 ({
    30 "title": "My first post",
    31 "id": 37,
    32 "email": "sarah@prisma.io",
    33 "published": false
    34 },
    35 {
    36 "title": "Prisma Day 2020",
    37 "id": 39,
    38 "email": "sarah@prisma.io",
    39 "published": false
    40 })
    41]
Edit this page on Github