SQLite
The SQLite data source connector connects Prisma to a SQLite database file. These files always have the file ending .db
(e.g.: dev.db
).
Example
To connect to a SQLite database file, you need to configure a datasource
block in your schema file:
schema.prisma
1datasource db {2 provider = "sqlite"3 url = "file:./dev.db"4}
The fields passed to the datasource
block are:
provider
: Specifies thesqlite
data source connector.url
: Specifies the connection URL for the SQLite database. The connection URL always starts with the prefixfile:
and then contains a file path pointing to the SQLite database file. In this case, the file is located in the same directory and calleddev.db
.
Data model mapping
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 type.
Data model | SQLite |
---|---|
String | TEXT |
Boolean | BOOLEAN |
Int | INTEGER |
BigInt | INTEGER |
DateTime | NUMERIC |
Float | REAL |
Decimal | DECIMAL |
Json | Not supported |
Bytes | BLOB |
Rounding errors on big numbers
SQLite is a loosely-typed database. If your Schema has a field of type Int
, then Prisma 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 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 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
1datasource db {2 provider = "sqlite"3 url = "file:./dev.db"4}
is the same as:
schema.prisma
1datasource db {2 provider = "sqlite"3 url = "file:dev.db"4}
You can also target files from the root or any other place in your file system:
schema.prisma
1datasource db {2 provider = "sqlite"3 url = "file:/Users/janedoe/dev.db"4}