Migrating from MariaDB to sqlite
I am currently migrating from mariadb to sqlite (both using prisma).
I have multiple
Decimal(20,8) values in my prisma schema file.
Do i need to store the values as text or does the prisma sqlite client keep the high decimal precision?1 Reply
You've gone full bot mode! Let me fetch that AI answer for you ASAP.
When migrating from MariaDB to SQLite using Prisma, handling high-precision decimals like
Decimal(20,8) requires caution. In MariaDB, DECIMAL(20,8) is a true fixed-point type, but in SQLite, the DECIMAL type does not guarantee fixed-point or high-precision storage. Instead, SQLite treats DECIMAL as a type with NUMERIC affinity, which often stores values as floating-point numbers, not as exact decimals. This can lead to rounding errors or loss of precision for large or highly precise decimal values.
Prisma maps the Decimal type in your schema to DECIMAL in SQLite, but due to SQLite's internal handling, you may not get the same precision as in MariaDB. Several issues and discussions confirm that SQLite's DECIMAL is not truly arbitrary-precision and can result in precision loss, especially beyond 15 significant digits. Prisma's own contributors have noted that "there is no proper and reliable way to store Decimal" in SQLite, and that the returned Decimal objects may be less accurate than expected. If you need to guarantee exact precision, storing values as TEXT and handling conversion in your application is a safer approach, though it sacrifices native numeric operations in the database. Alternatively, you can use Float (which maps to REAL in SQLite), but this will also not guarantee high precision for large or precise decimals.
In summary: Prisma does not guarantee high decimal precision for Decimal fields in SQLite. If you require exact precision, consider storing values as TEXT and converting them in your application logic, or be aware of the limitations and test thoroughly if you continue using Decimal in SQLiteType mapping between SQLite to Prisma schema, Github Issue: Decimal values are stored correctly but read values do not match (SQLite).
✅ Great, thanks for letting us know that this solved your question!