SQLite

The recently released queryCompiler feature in Prisma 6.7.0+ has introduced critical bugs affecting data integrity: JSONB columns return empty objects, String[] fields are returned as comma-separated strings instead of arrays, date fields become empty objects, and relations with @map fail to parse. Multiple users report broken functionality across PostgreSQL, D1 (SQLite), and other databases.

SQLite uses file-level locking that locks the entire database during write operations, preventing concurrent writes. This becomes a critical bottleneck in applications with background workers, asynchronous operations, or high-frequency write patterns, and can easily lead to deadlocks.

SQLite's query optimizer uses limited join order search and weak cardinality estimation, causing it to produce inefficient execution plans for complex queries with many tables. Real-world benchmarks show 10-50x slowdowns compared to other databases, with some queries timing out after 60+ seconds, making SQLite impractical for normalized schemas with complex analytical or business logic queries.

SQLite was designed for local file access and does not scale horizontally across networked filesystems (NFS, SMB). Using SQLite on network filesystems can cause locking issues, performance degradation, and data corruption, severely limiting deployment options for distributed or cloud-based applications.

SQLite lacks native support for replication, failover, geographic distribution, and multi-server clustering. The only way to replicate is manual file copying, making it unsuitable for applications requiring high availability, redundancy, or distributed data access across multiple machines.

SQLite generally does not use multiple threads, which limits its ability to handle concurrent operations efficiently. This is particularly problematic for OLAP workloads and applications with high concurrent write demands, as the database cannot parallelize query execution or leverage multi-core hardware capabilities.

Implementing conflict resolution for offline-first applications where users edit the same data across multiple devices simultaneously is difficult. Developers must choose between last-write-wins, operational transforms, CRDTs, or manual resolution flows.

SQLite's default configuration is optimized for backward compatibility rather than modern performance, requiring manual tuning of settings like WAL mode, cache size, and other pragmas. Developers often assume defaults are production-ready, leading to significant performance degradation in web applications and production deployments.

Developers often forget to wrap bulk INSERT, UPDATE, or DELETE operations in transactions. Without transactions, each operation runs separately and incurs significant performance overhead, whereas transaction-wrapped operations execute much more efficiently.

SQLite lacks built-in user authentication and row-level security (RLS) features, relying solely on filesystem permissions for access control. This is unsuitable for multi-tenant applications, team collaboration scenarios, or enterprise use cases requiring fine-grained access control and compliance features.

SQLite lacks support for stored procedures and has only basic trigger functionality, making it difficult to implement complex business logic and data manipulation operations on the database server side. This limits extensibility for enterprise use cases.

SQLite's default flexible typing allows values of any type to be stored in any column, which works during development but causes applications to fail when migrated to stricter databases like PostgreSQL or SQL Server that enforce type rules.

SQLite's row-oriented storage format and execution engine are not optimized for analytical data processing (OLAP) workloads, resulting in poor performance compared to column-oriented databases designed for analytical queries.

SQLite does not support database subscriptions, making it unsuitable for real-time applications that require push notifications or live data synchronization between clients.

Browser-based offline-first applications must gracefully handle storage quota-exceeded errors by pruning data, compressing payloads, or allowing users to clear cached content. Managing storage limits adds complexity to application logic.

SQLite's schema migration capabilities are more complex and error-prone compared to client-server databases, particularly in large projects. This creates friction when evolving database schemas in production environments.

SQLite lacks built-in support for efficient bulk insert operations, forcing developers to insert data row-by-row, which is extremely slow and tedious when dealing with large datasets.

File systems have size limits smaller than hard drive limits, and querying large SQLite files becomes slower as file size increases. The file-based architecture creates performance bottlenecks when databases approach system limits.

SQLite's file-on-disk design makes it impossible for external applications to connect directly to the database. Data must be exposed through HTTP endpoints on the host application, adding complexity and architectural constraints.

Developers frequently forget to properly close SQLite database connections, leading to memory leaks and performance degradation over time. This requires developers to implement proper connection lifecycle management patterns.

SQLite offers only partial support for JSON operations, lacking comprehensive JSON handling capabilities. Applications requiring heavy JSON processing must rely on additional libraries or external tools.

SQLite has foreign key constraint enforcement disabled by default for backwards compatibility with legacy databases, requiring developers to explicitly enable it. This can lead to referential integrity violations if developers are unaware of the default behavior.

Developers struggle with updating SQLite in C++ projects. Modifying third-party dependencies makes updates difficult, and developers often resort to compiling SQLite from source with custom defaults baked in rather than modifying framework dependencies.

SQLite allows NULL values in PRIMARY KEY columns due to a historical bug that was not fixed to maintain backwards compatibility with existing databases. This violates standard SQL semantics and can lead to data integrity issues if developers are not aware of this quirk.

SQLite does not support database extensions or plugins like TimescaleDB for PostgreSQL, limiting optimization options for specialized workloads such as time-series data processing.

SQLite does not perform full Unicode case folding by default; SQL functions like upper() and lower() only work on ASCII characters. Supporting full Unicode case folding would require tables larger than the entire SQLite library, making it impractical.