apps/docs/content/docs/(index)/prisma-orm/quickstart/sqlite.mdx
SQLite is a lightweight, file-based database that's perfect for development, prototyping, and small applications. It requires no setup and stores data in a local file.
In this guide, you will learn how to set up a new TypeScript project from scratch, connect it to SQLite using Prisma ORM, and generate a Prisma Client for easy, type-safe access to your database.
Create a project directory and navigate into it:
mkdir hello-prisma
cd hello-prisma
Initialize a TypeScript project:
npm init -y
npm install typescript tsx @types/node --save-dev
npx tsc --init
Install the packages needed for this quickstart:
npm install prisma @types/node @types/better-sqlite3 -D
npm install @prisma/client @prisma/adapter-better-sqlite3 dotenv
:::note[pnpm users with SQLite]
If using pnpm 10+ with pnpx, you'll need the --allow-build=better-sqlite3 flag when running Prisma Studio due to SQLite's native dependency requirements.
:::
Here's what each package does:
prisma - The Prisma CLI for running commands like prisma init, prisma migrate, and prisma generate@prisma/client - The Prisma Client library for querying your database@prisma/adapter-better-sqlite3 - The SQLite driver adapter that connects Prisma Client to your database@types/better-sqlite3 - TypeScript type definitions for better-sqlite3dotenv - Loads environment variables from your .env fileUpdate tsconfig.json for ESM compatibility:
{
"compilerOptions": {
"module": "ESNext",
"moduleResolution": "bundler",
"target": "ES2023",
"strict": true,
"esModuleInterop": true,
"ignoreDeprecations": "6.0"
}
}
Update package.json to enable ESM:
{
"type": "module" // [!code ++]
}
You can now invoke the Prisma CLI by prefixing it with npx:
npx prisma
Next, set up your Prisma ORM project by creating your Prisma Schema file with the following command:
npx prisma init --datasource-provider sqlite --output ../generated/prisma
This command does a few things:
prisma/ directory with a schema.prisma file containing your database connection and schema models.env file in the root directory for environment variablesprisma.config.ts file for Prisma configurationThe generated prisma.config.ts file looks like this:
import "dotenv/config";
import { defineConfig, env } from "prisma/config";
export default defineConfig({
schema: "prisma/schema.prisma",
migrations: {
path: "prisma/migrations",
},
datasource: {
url: env("DATABASE_URL"),
},
});
The generated schema uses the ESM-first prisma-client generator with a custom output path:
generator client {
provider = "prisma-client"
output = "../generated/prisma"
}
datasource db {
provider = "sqlite"
}
A .env file should be created with the following value:
DATABASE_URL="file:./dev.db"
Open prisma/schema.prisma and add the following models:
generator client {
provider = "prisma-client"
output = "../generated/prisma"
}
datasource db {
provider = "sqlite"
}
model User { // [!code ++]
id Int @id @default(autoincrement()) // [!code ++]
email String @unique // [!code ++]
name String? // [!code ++]
posts Post[] // [!code ++]
} // [!code ++]
model Post { // [!code ++]
id Int @id @default(autoincrement()) // [!code ++]
title String // [!code ++]
content String? // [!code ++]
published Boolean @default(false) // [!code ++]
author User @relation(fields: [authorId], references: [id]) // [!code ++]
authorId Int // [!code ++]
} // [!code ++]
Create your first migration to set up the database tables:
npx prisma migrate dev --name init
This command creates the database tables based on your schema.
Now run the following command to generate the Prisma Client:
npx prisma generate
Now that you have all the dependencies installed, you can instantiate Prisma Client. You need to pass an instance of the Prisma ORM driver adapter adapter to the PrismaClient constructor:
import "dotenv/config";
import { PrismaBetterSqlite3 } from "@prisma/adapter-better-sqlite3";
import { PrismaClient } from "../generated/prisma/client";
const connectionString = `${process.env.DATABASE_URL}`;
const adapter = new PrismaBetterSqlite3({ url: connectionString });
const prisma = new PrismaClient({ adapter });
export { prisma };
When targeting Bun, use the @prisma/adapter-libsql adapter instead of @prisma/adapter-better-sqlite3. Bun doesn’t support the native SQLite driver that better-sqlite3 relies on (see the node:sqlite reference). Instantiate Prisma Client like so:
import "dotenv/config";
import { PrismaLibSql } from "@prisma/adapter-libsql";
import { PrismaClient } from "../generated/prisma/client";
const adapter = new PrismaLibSql({
url: process.env.DATABASE_URL ?? "",
});
const prisma = new PrismaClient({ adapter });
export { prisma };
Create a script.ts file to test your setup:
import { prisma } from "./lib/prisma";
async function main() {
// Create a new user with a post
const user = await prisma.user.create({
data: {
name: "Alice",
email: "[email protected]",
posts: {
create: {
title: "Hello World",
content: "This is my first post!",
published: true,
},
},
},
include: {
posts: true,
},
});
console.log("Created user:", user);
// Fetch all users with their posts
const allUsers = await prisma.user.findMany({
include: {
posts: true,
},
});
console.log("All users:", JSON.stringify(allUsers, null, 2));
}
main()
.then(async () => {
await prisma.$disconnect();
})
.catch(async (e) => {
console.error(e);
await prisma.$disconnect();
process.exit(1);
});
Run the script:
npx tsx script.ts
You should see the created user and all users printed to the console!
To view SQLite databases within Prisma Studio:
file: protocol right now in the database URL for SQLitenode:sqlite module
NODE_OPTIONS=--experimental-sqlite environment variablebetter-sqlite3 as a dependency
pnpx, you'll need the --allow-build=better-sqlite3 flagIf you don't have node:sqlite available in your runtime or prefer not to install better-sqlite3 as a hard dependency, you can use npx to temporarily install the required packages:
npx -p better-sqlite3 -p prisma prisma studio --url file:./dev.db
This command:
better-sqlite3 without adding it to your project dependenciesbetter-sqlite3 in your projectYou've successfully set up Prisma ORM. Here's what you can explore next: