SQL views
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
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
, andupdate
, even though you cannot perform these queries on a view.
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-demoNext, make sure that your database server is running. Then execute the following command in your terminal to create a new database called
SqlViews
:$createdb SqlViewsmysql -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):
$psql -d SqlViews -c "\dt"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 called
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:
$psql SqlViews <\ sql-views-tables.sql$mysql <\ sql-views-tables.sqlRun the following command to validate that the tables were created:
$psql -d SqlViews -c "\dt"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 called
sql-views-draft.sql
and add the following code to it:CREATE VIEW "Draft" ASSELECT "published", "title", "email", "Post"."id"FROM "Post", "User"WHERE "published" = false AND "Post"."authorId" = "User"."id";CREATE VIEW Drafts ASSELECT published, title, email, Post.idFROM Post, UserWHERE published = false AND Post.authorId = User.id;Run the SQL statement against your database to create the view:
$psql SqlViews <\ sql-views-draft.sql$mysql <\ sql-views-draft.sqlRun 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 relationsSchema | Name | Type | Owner--------+--------+------+----------public | Draft | view | postgres+----------------+------------+| Tables_in_mydb | Table_type |+----------------+------------+| Drafts | VIEW |+----------------+------------+
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.
Set up a new Node.js project and add the
prisma
CLI as a development dependency:$npm init -y$npm install prisma --save-devCreate a new file called
schema.prisma
and add the following code to it:schema.prisma1datasource db {2 provider = "postgresql"3 url = env("DATABASE_URL")4}schema.prisma1datasource db {2 provider = "mysql"3 url = env("DATABASE_URL")4}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 theDATABASE_URL
environment variable: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:
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
.With both the
schema.prisma
and.env
files in place, run Prisma's introspection with the following command:$npx prisma introspectThis command introspects your database and for each table adds a Prisma model to the Prisma schema:
schema.prisma1datasource db {2 provider = "postgresql"3 url = env("DATABASE_URL")4}56model Post {7 authorId Int?8 content String?9 id Int @default(autoincrement()) @id10 published Boolean @default(false)11 title String12 User User? @relation(fields: [authorId], references: [id])13}1415model User {16 email String @unique17 id Int @default(autoincrement()) @id18 name String?19 Post Post[]20}schema.prisma1datasource db {2 provider = "mysql"3 url = env("DATABASE_URL")4}56model Post {7 authorId Int?8 content String?9 id Int @default(autoincrement()) @id10 published Boolean @default(false)11 title String12 User User? @relation(fields: [authorId], references: [id])13}1415model User {16 email String @unique17 id Int @default(autoincrement()) @id18 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.
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 nameddraft
in the Prisma schema.schema.prisma1datasource db {2 provider = "mysql"3 url = env("DATABASE_URL")4}56model Post {7 authorId Int?8 content String?9 id Int @default(autoincrement()) @id10 published Boolean @default(false)11 title String12 User User? @relation(fields: [authorId], references: [id])13}1415model User {16 email String @unique17 id Int @default(autoincrement()) @id18 name String?19 Post Post[]20}21+ model Draft {+ title String+ id Int @unique+ email String+ published Boolean+ }schema.prisma1datasource db {2 provider = "postgresql"3 url = env("DATABASE_URL")4}56model Post {7 authorId Int?8 content String?9 id Int @default(autoincrement()) @id10 published Boolean @default(false)11 title String12 User User? @relation(fields: [authorId], references: [id])13}1415model User {16 email String @unique17 id Int @default(autoincrement()) @id18 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.
Add a
generator
block to your Prisma schema (typically added right below thedatasource
block):schema.prisma1generator client {2 provider = "prisma-client-js"3}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 threePost
records each - Returns all
Draft
records from the view - Returns all
Draft
records from the view where the author's email isemily@prisma.io
- Creates two
Run the code with the following command:
$node index.jsThe following output indicates that the view works as expected - the first query returns all drafts (
Post
records wherepublished
isfalse
), and the second query returns drafts byemily@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,})]