Start from scratch


TypeScript
PostgreSQL

Overview

This page walks you through the process of setting up Prisma from scratch with your own database. It uses plain SQL to create tables in your database.

Prerequisites

In order to successfully complete this guide, you need:

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

Make sure you have your database connection URL (that includes your authentication credentials) at hand!

If you don't have a database server running and just want to explore Prisma, check out the Quickstart. Alternatively you can setup a free PostgreSQL database on Heroku and use it in this guide.

Create project setup

As a first step, create a project directory and navigate into it:

$mkdir hello-prisma
$cd hello-prisma

Next, initialize a TypeScript project and add the Prisma CLI as a development dependency to it:

$npm init -y
$npm install @prisma/cli typescript ts-node @types/node --save-dev

This creates a package.json with an initial setup for your TypeScript app.

Next, create a tsconfig.json file and add the following configuration to it:

tsconfig.json
1{
2 "compilerOptions": {
3 "sourceMap": true,
4 "outDir": "dist",
5 "strict": true,
6 "lib": ["esnext"],
7 "esModuleInterop": true
8 }
9}

Next, initialize a Node.js project and add the Prisma CLI as a development dependency to it:

$npm init -y
$npm install @prisma/cli --save-dev

This creates a package.json with an initial setup for a Node.js app.

You can now invoke the Prisma CLI by prefixing it with npx:

$npx prisma

Next, set up your Prisma project by creating your Prisma schema file with the following command:

$npx prisma init

This command created a new directory called prisma with the following contents:

  • schema.prisma: The Prisma schema with your database connection and the Prisma Client generator
  • .env: A dotenv file for defining environment variables (used for your database connection)

Connect your database

To connect your database, you need to set the url field of the datasource block in your Prisma schema to your database connection URL:

prisma/schema.prisma
1datasource db {
2 provider = "postgresql"
3 url = env("DATABASE_URL")
4}

In this case, the url is set via an environment variable which is defined in prisma/.env:

prisma/.env
1DATABASE_URL="postgresql://johndoe:randompassword@localhost:5432/mydb?schema=public"

You now need to adjust the connection URL to point to your own database.

The format of the connection URL for your database depends on the database you use. For PostgreSQL, it looks as follows (the parts spelled all-uppercased are placeholders for your specific connection details):

postgresql://USER:PASSWORD@HOST:PORT/DATABASE?schema=SCHEMA

Here's a short explanation of each component:

  • USER: The name of your database user
  • PASSWORD: The password for your database user
  • PORT: The port where your database server is running (typically 5432 for PostgreSQL)
  • DATABASE: The name of the database
  • SCHEMA: The name of the schema inside the database

If you're unsure what to provide for the schema parameter for a PostgreSQL connection URL, you can probably omit it. In that case, the default schema name public will be used.

As an example, for a PostgreSQL database hosted on Heroku, the connection URL might look similar to this:

prisma/.env
1DATABASE_URL="postgresql://opnmyfngbknppm:XXX@ec2-46-137-91-216.eu-west-1.compute.amazonaws.com:5432/d50rgmkqi2ipus?schema=hello-prisma"

When running PostgreSQL locally on Mac OS, your user and password as well as the database name typically correspond to the current user of your OS, e.g. assuming the user is called janedoe:

prisma/.env
1DATABASE_URL="postgresql://janedoe:janedoe@localhost:5432/janedoe?schema=hello-prisma"
prisma/schema.prisma
1datasource db {
2 provider = "postgresql"
3 url = env("DATABASE_URL")
4}

Note that the default schema created by prisma init uses PostgreSQL, so you first need to switch the provider to mysql:

prisma/schema.prisma
1datasource db {
provider = "mysql"
3 url = env("DATABASE_URL")
4}

In this case, the url is set via an environment variable which is defined in prisma/.env:

prisma/.env
1DATABASE_URL="mysql://johndoe:randompassword@localhost:3306/mydb"

You now need to adjust the connection URL to point to your own database.

The format of the connection URL for your database typically depends on the database you use. For MySQL, it looks as follows (the parts spelled all-uppercased are placeholders for your specific connection details):

mysql://USER:PASSWORD@HOST:PORT/DATABASE

Here's a short explanation of each component:

  • USER: The name of your database user
  • PASSWORD: The password for your database user
  • PORT: The port where your database server is running (typically 3306 for MySQL)
  • DATABASE: The name of the database

As an example, for a MySQL database hosted on AWS RDS, the connection URL might look similar to this:

prisma/.env
1DATABASE_URL="mysql://johndoe:XXX@mysql–instance1.123456789012.us-east-1.rds.amazonaws.com:3306/mydb"

When running MySQL locally, your connection URL typically looks similar to this:

prisma/.env
1DATABASE_URL="mysql://root:randompassword@localhost:3306/mydb"

Create database tables with SQL

In this guide, you'll use plain SQL to create the tables in your database. Create a new file called schema.sql and add the following contents to it:

schema.sql
1CREATE TABLE "public"."User" (
2 id SERIAL PRIMARY KEY NOT NULL,
3 name VARCHAR(255),
4 email VARCHAR(255) UNIQUE NOT NULL
5);
6
7CREATE TABLE "public"."Post" (
8 id SERIAL PRIMARY KEY NOT NULL,
9 title VARCHAR(255) NOT NULL,
10 "createdAt" TIMESTAMP NOT NULL DEFAULT now(),
11 content TEXT,
12 published BOOLEAN NOT NULL DEFAULT false,
13 "authorId" INTEGER NOT NULL,
14 FOREIGN KEY ("authorId") REFERENCES "public"."User"(id)
15);
16
17CREATE TABLE "public"."Profile" (
18 id SERIAL PRIMARY KEY NOT NULL,
19 bio TEXT,
20 "userId" INTEGER UNIQUE NOT NULL,
21 FOREIGN KEY ("userId") REFERENCES "public"."User"(id)
22);

See type mapping between PostgreSQL to Prisma schema during introspection for information about what each database type maps to in the Prisma Schema.

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.

You can create the tables using any PostgreSQL client of your choice. If you're using psql, you can now create the tables using the following command:

$psql -h HOST -d DATABASE -U USER -f schema.sql

Similar to before, you need to replace the all-uppercase placeholders with your database credentials, e.g.:

Unix (Mac OS, Linux)
Windows
$psql -h localhost -d hello-prisma -U janedoe -f schema.sql

Great, you now created three tables in your database

User

Column nameTypePrimary keyForeign keyRequiredDefault
idSERIAL✔️No✔️autoincrementing
nameVARCHAR(255)NoNoNo-
emailVARCHAR(255)NoNo✔️-

Post

Column nameTypePrimary keyForeign keyRequiredDefault
idSERIAL✔️No✔️autoincrementing
createdAtTIMESTAMPNoNo✔️now()
titleVARCHAR(255)NoNo✔️-
contentTEXTNoNoNo-
publishedBOOLEANNoNo✔️false
authorIdINTEGERNo✔️✔️-

Profile

Column nameTypePrimary keyForeign keyRequiredDefault
idSERIAL✔️No✔️autoincrementing
bioTEXTNoNoNo-
userIdINTEGERNo✔️✔️-

In this guide, you'll use plain SQL to create the tables in your database. Create a new file called schema.sql and add the following contents to it:

schema.sql
1CREATE TABLE User (
2 id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
3 name VARCHAR(255),
4 email VARCHAR(255) UNIQUE NOT NULL
5);
6
7CREATE TABLE Post (
8 id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
9 title VARCHAR(255) NOT NULL,
10 createdAt TIMESTAMP NOT NULL DEFAULT now(),
11 content TEXT,
12 published BOOLEAN NOT NULL DEFAULT false,
13 authorId INTEGER NOT NULL,
14 FOREIGN KEY (authorId) REFERENCES User(id)
15);
16
17CREATE TABLE Profile (
18 id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
19 bio TEXT,
20 userId INTEGER UNIQUE NOT NULL,
21 FOREIGN KEY (userId) REFERENCES User(id)
22);

You can create the tables using any MySQL client of your choice. If you're using mysql, you can now create the tables using the following command:

$mysql <\ schema.sql

Great, you now created three tables in your database

User

Column nameTypePrimary keyForeign keyRequiredDefault
idINTEGER✔️No✔️autoincrementing
nameVARCHAR(255)NoNoNo-
emailVARCHAR(255)NoNo✔️-

Post

Column nameTypePrimary keyForeign keyRequiredDefault
idINTEGER✔️No✔️autoincrementing
createdAtDATETIME(3)NoNo✔️now()
titleVARCHAR(255)NoNo✔️-
contentTEXTNoNoNo-
publishedBOOLEANNoNo✔️false
authorIdINTEGERNo✔️✔️false

Profile

Column nameTypePrimary keyForeign keyRequiredDefault
idINTEGER✔️No✔️autoincrementing
bioTEXTNoNoNo-
userIdINTEGERNo✔️✔️-

Introspect your database with Prisma

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:

$npx prisma introspect

This command 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 has completed, your Prisma schema file was updated:

The data model now looks as follows (note that the fields on the models have been reordered for better readability):

