MySQL vs SQLite — When You Need a Database vs a File
MySQL is a full server for apps; SQLite is a file you drop in. Pick wrong and you'll hate your life.
MySQL
MySQL scales beyond a single file and handles concurrent users without melting down. SQLite is brilliant until you need more than one person writing at once.
Architecture: Server vs Embedded
MySQL runs as a separate server process—clients connect over the network, it handles multiple connections, and you can scale it across machines. SQLite is embedded: the entire database is a single file on disk, with no server process. This means SQLite is zero-configuration (just include the library), but it can't handle high concurrency because every write locks the entire database file. MySQL's client-server model lets you throw it on a cloud VM and scale vertically or horizontally; SQLite is stuck on one machine.
Concurrency and Performance
MySQL supports multiple concurrent writes with row-level locking in InnoDB (its default storage engine), making it suitable for web apps with many users. SQLite uses a file-level lock: only one write operation can occur at a time, which bottlenecks under heavy load. For read-heavy apps (like a blog or local tool), SQLite is fast and efficient, but try to run a SaaS with 100 users and you'll hit a wall. MySQL's performance scales with hardware; SQLite's is limited by disk I/O and locking.
Setup and Deployment
SQLite wins on simplicity: no installation, no configuration—just include the SQLite library in your app and point it to a file. It's perfect for mobile apps (Android/iOS use it heavily), desktop software, or prototyping. MySQL requires setting up a server, managing users, and configuring ports (default 3306). It's more work upfront, but necessary for production environments. If you're building a quick script or a single-user app, SQLite's ease is unbeatable; for anything multi-user, MySQL's setup is non-negotiable.
Features and SQL Support
MySQL offers advanced features like stored procedures, triggers, views, and replication—tools for complex applications. It supports ACID compliance with InnoDB, ensuring data integrity. SQLite supports a subset of SQL (no ALTER TABLE for some operations, limited foreign key enforcement unless enabled) and lacks stored procedures. It's ACID-compliant too, but simpler. For example, if you need to replicate data across servers, MySQL has built-in solutions; SQLite requires manual file copying, which is error-prone.
Pricing and Licensing
Both are open-source and free, but with caveats. MySQL is under GPL, meaning if you modify and distribute it, you must open-source your changes—Oracle offers commercial licenses for proprietary use. SQLite is public domain (no license restrictions), making it ideal for commercial products without legal hassles. However, MySQL has paid enterprise editions with extra features like advanced security and support; SQLite is purely community-driven. Cost-wise, they're both $0, but MySQL might incur hosting fees for servers.
Use Cases: Where Each Shines
Use SQLite for embedded systems (like IoT devices), mobile apps (iOS/Android), local caching, or small tools where you don't want a database server. It's the go-to for simplicity. Use MySQL for web applications (e.g., WordPress, e-commerce sites), multi-user systems, or when you need scalability and high availability. If you're building a prototype, start with SQLite to move fast, but plan to migrate to MySQL before launch. Ignore this and you'll regret it when users complain about slow writes.
Quick Comparison
| Factor | Mysql | Sqlite |
|---|---|---|
| Concurrent Writes | Supports multiple with row-level locking | Single write at a time (file lock) |
| Setup Complexity | Requires server installation and config | Zero-config, embed as library |
| Scalability | Scales vertically/horizontally | Limited to single machine/file |
| SQL Features | Full SQL with stored procedures, triggers | Subset, no stored procedures |
| Licensing | GPL (commercial options available) | Public domain (no restrictions) |
| Best For | Web apps, multi-user systems | Mobile apps, local tools |
| Default Port | 3306 | N/A (file-based) |
| ACID Compliance | Yes (with InnoDB) | Yes |
The Verdict
Use Mysql if: You're building a web app with multiple users or need to scale beyond a single server.
Use Sqlite if: You're developing a mobile app, embedded system, or a simple local tool without concurrency needs.
Consider: PostgreSQL if you need advanced features like JSON support or stricter ACID compliance—it's like MySQL but with more bells and whistles.
MySQL scales beyond a single file and handles concurrent users without melting down. SQLite is brilliant until you need more than one person writing at once.
Related Comparisons
Disagree? nice@nicepick.dev