Quickstart
In this page, you will learn how to send queries to a SQLite database in a plain TypeScript script using Prisma Client. Our other guides cover how to set up Prisma with your own database or in a new project from scratch.
In this page, you will learn how to send queries to a SQLite database in a plain Node.js script using Prisma Client. Our other guides cover how to set up Prisma with your own database or set up a new project from scratch.
Note: You need Node.js (version 10 or higher) for this tutorial.
Download SQLite starter project & install dependencies
Open your terminal and use the following command to download the starter project containing Prisma set up with a SQLite database file:
$
$
The project is downloaded to a directory called starter
.
The starter project consists of six files:
package.json
: Defines your npm dependenciesprisma/schema.prisma
: Prisma schema file defining your modelsprisma/.env
: Defines your database connection URL as an environment variableprisma/dev.db
: A SQLite database filescript.ts
: A plain, executable TypeScript scripttsconfig.json
: TypeScript configuration
Note that the dependencies defined in your project are:
@prisma/cli
: The Prisma CLI which you can invoke withnpx prisma
@prisma/client
: Prisma Client library for accessing your databasetypescript
: TypeScript toolchaints-node
: Used to run the TypeScript script
The starter project consists of five files:
package.json
: Defines your npm dependenciesprisma/schema.prisma
: Prisma schema file defining your modelsprisma/.env
: Defines your database connection URL as an environment variableprisma/dev.db
: A SQLite database filescript.js
: A plain, executable Node.js script
Note that the only two dependencies defined in your project are:
@prisma/cli
: The Prisma CLI which you can invoke withnpx prisma
@prisma/client
: Prisma Client library for accessing your database
The SQLite database file prisma/dev.db
contains two tables with dummy data:
User
id | name | |
---|---|---|
1 | "sarah@prisma.io" | "Sarah" |
2 | "maria@prisma.io" | "Maria" |
Post
id | title | content | published | authorId |
---|---|---|---|---|
1 | "Hello World" | null | false | 2 |
Note: The
authorId
column references theid
column of theUser
table, meaning that the2
in theauthorId
column refers to theUser
record with anid
value of2
.
Before you move on, navigate into the starter
directory and install the npm dependencies with these commands:
$$
Write your first query with Prisma Client
Let's write your first query with Prisma Client. For reference, this is what the Prisma schema that your Prisma Client was generated from looks like:
prisma/schema.prisma
1datasource db {2 provider = "sqlite"3 url = env("DATABASE_URL")4}56generator client {7 provider = "prisma-client-js"8}910model Post {11 id Int @id @default(autoincrement())12 title String13 content String?14 published Boolean @default(false)15 author User? @relation(fields: [authorId], references: [id])16 authorId Int?17}1819model User {20 id Int @id @default(autoincrement())21 email String @unique22 name String?23 posts Post[]24}
The code inside script.ts
currently looks as follows:
script.ts
1import { PrismaClient } from "@prisma/client"23const prisma = new PrismaClient()45// A `main` function so that you can use async/await6async function main() {7 // ... you will write your Prisma Client queries here8}910main()11 .catch(e => {12 throw e13 })14 .finally(async () => {15 await prisma.$disconnect()16 })
The code inside script.js
currently looks as follows:
script.js
1const { PrismaClient } = require("@prisma/client")23const prisma = new PrismaClient()45async function main() {6 // ... you will write your Prisma Client queries here7}89main()10 .catch(e => {11 throw e12 })13 .finally(async () => {14 await prisma.$disconnect()15 })
Here's a quick overview of the main parts:
- Import
PrismaClient
constructor from the@prisma/client
node module - Instantiate
PrismaClient
- Define an
async
function calledmain
to send queries to the database - Call the
main
function - 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:
script.ts
1234
script.js
1234
Now run the code with this command:
$
This should print the following result to your terminal:
[{ id: 1, email: "sarah@prisma.io", name: "Sarah" },{ id: 2, email: "maria@prisma.io", name: "Maria" },]
One of the main features of Prisma Client is the ease of working with relations. You can retrieve the posts
of each user by using the include
option. Adjust your code to look as follows:
script.ts
1234567
script.js
1234567
Run the code again with:
$
Each object inside allUsers
now includes a posts
field that represents data retrieved by following the relation between the User
and Post
tables:
[{ id: 1, email: 'sarah@prisma.io', name: 'Sarah', posts: [] },{id: 2,email: 'maria@prisma.io',name: 'Maria',posts: [{id: 1,title: 'Hello World',content: null,published: false,authorId: 2}]}]
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: numbertitle: stringcontent: string | nullpublished: booleanauthorId: number | null}
Congratulations, you just wrote your first query with Prisma Client 🎉
Write data into the database
The findMany
query you used in the previous section only reads data from the database. 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:
script.ts
12345678910111213141516
script.js
12345678910111213141516
This code creates a new Post
record and connects it in a nested write to an existing User
record.
Run the code with this command:
$
The output should look similar to this:
{id: 2,title: 'Prisma makes databases easy',content: null,published: false,authorId: 1}[{id: 1,email: 'sarah@prisma.io',name: 'Sarah',posts: [{id: 2,title: 'Prisma makes databases easy',content: null,published: false,authorId: 1}]},{id: 2,email: 'maria@prisma.io',name: 'Maria',posts: [{id: 1,title: 'Hello World',content: null,published: false,authorId: 2}]}]
The query added a new record to the Post
table:
Post
id | title | content | published | authorId |
---|---|---|---|---|
1 | "Hello World" | null | false | 2 |
2 | "Prisma makes databases easy" | null | false | 1 |
Before moving on to the next section, you'll "publish" the Post
record you just created using an update
query. Adjust the main
function as follows:
script.ts
1234567
script.js
1234567
Now run the code using the same command as before:
$
You will see the following output:
{id: 2,title: 'Prisma makes databases easy',content: null,published: true,authorId: 1}
The Post
record with an id
of 2
has been updated in the database:
Post
id | title | content | published | authorId |
---|---|---|---|---|
1 | "Hello World" | null | false | 2 |
2 | "Prisma makes databases easy" | null | true | 1 |
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 SQLite-based 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 "prisma"
Create a new User
and a new Post
record in the same query
Use the fluent relations API to retrieve the Post
records of a User
Delete a User
record
Set up Prisma with your own database
To learn how to connect Prisma to your own database, you can follow the respective setup guide.
Set up a new project from scratch
The following guide explains how you can set up a new project from scratch using your own database as well as how to send queries with Prisma Client.
Start from scratchAdd Prisma to an existing npm project
When adding Prisma to an already existing npm project, you can obtain your initial data model through introspection.
Add Prisma to existing projectExplore the data in Prisma Studio
Prisma Studio is a visual editor for the data in your database.
You can run it with two ways:
- Run
$ npx prisma studio
in your terminal. - Install the desktop app from the installers. Windows, macOS and Linux are supported.
Change the database schema (e.g. create more tables)
You can change your database schema using Prisma Migrate.
First, add a new model to your Prisma schema file:
schema.prisma
1model Post {2 id Int @id @default(autoincrement())3 title String4 content String?5 published Boolean @default(false)6 author User? @relation(fields: [authorId], references: [id])7 authorId Int?8}910model User {11 id Int @id @default(autoincrement())12 email String @unique13 name String?14 posts Post[]+ profile Profile?16}17+model Profile {+ id Int @id @default(autoincrement())+ bio String+ user User @relation(fields: [userId], references: [id])+ userId Int @unique+}
Then, run the following command in your terminal:
$
You can now run queries against the new Profile
table, e.g. using prisma.profile.findMany()
.
Try a Prisma example
The prisma-examples
repository contains a number of ready-to-run examples:
Demo | Stack | Description |
---|---|---|
rest-nextjs-api-routes | Fullstack | Simple Next.js app (React) with a REST API |
graphql-nextjs | Fullstack | Simple Next.js app (React) with a GraphQL API |
graphql-apollo-server | Backend only | Simple GraphQL server based on apollo-server |
rest-express | Backend only | Simple REST API with Express.JS |
grpc | Backend only | Simple gRPC API |