SQLite

The SQLite data source connector connects Prisma ORM to a SQLite database file. These files always have the file ending .db (e.g.: dev.db).

By default, the SQLite connector contains a database driver responsible for connecting to your database. You can use a driver adapter (Preview) to connect to your database using a JavaScript database driver from Prisma Client.

Example

To connect to a SQLite database file, you need to configure a datasource block in your Prisma schema:

schema.prisma

datasource db {
  provider = "sqlite"
  url      = "file:./dev.db"
}

The fields passed to the datasource block are:

• provider: Specifies the sqlite data source connector.
• url: Specifies the connection URL for the SQLite database. The connection URL always starts with the prefix file: and then contains a file path pointing to the SQLite database file. In this case, the file is located in the same directory and called dev.db.

Using the better-sqlite3 driver

As of v5.4.0, you can use Prisma ORM with database drivers from the JavaScript ecosystem (instead of using Prisma ORM's built-in drivers). You can do this by using a driver adapter.

For SQLite, better-sqlite3 is one of the most popular drivers in the JavaScript ecosystem.

This section explains how you can use it with Prisma ORM and the @prisma/adapter-better-sqlite3 driver adapter.

1. Enable the driverAdapters Preview feature flag

Since driver adapters are currently in Preview, you need to enable its feature flag on the datasource block in your Prisma schema:

schema.prisma

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["driverAdapters"]
}

datasource db {
  provider = "sqlite"
  url      = env("DATABASE_URL")
}

Once you have added the feature flag to your schema, re-generate Prisma Client:

npx prisma generate

2. Install the dependencies

Next, install Prisma ORM's driver adapter for better-sqlite3:

npm install @prisma/adapter-better-sqlite3

3. Instantiate Prisma Client using the driver adapter

Finally, when you instantiate Prisma Client, you need to pass an instance of Prisma ORM's driver adapter to the PrismaClient constructor:

import { PrismaBetterSQLite3 } from '@prisma/adapter-better-sqlite3';
import { PrismaClient } from './generated/prisma';

const adapter = new PrismaBetterSQLite3({
  url: "file:./prisma/dev.db"
});
const prisma = new PrismaClient({ adapter });

Type mapping between SQLite to Prisma schema

The SQLite connector maps the scalar types from the data model to native column types as follows:

Alternatively, see Prisma schema reference for type mappings organized by Prisma ORM type.

Native type mapping from Prisma ORM to SQLite

Prisma ORM	SQLite
String	TEXT
Boolean	BOOLEAN
Int	INTEGER
BigInt	INTEGER
Float	REAL
Decimal	DECIMAL
DateTime	NUMERIC
Json	JSONB
Bytes	BLOB
Enum	TEXT

note

SQLite doesn't have a dedicated Boolean type. While this table shows BOOLEAN, columns are assigned a NUMERIC affinity (storing 0 for false and 1 for true). Learn more.

warning

When using enum fields in SQLite, be aware of the following:

• No database-level enforcement for correctness: If you bypass Prisma ORM and store an invalid enum entry in the database, Prisma Client queries will fail at runtime when reading that entry.
• No migration-level enforcement for correctness: It's possible to end up with incorrect data after schema changes similarly to MongoDB (since the enums aren't checked by the database).

Rounding errors on big numbers

SQLite is a loosely-typed database. If your Schema has a field of type Int, then Prisma ORM prevents you from inserting a value larger than an integer. However, nothing prevents the database from directly accepting a bigger number. These manually-inserted big numbers cause rounding errors when queried.

To avoid this problem, Prisma ORM 4.0.0 and later checks numbers on the way out of the database to verify that they fit within the boundaries of an integer. If a number does not fit, then Prisma ORM throws a P2023 error, such as:

Inconsistent column data: Conversion failed:
Value 9223372036854775807 does not fit in an INT column,
try migrating the 'int' column type to BIGINT

Connection details

Connection URL

The connection URL of a SQLite connector points to a file on your file system. For example, the following two paths are equivalent because the .db is in the same directory:

schema.prisma

datasource db {
  provider = "sqlite"
  url      = "file:./dev.db"
}

is the same as:

schema.prisma

datasource db {
  provider = "sqlite"
  url      = "file:dev.db"
}

You can also target files from the root or any other place in your file system:

schema.prisma

datasource db {
  provider = "sqlite"
  url      = "file:/Users/janedoe/dev.db"
}