SQLite vs MySQL: Differences, Tested Examples and When to Use Each

Ninad Pathak

October 7, 2026

SQLite and MySQL both speak SQL, but SQLite is a file your program opens and MySQL is a server your programs connect to. The SQL differs too, in ways that break code when it moves from one to the other. In this article I’ll walk you through those differences, with the same statements run on both, so you can pick the right one.

TLDR

SQLite is a library inside your program, with the whole database in one file. MySQL is a database server that many clients connect to over a network.
Choose SQLite when the database sits on the same machine as the code and writes can take turns.

Choose MySQL when many clients write at the same moment, or when you need user accounts and permissions.
Moving between them means rewriting keys and upserts. Loosely typed data also needs cleaning up first.

What is SQLite?

SQLite is a relational database engine that your program links in, with the whole database stored in one ordinary file. The SQLite project puts it this way: “SQLite does not compete with client/server databases. SQLite competes with fopen()” (Appropriate Uses For SQLite).

What is MySQL?

MySQL is a client/server database: “a multithreaded SQL server” plus client programs and libraries (What is MySQL?). You install and run the server, then connect to it from your application with an account.

SQLite vs MySQL at a glance

The differences that decide the choice:

	SQLite	MySQL
Architecture	Library inside your program; no server process (SQLite is serverless)	Client/server (What is MySQL?)
Storage	One ordinary file (plus temporary journal or WAL files)	Managed by the server in its data directory
Typing	Flexible by default; `STRICT` tables enforce types	Column types enforced by the default SQL mode
Concurrent writes	One writer at a time per database file	Many concurrent writers, coordinated by the server
Users and permissions	None in SQL; only file permissions	User accounts with `GRANT` and `REVOKE`
Licence	Public domain (SQLite is public domain)	GPL, or a commercial licence from Oracle

Differences that matter

To show you the differences, I will run each pair of statements on SQLite and on MySQL with its default settings. Lines starting with sqlite> or mysql> show the statement, and the lines after them are its output. There are no speed benchmarks, because results depend on your hardware and workload.

Column types: flexible in SQLite, enforced in MySQL

SQLite uses “a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container” (Datatypes in SQLite). So an INTEGER column accepts a string:

sqlite> CREATE TABLE items (id INTEGER PRIMARY KEY, qty INTEGER);
sqlite> INSERT INTO items (qty) VALUES ('ten');
sqlite> SELECT id, qty, typeof(qty) FROM items;
id  qty  typeof(qty)
--  ---  -----------
1   ten  text

A STRICT table (SQLite 3.37.0 and later, STRICT tables) rejects it, which is what MySQL does by default:

sqlite> CREATE TABLE items_strict (id INTEGER PRIMARY KEY, qty INTEGER) STRICT;
sqlite> INSERT INTO items_strict (qty) VALUES ('ten');
Error: stepping, cannot store TEXT value in INTEGER column items_strict.qty (19)

mysql> CREATE TABLE items (id INT AUTO_INCREMENT PRIMARY KEY, qty INT);
mysql> INSERT INTO items (qty) VALUES ('ten');
ERROR 1366 (HY000) at line 1: Incorrect integer value: 'ten' for column 'qty' at row 1

MySQL rejects the value because its default SQL mode includes STRICT_TRANS_TABLES (MySQL server SQL modes). These are the defaults on the test server:

mysql> SELECT @@GLOBAL.sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+

Auto-increment IDs

In SQLite, a column declared INTEGER PRIMARY KEY gets the next ID automatically. MySQL needs AUTO_INCREMENT:

sqlite> CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, logins INTEGER NOT NULL DEFAULT 0);
sqlite> INSERT INTO users (name) VALUES ('ada'), ('linus'); SELECT * FROM users;
id  name   logins
--  -----  ------
1   ada    0
2   linus  0

mysql> CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL UNIQUE, logins INT NOT NULL DEFAULT 0);
mysql> INSERT INTO users (name) VALUES ('ada'), ('linus'); SELECT * FROM users;
+----+-------+--------+
| id | name  | logins |
+----+-------+--------+
|  1 | ada   |      0 |
|  2 | linus |      0 |
+----+-------+--------+

