What Is SQLite? The Database That Runs Inside Your App
SQLite is a self-contained database that runs without a server. Here's what it is, how it works, and when it's the right choice for your app.
A Database That Lives in a File
Most databases need a server. You install Postgres or MySQL, configure it, manage connections, keep it running. SQLite does none of that. It’s a database engine that runs inside your application, stores everything in a single file on disk, and requires zero configuration to get started.
SQLite is probably already running on your devices right now. It’s inside iOS, Android, macOS, Windows, Firefox, Chrome, and thousands of other apps. It’s one of the most widely deployed pieces of software in existence — estimates put it on over a trillion devices worldwide. And yet it’s often misunderstood, either dismissed as a “toy” database or treated as a niche embedded option when it’s actually a solid choice for a wide range of production applications.
This article explains what SQLite is, how it works under the hood, when it makes sense to use it, and when it doesn’t. If you’re building an app and trying to figure out your database layer, this is worth reading before you default to something more complex.
How SQLite Actually Works
No server, no daemon
Traditional databases like PostgreSQL and MySQL run as separate server processes. Your application talks to them over a network connection or a local socket. There’s a whole process of connecting, authenticating, sending queries, and receiving results.
SQLite is different. The database engine is a library that gets compiled directly into your application (or loaded as a shared library). When your app wants to read or write data, it calls SQLite functions directly. There’s no network hop, no connection pool, no server process to manage.
The entire database — tables, indexes, data — lives in a single .sqlite or .db file on disk. You can copy it, email it, back it up with cp. That’s the whole database.
SQL that’s fully standards-compliant
Despite being embedded and serverless, SQLite speaks real SQL. You get SELECT, INSERT, UPDATE, DELETE, JOIN, subqueries, GROUP BY, window functions, full-text search via FTS5, JSON support, and more. It’s not a simplified dialect. Most SQL you’d write for Postgres will run in SQLite with minor or no changes.
SQLite enforces ACID transactions: Atomicity, Consistency, Isolation, Durability. Writes are durable. Transactions either complete fully or roll back. You’re not trading reliability for simplicity.
WAL mode changes the concurrency story
One criticism of SQLite that was valid for years is that it doesn’t handle concurrent writes well. The default journal mode uses exclusive locks, which means only one writer at a time.
WAL mode (Write-Ahead Logging) changes this significantly. In WAL mode, readers and writers don’t block each other. Multiple readers can access the database at the same time a writer is active. This is why modern SQLite deployments — including Remy’s — default to WAL mode. It’s much better for real-world application workloads.
The remaining limitation: only one writer at a time. If you have dozens of concurrent writes per second, you’ll hit contention. For most apps, this isn’t a practical problem.
What Makes SQLite Different From Other Databases
Understanding where SQLite sits relative to other options helps you make a better choice for your project. If you’re still getting your bearings on databases in general, the article on what a backend is and how it works is a good place to start.
SQLite vs PostgreSQL
PostgreSQL is a full client-server relational database. It handles massive concurrent workloads, complex queries, and terabytes of data. It requires a server, connection management, and ongoing administration. It’s the right choice for high-traffic applications with many simultaneous users all writing to the database.
SQLite is better when you want zero operational overhead and your workload fits its concurrency model.
SQLite vs MySQL / MariaDB
Similar tradeoff. MySQL is client-server, handles concurrent connections well, and needs a server process. If you’re deploying to shared hosting or need compatibility with legacy systems, MySQL might be what you use. But for greenfield apps, there’s often no reason to choose MySQL over Postgres — and SQLite is worth considering before either.
SQLite vs MongoDB
MongoDB is a document database — you store JSON-like documents rather than rows in tables. It has different consistency guarantees and a different query model. SQLite stores relational data in tables with strict schemas. They solve different problems. If you want schemaless or document storage, MongoDB is in scope; if you want relational data with SQL, SQLite (or Postgres) makes more sense.
The choice of database also intersects with your database schema design — SQLite enforces a typed, structured schema, which is a feature, not a limitation.
What SQLite Is Good At
Local and embedded applications
SQLite was designed for local-first applications. Desktop apps, mobile apps, CLI tools, offline-capable web apps — anywhere the database lives alongside the application rather than on a separate server. This is why every smartphone and laptop has SQLite built in.
Development and testing
Even if you plan to deploy with Postgres in production, using SQLite in development is common. It’s fast to set up, easy to reset, and doesn’t require running a local database server. Many testing frameworks default to SQLite for exactly this reason.
Small to medium web applications
The “SQLite is only for small apps” take is outdated. With WAL mode, proper indexing, and modern hardware (NVMe SSDs), a single SQLite database on a capable server can handle tens of thousands of requests per second for typical read-heavy workloads.
The SQLite documentation itself says it’s appropriate for “any site that gets fewer than 100,000 hits per day.” That covers the vast majority of web applications that will ever exist, including many production SaaS apps.
Single-instance deployments
SQLite doesn’t support replication natively. If you need to run multiple application servers all writing to the same database simultaneously, you need a client-server database or a SQLite extension like LiteFS or Turso’s libSQL that handles replication.
For a single server deployment — which is how most apps start — SQLite is a perfectly reasonable choice.
Data files and embedded analytics
Because the database is a single file, SQLite works well for distributing datasets. You can ship a .db file alongside your application that contains reference data, lookup tables, or cached results. There’s no import step. Just open the file.
What SQLite Is Not Good At
High write concurrency
If your application has many simultaneous writers — think thousands of concurrent users all writing at the same time — SQLite’s single-writer model will be a bottleneck. Write operations queue up. Under heavy write load, you’ll see increased latency and potential timeouts.
This is the most common reason to move from SQLite to a client-server database as an application scales.
Distributed deployments
Running your app on multiple servers means multiple SQLite files with no automatic synchronization. This is solvable with extensions and services (Turso, LiteFS), but out of the box, SQLite doesn’t support multi-primary replication. If you’re thinking about how to set up a managed database for your web app at scale, you’ll want to understand this constraint early.
Very large datasets
SQLite supports databases up to 281 terabytes in theory, but in practice, performance degrades at very large scales. Complex queries over tens of millions of rows can be slow without careful indexing. For large analytics workloads, dedicated tools like DuckDB or a columnar data warehouse are more appropriate.
Networked access
You can’t connect to SQLite from multiple separate processes the way you connect to Postgres. If you need multiple apps or services to share the same database, SQLite is the wrong tool. A client-server database that exposes a connection interface (and ideally a REST API layer on top) is the right approach.
SQLite in Modern App Development
SQLite has had a quiet resurgence among developers building real production applications. Several factors drive this:
Edge computing. Platforms like Cloudflare D1 run SQLite at edge locations globally. This brings database reads physically close to users, reducing latency. D1 is built on SQLite because it’s embeddable and doesn’t require a persistent server process.
The “local-first” movement. Applications that store data locally and sync to the cloud are gaining traction. SQLite is often at the center of these architectures because it’s fast, reliable, and operates entirely on-device.
Serverless and containerized deployments. When you’re running short-lived containers or serverless functions, spinning up a database server is inconvenient. SQLite running inside the container avoids that entirely.
Better tooling. Libraries like Drizzle ORM, Prisma, and Bun’s built-in SQLite driver have made working with SQLite in TypeScript-based backends much smoother than it was five years ago. If you’re doing full-stack TypeScript development, SQLite is well-supported across the stack.
SQLite and Schema Migrations
One operational detail that trips people up: SQLite handles schema changes differently from Postgres.
ALTER TABLE in SQLite is limited. You can add columns and rename tables, but you can’t drop columns (before version 3.35.0) or change column types. Complex schema changes often require creating a new table, copying data, and renaming.
Modern ORMs and migration tools handle this for you. Drizzle and Prisma both support SQLite migrations that use the create-copy-drop-rename pattern automatically. You don’t have to do this manually, but you should understand what’s happening underneath.
Automatic schema migrations on deploy — like what Remy handles — are especially useful for SQLite because they abstract away this complexity entirely.
How Remy Uses SQLite
Remy uses SQLite (in WAL/journalled mode) as the default database for every app it builds. This is a deliberate choice, not a shortcut.
For the apps Remy targets — full-stack web applications, internal tools, SaaS MVPs — SQLite covers the vast majority of real workloads without any operational complexity. There’s no database server to provision, no connection pool to configure, no network latency between the app and the database. The database lives with the application.
Remy also handles automatic schema migrations on deploy. When your database schema changes — new tables, new columns, changed relationships — the migration runs automatically when you push to main. You don’t manage migration files manually.
This connects directly to the core idea behind Remy: the spec is the source of truth. You describe your application’s data model in the spec document. Remy derives the database schema from that. When the spec changes, the schema follows. The database is part of the compiled output, not something you manage separately.
If you’re building apps with AI features built in — user data, conversation history, structured outputs — SQLite backed by Remy’s automatic migrations means you get a real, persistent database without spending time on infrastructure setup.
You can see this in action at mindstudio.ai/remy.
SQLite vs Managed Database Services
When you’re choosing a database for a new project, you’re often choosing between running your own (SQLite on the server, Postgres you manage yourself) and using a managed service. Options like Supabase or PlanetScale handle the infrastructure for you.
Here’s a practical comparison:
| SQLite | Managed Postgres (Supabase, etc.) | |
|---|---|---|
| Setup time | Seconds | Minutes to hours |
| Operational overhead | Zero | Low (still need to configure) |
| Concurrent writes | Limited (single writer) | High |
| Replication | Not built-in | Included |
| Cost | Free (file storage only) | Free tier, then paid |
| Best for | Single-server apps, early-stage, embedded | Multi-server, high concurrency, teams |
Neither is universally better. The right choice depends on your workload and stage.
For a brand-new app with unknown traffic, SQLite is a reasonable starting point. You can always migrate to Postgres when the need is clear. Migration is work, but it’s the kind of work you want to have — it means your app has enough users to need it.
For a team building a SaaS product from day one, a managed Postgres service like Supabase gives you connection-level auth, row-level security, and automatic backups. That overhead pays off at scale. If you’re thinking through how to choose a backend for your app, the database choice is part of that decision.
A Note on AI Agents and SQLite
AI agents increasingly need persistent storage — conversation history, user preferences, retrieved documents, structured outputs from multi-step tasks. SQLite is often the right storage layer here because the agent and database run in the same process, queries are fast, and there’s nothing to configure.
That said, agents that can write to databases at scale introduce real risk. The 1.9 million row database wipe incident is a sobering reminder that any agent with write access needs carefully scoped permissions and checkpoints. This applies regardless of whether the underlying database is SQLite or Postgres.
For AI agents that need semantic search over stored content — not just structured queries — a vector database is a different tool for a different job. SQLite handles structured relational data well. It doesn’t replace vector search.
Frequently Asked Questions
Is SQLite a real database or just for prototypes?
SQLite is a real database with full ACID transactions, real SQL support, and proven reliability at scale. It runs inside browsers, operating systems, and production applications serving millions of users. “Just for prototypes” is a common misconception driven by old benchmarks and the single-writer limitation. With WAL mode and modern hardware, SQLite handles production workloads for the vast majority of web applications.
Can SQLite handle multiple users at the same time?
Yes, with some nuance. Multiple users can read simultaneously without contention. Writes are serialized — only one write operation happens at a time. In practice, writes complete in milliseconds, so queuing rarely causes visible problems unless you have extreme write concurrency. If you’re building a high-traffic application where many users are writing simultaneously (think real-time collaborative editing or financial transactions at volume), a client-server database like Postgres handles that better.
What happens if two processes try to write to SQLite at once?
SQLite uses file-level locking to prevent corruption. In WAL mode, readers and writers can operate concurrently, but only one writer holds the write lock at a time. If a second process tries to write while another write is in progress, it either waits (if the timeout is set) or returns a SQLITE_BUSY error. Most application code handles this with a retry loop. The SQLite documentation provides detailed guidance on this behavior.
How big can a SQLite database get?
The theoretical maximum is 281 terabytes. In practice, SQLite works well up to a few gigabytes for typical application workloads. Beyond that, you’ll want to pay close attention to indexing strategy. Very large databases (hundreds of GB) are possible but unusual and benefit from specialized tooling.
Is SQLite safe to use in production?
Yes. SQLite is used in production by Apple, Google, Mozilla, Airbus, and countless others. It’s one of the most thoroughly tested pieces of software in existence, with a test suite that covers an enormous range of edge cases. The SQLite source code has more test code than implementation code by a significant margin. The bigger risk in production is usually misconfiguration (not using WAL mode, no backups) rather than SQLite itself being unreliable.
Can I use SQLite with an ORM?
Yes. Drizzle ORM, Prisma, TypeORM, and Sequelize all support SQLite. Most modern ORMs handle SQLite’s schema migration quirks automatically. If you’re building a TypeScript backend, Drizzle in particular has excellent SQLite support with a lightweight, explicit API that works well for greenfield applications.
Key Takeaways
- SQLite is a self-contained, serverless database engine that runs inside your application and stores everything in a single file.
- It supports real SQL, ACID transactions, and — in WAL mode — concurrent reads with single-writer semantics.
- It’s well-suited for local apps, development environments, single-server web apps, and embedded use cases.
- The main limitations are write concurrency and the lack of built-in replication — both matter at scale, but not for most early-stage apps.
- Modern tooling (Drizzle, Prisma, edge platforms like Cloudflare D1) has made SQLite a practical production choice, not just a development shortcut.
- Remy uses SQLite by default for every app it builds, with automatic schema migrations on deploy and WAL mode enabled.
If you’re building a full-stack app and want a database that just works without standing up infrastructure, try Remy at mindstudio.ai/remy.