prisma/schema.prisma
1model Post {
2 id Int @default(autoincrement()) @id
3 createdAt DateTime @default(now())
4 title String
5 content String?
6 published Boolean @default(false)
7 User User @relation(fields: [authorId], references: [id])
8 authorId Int
9}
10
11model Profile {
12 id Int @default(autoincrement()) @id
13 bio String?
14 User User @relation(fields: [userId], references: [id])
15 userId Int @unique
16}
17
18model User {
19 id Int @default(autoincrement()) @id
20 email String @unique
21 name String?
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 called author to describe the relationship between User and Post better.
  • The Post and Profile relation fields on User as well as the User relation field on Profile are all uppercased. To adhere to Prisma's naming conventions , both fields should be lowercased to post, profile and user.
  • Even after lowercasing, the post field on User 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 @default(autoincrement()) @id
3 createdAt DateTime @default(now())
4 title String
5 content String?
6 published Boolean @default(false)
author User @relation(fields: [authorId], references: [id])
8 authorId Int
9}
10
11model Profile {
12 id Int @default(autoincrement()) @id
13 bio String?
user User @relation(fields: [userId], references: [id])
15 userId Int @unique
16}
17
18model User {
19 id Int @default(autoincrement()) @id
20 email String @unique
21 name String?
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.

Learn more about this on the Configuring your Prisma Client API page.

Install and generate Prisma Client

To get started with Prisma Client, you need to install the @prisma/client package:

$npm install @prisma/client

Notice that the @prisma/client node module references a folder named .prisma\client. The .prisma\client folder contains your generated Prisma Client, and is modified each time you change the schema and run the following command:

$npx prisma generate

This commands reads your Prisma schema and generates your Prisma Client library into node_modules/@prisma/client.

The @prisma/client node module references a folder named .prisma/client, which contains your unique, generated Prisma Client:

The .prisma and @prisma folders

Write your first query with Prisma Client

Now that you have generated your Prisma Client library, you can start writing queries to read and write data in your database. For the purpose of this guide, you'll use a plain Node.js script to explore some basic features of Prisma Client.

Create a new file called index.ts and add the following code to it:

index.ts
1import { PrismaClient } from "@prisma/client"
2
3const prisma = new PrismaClient()
4
5async function main() {
6 // ... you will write your Prisma Client queries here
7}
8
9main()
10 .catch(e => {
11 throw e
12 })
13 .finally(async () => {
14 await prisma.$disconnect()
15 })

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

index.js
1const { PrismaClient } = require("@prisma/client")
2
3const prisma = new PrismaClient()
4
5async function main() {
6 // ... you will write your Prisma Client queries here
7}
8
9main()
10 .catch(e => {
11 throw e
12 })
13 .finally(async () => {
14 await prisma.$disconnect()
15 })

Here's a quick overview of the different parts of the code snippet:

  1. Import the PrismaClient constructor from the @prisma/client node module
  2. Instantiate PrismaClient
  3. Define an async function called main to send queries to the database
  4. Call the main function
  5. Close the database connections when the script terminates

Inside the main function, add the following query to read all User records from the database and print the result:

index.ts
1async function main() {
- // ... you will write your Prisma Client queries here
+ const allUsers = await prisma.user.findMany()
+ console.log(allUsers)
5}
index.js
1async function main() {
- // ... you will write your Prisma Client queries here
+ const allUsers = await prisma.user.findMany()
+ console.log(allUsers)
5}

Now run the code with this command:

$npx ts-node index.ts
$node index.js

This should print an empty array because there are no User records in the database yet:

[]

Write data into the database

The findMany query you used in the previous section only reads data from the database (although it was still empty). In this section, you'll learn how to write a query to write new records into the Post and User tables.

Adjust the main function to send a create query to the database:

index.ts
1async function main() {
2
3 await prisma.user.create({
4 data: {
5 name: "Alice",
6 email: "alice@prisma.io",
7 posts: {
8 create: { title: "Hello World" },
9 },
10 profile: {
11 create: { bio: "I like turtles" }
12 }
13 }
14 })
15
16 const allUsers = await prisma.user.findMany({
17 include: {
18 posts: true,
19 profile: true
20 },
21 })
22 console.dir(allUsers, { depth: null })
23}
index.js
1async function main() {
2
3 await prisma.user.create({
4 data: {
5 name: "Alice",
6 email: "alice@prisma.io",
7 posts: {
8 create: { title: "Hello World" },
9 },
10 profile: {
11 create: { bio: "I like turtles" }
12 }
13 }
14 })
15
16 const allUsers = await prisma.user.findMany({
17 include: {
18 posts: true,
19 profile: true
20 },
21 })
22 console.dir(allUsers, { depth: null })
23}

This code creates a new User record together with new Post and Profile records using a nested write query. The User record is connected to the two other ones via the Post.authorUser.posts and Profile.userUser.profile relation fields respectively.

Notice that you're passing the include option to findMany which tells Prisma Client to include the posts and profile relations on the returned User objects.

Run the code with this command:

$npx ts-node index.ts

Run the code with this command:

$node index.js

The output should look similar to this:

[
{
email: 'alice@prisma.io',
id: 1,
name: 'Alice',
posts: [
{
content: null,
createdAt: 2020-03-21T16:45:01.246Z,
id: 1,
published: false,
title: 'Hello World',
authorId: 1,
}
],
profile: {
bio: 'I like turtles',
id: 1,
userId: 1,
}
}
]

Also note that allUsers is statically typed thanks to Prisma Client's generated types. You can observe the type by hovering over the allUsers variable in your editor. It should be typed as follows:

const allUsers: (User & {
posts: Post[];
})[]
export type Post = {
id: number
title: string
content: string | null
published: boolean
authorId: number | null
}

The query added new records to the User and the Post tables:

User

idemailname
1"alice@prisma.io""Alice"

Post

idcreatedAttitlecontentpublishedauthorId
12020-03-21T16:45:01.246Z"Hello World"nullfalse1

Profile

idbiouserId
1"I like turtles"1

Note: The numbers in the authorId column on Post and userId column on Profile both reference the id column of the User table, meaning the id value 1 column therefore refers to the first (and only) User record in the database.

Before moving on to the next section, you will "publish" the Post record you just created using an update query. Adjust the main function as follows:

index.ts
1async function main() {
2 const post = await prisma.post.update({
3 where: { id: 1 },
4 data: { published: true },
5 })
6 console.log(post)
7}
index.js
1async function main() {
2 const post = await prisma.post.update({
3 where: { id: 1 },
4 data: { published: true },
5 })
6 console.log(post)
7}

Now run the code using the same command as before:

$npx ts-node index.ts

Now run the code using the same command as before:

$node index.js

You will see the following output:

{
authorId: 1,
content: null,
createdAt: 2020-04-09T09:25:07.663Z,
id: 1,
published: true,
title: 'Hello World'
}

The Post record with an id of 1 now got updated in the database:

Post

idtitlecontentpublishedauthorId
1"Hello World"nulltrue1

Fantastic, you just wrote new data into your database for the first time using Prisma Client 🚀

Next steps

This section lists a number of potential next steps you can now take from here. Feel free to explore these or read the Introduction page to get a high-level overview of Prisma.

Continue exploring the Prisma Client API

You can send a variety of queries with the Prisma Client API. Check out the API reference and use your existing database setup from this guide to try them out.

Tip: You can use your editor's auto-completion feature to learn about the different API calls and the arguments it takes. Auto-completion is commonly invoked by hitting CTRL+SPACE on your keyboard.

Here are a few suggestions for a number of more queries you can send with Prisma Client:

Filter all Post records that contain "hello"

const filteredPosts = await prisma.post.findMany({
where: {
OR: [
{ title: { contains: "hello" },
{ content: { contains: "hello" },
],
},
})

Create a new Post record and connect it to an existing User record

const post = await prisma.post.create({
data: {
title: "Join us for Prisma Day 2020",
author: {
connect: { email: "alice@prisma.io" },
},
},
})

Use the fluent relations API to retrieve the Post records of a User by traversing the relations

const posts = await prisma.profile
.findOne({
where: { id: 1 },
})
.user()
.posts()

Delete a User record

const deletedUser = await prisma.user.delete({
where: { email: "sarah@prisma.io" },
})

Explore the data in Prisma Studio

Prisma Studio is a visual editor for the data in your database. You can use it by running the following command:

$npx prisma studio

Change the database schema (e.g. add more tables)

To evolve the app, you need to follow the same flow of the tutorial:

  1. Manually adjust your database schema using SQL
  2. Re-introspect your database
  3. Optionally re-configure your Prisma Client API
  4. Re-generate Prisma Client

Try a Prisma example

The prisma-examples repository contains a number of ready-to-run examples:

TypeScript
JavaScript (Node.js)
DemoStackDescription
rest-nextjsFullstackSimple Next.js app (React) with a REST API
graphql-nextjsFullstackSimple Next.js app (React) with a GraphQL API
graphql-apollo-serverBackend onlySimple GraphQL server based on apollo-server
rest-expressBackend onlySimple REST API with Express.JS
grpcBackend onlySimple gRPC API
Edit this page on GitHub