Upsert: ON CONFLICT vs ON DUPLICATE KEY UPDATE

Both can insert a row or update it if the key already exists, with different syntax. The user ada already exists here, so each statement adds one to logins:

sqlite> INSERT INTO users (name, logins) VALUES ('ada', 1) ON CONFLICT(name) DO UPDATE SET logins = logins + 1; SELECT * FROM users WHERE name = 'ada';
id  name  logins
--  ----  ------
1   ada   1

mysql> INSERT INTO users (name, logins) VALUES ('ada', 1) AS new ON DUPLICATE KEY UPDATE logins = users.logins + 1; SELECT * FROM users WHERE name = 'ada';
+----+------+--------+
| id | name | logins |
+----+------+--------+
|  1 | ada  |      1 |
+----+------+--------+

The AS new row alias is the current MySQL form (INSERT … ON DUPLICATE KEY UPDATE). More on the SQLite form in SQLite UPSERT.

Booleans

Neither database has a separate boolean storage type. SQLite stores TRUE and FALSE as 1 and 0, and a BOOLEAN column still accepts any value (Datatypes in SQLite):

sqlite> CREATE TABLE flags (id INTEGER PRIMARY KEY, active BOOLEAN); INSERT INTO flags (active) VALUES (TRUE), (FALSE), ('yes'); SELECT id, active, typeof(active) FROM flags;
id  active  typeof(active)
--  ------  --------------
1   1       integer
2   0       integer
3   yes     text

In MySQL, BOOLEAN becomes TINYINT(1) (MySQL numeric types):

mysql> CREATE TABLE flags (id INT AUTO_INCREMENT PRIMARY KEY, active BOOLEAN); INSERT INTO flags (active) VALUES (TRUE), (FALSE);
mysql> SELECT COLUMN_NAME, COLUMN_TYPE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'demo' AND TABLE_NAME = 'flags' ORDER BY ORDINAL_POSITION;
+-------------+-------------+
| COLUMN_NAME | COLUMN_TYPE |
+-------------+-------------+
| id          | int         |
| active      | tinyint(1)  |
+-------------+-------------+
mysql> SELECT id, active FROM flags;
+----+--------+
| id | active |
+----+--------+
|  1 |      1 |
|  2 |      0 |
+----+--------+

Dates

SQLite has no date type (Datatypes in SQLite), so it stores the impossible date 30 February as text. Its date() function then rolls it forward to 2 March. MySQL refuses the value:

sqlite> CREATE TABLE events (id INTEGER PRIMARY KEY, happened_on DATE); INSERT INTO events (happened_on) VALUES ('2026-02-30'); SELECT id, happened_on, date(happened_on) AS normalised FROM events;
id  happened_on  normalised
--  -----------  ----------
1   2026-02-30   2026-03-02

mysql> CREATE TABLE events (id INT AUTO_INCREMENT PRIMARY KEY, happened_on DATE); INSERT INTO events (happened_on) VALUES ('2026-02-30');
ERROR 1292 (22007) at line 1: Incorrect date value: '2026-02-30' for column 'happened_on' at row 1

See SQLite date and time functions for storing dates safely.

String concatenation

In SQLite, || joins strings. In MySQL’s default mode, || means logical OR, so the same query returns 0 with warnings. Use CONCAT() in MySQL:

sqlite> SELECT 'sql' || 'docs' AS joined;
joined
-------
sqldocs

mysql> SELECT 'sql' || 'docs' AS joined; SHOW WARNINGS;
+--------+
| joined |
+--------+
|      0 |
+--------+
+---------+------+-------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                               |
+---------+------+-------------------------------------------------------------------------------------------------------+
| Warning | 1287 | '|| as a synonym for OR' is deprecated and will be removed in a future release. Please use OR instead |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'sql'                                                               |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'docs'                                                              |
+---------+------+-------------------------------------------------------------------------------------------------------+
mysql> SELECT CONCAT('sql', 'docs') AS joined;
+---------+
| joined  |
+---------+
| sqldocs |
+---------+

