Quickstart: Getting started with TypeScript & SQLite

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:

Unix (Mac OS, Linux)

Windows


$curl -L https://pris.ly/quickstart | tar -xz --strip=2 quickstart-master/typescript/starter

Unix (Mac OS, Linux)

Windows


$curl -L https://pris.ly/quickstart | tar -xz --strip=2 quickstart-master/javascript/starter

The project is downloaded to a directory called starter.

Expand for more info on the starter project

The starter project consists of six files:

package.json: Defines your npm dependencies
prisma/schema.prisma: Prisma schema file defining your models
prisma/.env: Defines your database connection URL as an environment variable
prisma/dev.db: A SQLite database file
script.ts: A plain, executable TypeScript script
tsconfig.json: TypeScript configuration

Note that the dependencies defined in your project are:

prisma: The Prisma CLI which you can invoke with npx prisma
@prisma/client: Prisma Client library for accessing your database
typescript: TypeScript toolchain
ts-node: Used to run the TypeScript script

The starter project consists of five files:

package.json: Defines your npm dependencies
prisma/schema.prisma: Prisma schema file defining your models
prisma/.env: Defines your database connection URL as an environment variable
prisma/dev.db: A SQLite database file
script.js: A plain, executable Node.js script

Note that the only two dependencies defined in your project are:

prisma: The Prisma CLI which you can invoke with npx 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	email	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 the id column of the User table, meaning that the 2 in the authorId column refers to the User record with an id value of 2.

Before you move on, navigate into the starter directory and install the npm dependencies with these commands:


$cd starter
$npm install

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}
5
6generator client {
7  provider = "prisma-client-js"
8}
9
10model Post {
11  id        Int     @id @default(autoincrement())
12  title     String
13  content   String?
14  published Boolean @default(false)
15  author    User?   @relation(fields: [authorId], references: [id])
16  authorId  Int?
17}
18
19model User {
20  id    Int     @id @default(autoincrement())
21  email String  @unique
22  name  String?
23  posts Post[]
24}

The code inside script.ts currently looks as follows:

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

The code inside script.js currently looks as follows:

script.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  })

Expand for an explanation of the script

Here's a quick overview of the main parts:

Import PrismaClient constructor from the @prisma/client node module
Instantiate PrismaClient
Define an async function called main 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
1async function main() {
2  const allUsers = await prisma.user.findMany()
3  console.log(allUsers)
4}

script.js
1async function main() {
2  const allUsers = await prisma.user.findMany()
3  console.log(allUsers)
4}

Now run the code with this command:


$npm run dev

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
1async function main() {
2  const allUsers = await prisma.user.findMany({
3    include: { posts: true },
4  })
5  // use `console.dir` to print nested objects
6  console.dir(allUsers, { depth: null })
7}

script.js
1async function main() {
2  const allUsers = await prisma.user.findMany({
3    include: { posts: true },
4  })
5  // use `console.dir` to print nested objects
6  console.dir(allUsers, { depth: null })
7}

Run the code again with:


$npm run dev

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: number
  title: string
  content: string | null
  published: boolean
  authorId: 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
1async function main() {
2  const post = await prisma.post.create({
3    data: {
4      title: 'Prisma makes databases easy',
5      author: {
6        connect: { email: 'sarah@prisma.io' },
7      },
8    },
9  })
10  console.log(post)
11
12  const allUsers = await prisma.user.findMany({
13    include: { posts: true },
14  })
15  console.dir(allUsers, { depth: null })
16}

script.js
1async function main() {
2  const post = await prisma.post.create({
3    data: {
4      title: 'Prisma makes databases easy',
5      author: {
6        connect: { email: 'sarah@prisma.io' },
7      },
8    },
9  })
10  console.log(post)
11
12  const allUsers = await prisma.user.findMany({
13    include: { posts: true },
14  })
15  console.dir(allUsers, { depth: null })
16}

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:


$npm run dev

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
1async function main() {
2  const post = await prisma.post.update({
3    where: { id: 2 },
4    data: { published: true },
5  })
6  console.log(post)
7}

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

Now run the code using the same command as before:


$npm run dev

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.

Expand for more Prisma Client API examples

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

Filter all Post records that contain "prisma"


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

Create a new User and a new Post record in the same query


const user = await prisma.user.create({
  data: {
    name: 'Nancy',
    email: 'nancy@prisma.io',
    posts: {
      create: { title: 'Join us for Prisma Day 2020' },
    },
  },
})

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


const posts = await prisma.user
  .findUnique({
    where: { email: 'sarah@prisma.io' },
  })
  .posts()

Delete a User record


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

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 scratch

Add 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 project

Explore 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     String
4  content   String?
5  published Boolean @default(false)
6  author    User?   @relation(fields: [authorId], references: [id])
7  authorId  Int?
8}
9
10model User {
11  id      Int      @id @default(autoincrement())
12  email   String   @unique
13  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:


$npx prisma migrate dev --name add-profile

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:

TypeScript

JavaScript (Node.js)

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

Was this helpful?

Edit this page on GitHub

Quickstart

Download SQLite starter project & install dependencies

Write your first query with Prisma Client

Write data into the database

Next steps

Continue exploring the Prisma Client API

Set up Prisma with your own database

Explore the data in Prisma Studio

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

Try a Prisma example

Was this helpful?

Products

Resources

Prisma With

Community

Company

Newsletter

Find Us