SQLite JSON Functions: Store and Query JSON Data
SQLite’s JSON functions are surprisingly powerful, allowing you to treat JSON documents as first-class citizens within your relational database without .
Sqlite Articles
49 articles
SQLite Large Dataset Limits: When to Migrate to Postgres
SQLite's perceived limits are often about practical performance, not hard ceilings. Let's see how a typical SQLite setup handles a moderately sized data.
SQLite Limitations: Use Cases Where SQLite Will Fail
SQLite Limitations: Use Cases Where SQLite Will Fail — practical guide covering sqlite setup, configuration, and troubleshooting with real-world examples.
SQLite Migrations in Production: Schema Change Strategies
SQLite migrations in production are a minefield, and the most surprising thing is that the "ideal" strategy often involves not changing the schema at al.
SQLite on Mobile: iOS and Android Best Practices
SQLite on mobile is less about the database itself and more about managing its lifecycle and state across app updates and user interactions.
SQLite Multi-Process Access: Patterns and Locking
SQLite's ability to handle multiple processes accessing the same database file simultaneously is surprisingly robust, but it's not magic; it relies on a.
SQLite Page Size Tuning: Optimize for Your Workload
When you're tweaking SQLite for performance, the pagesize is one of the most impactful settings, but most people don't realize it's not just about makin.
SQLite Partial Indexes: Index Only What You Query
SQLite's partial indexes let you index only a subset of rows in a table, which sounds like a minor optimization, but it's actually a fundamental shift i.
SQLite Performance Tuning: PRAGMA Settings for Production
SQLite's PRAGMA settings can dramatically impact performance, and many are not what you'd expect for a production environment.
SQLite in Python: Thread-Safe Usage Patterns
The most surprising thing about SQLite in Python is that it's not thread-safe by default, which means you can't just share a single sqlite3.
SQLite R-Tree Extension: Geospatial Index for Locations
The SQLite R-Tree extension can index spatial data, but it doesn't actually understand "locations" like latitude/longitude or UTM coordinates out of the.
SQLite Read-Only Mode: Open Databases Safely
Opening an SQLite database in read-only mode can prevent accidental data corruption, but it might surprise you how many ways a read-only file can still .
SQLite Schema Versioning: Track and Apply Migrations
The most surprising thing about SQLite schema versioning is that it's entirely a convention; SQLite itself doesn't know or care about "versions" or "mig.
SQLite at the Edge: Serverless and Edge Deployment Patterns
SQLite at the edge is less about running a traditional database server and more about embedding a transactional data store directly into the application.
SQLite Shared Cache Mode: Share Cache Across Connections
SQLite's shared-cache mode is the surprising exception to the rule that database connections are strictly isolated, allowing them to pool resources and .
SQLite STRICT Tables: Enforce Column Types
SQLite STRICT tables are a game-changer for data integrity, but the most surprising thing is how they still let you shoot yourself in the foot, just dif.
SQLite synchronous PRAGMA: Balance Safety and Speed
SQLite's PRAGMA synchronous setting is the most common knob you'll twist to balance data safety against write performance.
SQLite Thread Safety Modes: Serialized, Multi-Thread, Single
SQLite's thread safety modes are less about how many threads can access the database, and more about how safely they can do it.
SQLite Triggers: Automate Actions on Table Changes
SQLite Triggers: Automate Actions on Table Changes — practical guide covering sqlite setup, configuration, and troubleshooting with real-world examples.
SQLite UPSERT: INSERT OR REPLACE and ON CONFLICT
INSERT OR REPLACE and ON CONFLICT are two ways to achieve an "upsert" insert or update in SQLite, but they operate quite differently under the hood, and.
SQLite VACUUM and ANALYZE: Maintain Database Health
VACUUM and ANALYZE are two essential maintenance commands for SQLite databases, often overlooked but critical for optimal performance and efficient stor.
SQLite Virtual Tables: Build Custom Table Extensions
Virtual tables let you create table-like interfaces that don't store data in the usual way, opening up a world of possibilities beyond simple file-backe.
SQLite vs DuckDB: Choose for Analytical Workloads
DuckDB is a "data frame in C++" masquerading as a SQL database, and that's why it blows SQLite out of the water for analytical queries.
SQLite vs PostgreSQL: When to Use Each Database
SQLite vs PostgreSQL: When to Use Each Database — practical guide covering sqlite setup, configuration, and troubleshooting with real-world examples.
SQLite WAL Mode Explained: Concurrent Reads and Writes
SQLite's Write-Ahead Logging WAL mode allows readers to access the database while a writer is actively modifying it, a feat previously impossible with i.
SQLite in the Browser with WASM: Setup and Use Cases
You can run a full relational database, complete with ACID compliance and SQL, directly in the user's browser using SQLite compiled to WebAssembly.
SQLite Window Functions: Analytics Without a Separate DB
Window functions in SQLite are a surprising way to perform complex analytical queries directly on your data without needing a dedicated data warehouse o.
SQLite WITHOUT ROWID Tables: Clustered Index Optimization
SQLite's WITHOUT ROWID tables are a performance optimization that eliminates the hidden rowid column, allowing the primary key to act as the clustered i.
Fix SQLite SQLITE_RANGE: Column Index Out of Range
Fix SQLite SQLITE_RANGE: Column Index Out of Range — practical guide covering sqlite setup, configuration, and troubleshooting with real-world examples.
SQLite Custom Aggregate Functions: Build Your Own
SQLite Custom Aggregate Functions: Build Your Own. SQLite lets you define custom aggregate functions, which is way cooler than just using SUM or AVG.
SQLite Application ID Pragma: Identify Your File Format
The applicationid pragma in SQLite is actually a way to embed a small, user-defined integer into your database file itself, acting as a tiny, portable i.
SQLite ATTACH DATABASE: Query Multiple Database Files
SQLite's ATTACH DATABASE command lets you treat multiple independent database files as if they were a single, unified database.
SQLite AUTOINCREMENT vs ROWID: Choose the Right Primary Key
SQLite's AUTOINCREMENT keyword is a bit of a red herring; most of the time, you don't need it and ROWID is doing all the heavy lifting anyway.
SQLite Online Backup API: Hot Backup Without Locking
The SQLite Online Backup API lets you copy a live database file without taking a write lock, which is pretty wild when you think about it.
SQLite Batch Insert Performance: Transactions and PRAGMA
SQLite's batch insert performance is often a surprise because it's not just about how many rows you throw at it, but how you throw them.
SQLite Concurrent Writes: WAL Mode and Lock Handling
SQLite's default write locking mechanism is a performance bottleneck in concurrent environments because it locks the entire database file for writes, pr.
SQLite Connection String Options: URI and PRAGMA Config
SQLite's connection string can be a lot more than just a file path; it's a powerful configurator that lets you tune its behavior at runtime.
SQLite Cross-Platform Path Handling: Windows and POSIX
SQLite, despite its embedded nature, doesn't magically abstract away filesystem differences; it relies on the underlying OS's path handling, which is a .
SQLite CSV Import and Export: .import and .mode csv
SQLite CSV Import and Export: .import and .mode csv — The .import command in SQLite doesn't actually parse CSV. It treats CSV files as simple, delimited t.
SQLite CTEs and Recursive Queries: Hierarchical Data
Common Table Expressions CTEs and recursive queries are the standard SQL way to traverse and manipulate hierarchical data, but their power lies in under.
SQLite Date and Time Storage: TEXT, INTEGER, REAL Formats
SQLite stores date and time values using one of three formats: TEXT, INTEGER, or REAL. Here's how you might see that in action
SQLite Encryption with SQLCipher: Secure Your Database
SQLite Encryption with SQLCipher: Secure Your Database — SQLite databases, by default, are plain text files. Anyone with file system access can read the...
Read SQLite EXPLAIN QUERY PLAN: Optimize Your Queries
EXPLAIN QUERY PLAN is your secret weapon for understanding why SQLite is choosing a particular execution path for your SQL statements, and more importan.
Fix SQLite File Size Growing Too Large: VACUUM and Limits
The database file is growing too large because SQLite keeps deleted data around, even though it's no longer visible to your application.
SQLite Foreign Key Enforcement: Enable and Validate
SQLite doesn't enforce foreign key constraints by default, which means your database can easily end up in a state with orphaned records or invalid refer.
SQLite FTS5 Full-Text Search: Index and Query Text
SQLite's FTS5 module is surprisingly powerful, but its core strength lies not in speeding up searches, but in how it represents text to make searching p.
SQLite In-Memory Database: Fast Ephemeral Data Store
An SQLite in-memory database lets you run an entire database entirely in RAM, discarding data when the connection closes.
SQLite Index Bloat: Rebuild Fragmented Indexes
SQLite Index Bloat: Rebuild Fragmented Indexes — practical guide covering sqlite setup, configuration, and troubleshooting with real-world examples.
SQLite Index Optimization: Cover Queries with Right Indexes
The most surprising thing about SQLite indexes is that they don't just speed up lookups; they actively prevent certain types of expensive operations fro.