Users and permissions

MySQL has user accounts, and GRANT assigns privileges at levels from the whole server down to single columns (MySQL GRANT). I created a read-only user, and MySQL refused its DELETE:

mysql> CREATE USER IF NOT EXISTS 'reporter'@'%' IDENTIFIED BY 'report-pass'; GRANT SELECT ON demo.* TO 'reporter'@'%';
mysql> SHOW GRANTS FOR 'reporter'@'%';
+--------------------------------------------+
| Grants for reporter@%                      |
+--------------------------------------------+
| GRANT USAGE ON *.* TO `reporter`@`%`       |
| GRANT SELECT ON `demo`.* TO `reporter`@`%` |
+--------------------------------------------+
mysql (as reporter)> DELETE FROM flags;
ERROR 1142 (42000) at line 1: DELETE command denied to user 'reporter'@'localhost' for table 'flags'
mysql> DROP USER 'reporter'@'%';

SQLite has no GRANT at all: “Since SQLite reads and writes an ordinary disk file, the only access permissions that can be applied are the normal file access permissions of the underlying operating system” (SQL features SQLite does not implement). Anyone who can read the file can read all of it:

sqlite> GRANT SELECT ON flags TO reporter;
Error: in prepare, near "GRANT": syntax error
  GRANT SELECT ON flags TO reporter;
  ^--- error here

Concurrency

A MySQL server coordinates many clients reading and writing at once. SQLite allows one writer at a time per database file. In WAL mode, “readers do not block writers and a writer does not block readers”, but “there can only be one writer at a time” (Write-Ahead Logging). SQLite’s documentation adds that SQLite “will handle more write concurrency than many people suspect” (Appropriate Uses For SQLite). If writers wait too long, see database is locked and SQLite WAL mode.

Size limits

Since SQLite 3.45.0, a database can hold up to 4,294,967,294 pages by default. With the default page size of 4,096 bytes, that’s about 17.5 TB. With the maximum page size of 65,536 bytes, it’s about 281 TB (Limits in SQLite). In MySQL, InnoDB defines a 64 TiB limit per table or tablespace, which the file system can lower: ext4’s 16 TiB file limit makes it 16 TiB (InnoDB limits).

Licensing

SQLite’s code “has been dedicated to the public domain by the authors” (SQLite is public domain). MySQL uses the GPL, and Oracle sells a commercial licence for cases where the GPL doesn’t fit, such as embedding MySQL in a commercial product (What is MySQL?).

When to use SQLite and when to use MySQL

SQLite’s own documentation (Appropriate Uses For SQLite) gives a short checklist. Choose a client/server database such as MySQL when any of these is true:

The data is separated from the application by a network. A database engine filters a lot of disk data down to small results, so it should sit next to the disk.
Many concurrent writers. “SQLite only supports one writer at a time per database file. But in most cases, a write transaction only takes milliseconds and so multiple writers can simply take turns.”

Big data. “When the size of the content looks like it might creep into the terabyte range, it would be good to consider a centralized client/server database.”

Otherwise, for device-local storage with low writer concurrency, the same page recommends SQLite. That covers apps and tools that own their data, and websites whose application server sits on the same machine as the database file.

If you outgrow SQLite

The differences above make up the porting checklist:

Replace INTEGER PRIMARY KEY with INT AUTO_INCREMENT PRIMARY KEY (or BIGINT).

Find rows whose values don’t match their column type before importing, because MySQL will reject them. In SQLite, typeof() finds them:

sqlite> SELECT id, qty FROM items WHERE typeof(qty) <> 'integer';
id  qty
--  ---
1   ten

Check stored dates with SQLite’s date functions, because MySQL rejects impossible dates.
Rewrite ON CONFLICT … DO UPDATE as ON DUPLICATE KEY UPDATE, and || as CONCAT().

Create MySQL accounts with only the privileges each application needs.

Conclusion

SQLite and MySQL share the SQL basics but not the setup. SQLite is a file your program opens, and MySQL is a server your programs connect to. Start with SQLite when one machine owns the data. Move to MySQL when many clients need to write at once or need their own accounts.