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

Ninad Pathak

October 7, 2026

Should your app’s data live in SQLite or MariaDB? SQLite is a database inside your program and MariaDB is a separate server, so the choice comes down to whether you want a server. Home Assistant, for one, defaults to SQLite and offers MariaDB as the alternative. In this article I’ll walk you through how SQLite and MariaDB differ, with the same statements run on both, and when each one is the better pick.

TLDR

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

Choose MariaDB when several servers share the data and write to it at the same time, or when you need accounts and permissions.
MariaDB’s upsert differs from MySQL 8’s: it uses VALUES(column), not the AS new alias.

What is SQLite?

SQLite is a relational database engine that runs inside your program, with the database stored in one file. SQLite’s own guidance: “SQLite does not compete with client/server databases. SQLite competes with fopen()” (Appropriate Uses For SQLite).

What is MariaDB?

MariaDB is a database server, a fork of MySQL “made by the original developers of MySQL and guaranteed to stay open source” (About MariaDB Server). Like MySQL, it runs as a server that many clients connect to over a network. Its tables use a storage engine, and the default in this test was InnoDB:

mariadb> CREATE TABLE engine_demo (id INT PRIMARY KEY); SELECT @@default_storage_engine AS default_engine, ENGINE AS table_engine FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'demo' AND TABLE_NAME = 'engine_demo'; DROP TABLE engine_demo;
+----------------+--------------+
| default_engine | table_engine |
+----------------+--------------+
| InnoDB         | InnoDB       |
+----------------+--------------+

SQLite vs MariaDB at a glance

The differences that decide the choice:

	SQLite	MariaDB
Architecture	Library inside your program; no server process (SQLite is serverless)	Database server; a fork of MySQL
Typing	Flexible by default; `STRICT` tables enforce types	Enforced; the default SQL mode is strict (shown below)
Concurrent writes	One writer at a time per database file	Many concurrent writers, coordinated by the server
Users and permissions	None in SQL; file permissions only (SQL features SQLite does not implement)	Accounts with `GRANT` and `REVOKE`
JSON	Text plus JSON functions	`JSON` is an alias for `LONGTEXT` with an automatic `JSON_VALID` check
Licence	Public domain (SQLite is public domain)	GPLv2 (About MariaDB Server)

Differences that matter

To show you the differences, I will run each pair of statements on SQLite and on MariaDB with its default settings. Lines starting with sqlite> or mariadb> show the statement, and the lines after them are its output. When a MariaDB statement fails, the client echoes it between dashed lines before the error. There are no speed benchmarks.

Column types

SQLite stores the type with each value, so an INTEGER column accepts text unless the table is STRICT (Datatypes in SQLite, STRICT tables):

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

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)

MariaDB rejects the value, because its default SQL mode is strict:

mariadb> CREATE TABLE items (id INT AUTO_INCREMENT PRIMARY KEY, qty INT);
mariadb> INSERT INTO items (qty) VALUES ('ten');
--------------
INSERT INTO items (qty) VALUES ('ten')
--------------

ERROR 1366 (22007) at line 1: Incorrect integer value: 'ten' for column `demo`.`items`.`qty` at row 1

mariadb> SELECT @@GLOBAL.sql_mode;
+-------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                         |
+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+

Auto-increment IDs

In SQLite, INTEGER PRIMARY KEY assigns IDs automatically. MariaDB uses 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

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

Upsert

SQLite uses ON CONFLICT … DO UPDATE:

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

MariaDB doesn’t accept MySQL 8’s AS new row alias. Use VALUES(column) to refer to the value you tried to insert (INSERT ON DUPLICATE KEY UPDATE):

mariadb> INSERT INTO users (name, logins) VALUES ('ada', 1) AS new ON DUPLICATE KEY UPDATE logins = users.logins + 1;
--------------
INSERT INTO users (name, logins) VALUES ('ada', 1) AS new ON DUPLICATE KEY UPDATE logins = users.logins + 1
--------------

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AS new ON DUPLICATE KEY UPDATE logins = users.logins + 1' at line 1
mariadb> INSERT INTO users (name, logins) VALUES ('ada', 1) ON DUPLICATE KEY UPDATE logins = logins + VALUES(logins); SELECT * FROM users WHERE name = 'ada';
+----+------+--------+
| id | name | logins |
+----+------+--------+
|  1 | ada  |      1 |
+----+------+--------+

Booleans

SQLite stores booleans as the integers 1 and 0 (Datatypes in SQLite). In MariaDB, BOOLEAN is a synonym for TINYINT(1) (MariaDB BOOLEAN):

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

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

Dates

SQLite stores the impossible date 30 February as text, and MariaDB rejects it:

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

mariadb> CREATE TABLE events (id INT AUTO_INCREMENT PRIMARY KEY, happened_on DATE); INSERT INTO events (happened_on) VALUES ('2026-02-30');
--------------
INSERT INTO events (happened_on) VALUES ('2026-02-30')
--------------

ERROR 1292 (22007) at line 1: Incorrect date value: '2026-02-30' for column `demo`.`events`.`happened_on` at row 1

String concatenation

SQLite joins strings with ||. In MariaDB’s default mode, use CONCAT():

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

mariadb> SELECT 'sql' || 'docs' AS joined; SHOW WARNINGS;
+--------+
| joined |
+--------+
|      0 |
+--------+
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'sql'  |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'docs' |
+---------+------+------------------------------------------+
mariadb> SELECT CONCAT('sql', 'docs') AS joined;
+---------+
| joined  |
+---------+
| sqldocs |
+---------+

RETURNING

Both return the inserted row (INSERT…RETURNING):

sqlite> INSERT INTO users (name) VALUES ('grace') RETURNING id, name;
id  name
--  -----
3   grace

mariadb> INSERT INTO users (name) VALUES ('grace') RETURNING id, name;
+----+-------+
| id | name  |
+----+-------+
|  4 | grace |
+----+-------+

MariaDB returned id 4, not 3. The earlier upsert updated ada’s row, but it still used up an auto-increment value. This separate test shows the counter moving from 3 to 4 without a new row:

mariadb> CREATE TABLE ai_t (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) UNIQUE, n INT DEFAULT 0); INSERT INTO ai_t (name) VALUES ('ada'), ('linus'); SELECT AUTO_INCREMENT AS next_id FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'demo' AND TABLE_NAME = 'ai_t';
+---------+
| next_id |
+---------+
|       3 |
+---------+
mariadb> INSERT INTO ai_t (name) VALUES ('ada') ON DUPLICATE KEY UPDATE n = n + 1; SELECT AUTO_INCREMENT AS next_id FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'demo' AND TABLE_NAME = 'ai_t'; DROP TABLE ai_t;
+---------+
| next_id |
+---------+
|       4 |
+---------+

JSON columns

In MariaDB, “JSON is an alias for LONGTEXT COLLATE utf8mb4_bin”, and a JSON_VALID check “is automatically included for types using the JSON alias” (MariaDB JSON):

mariadb> CREATE TABLE docs (id INT AUTO_INCREMENT PRIMARY KEY, body JSON);
mariadb> SELECT COLUMN_TYPE, DATA_TYPE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'demo' AND TABLE_NAME = 'docs' AND COLUMN_NAME = 'body';
+-------------+-----------+
| COLUMN_TYPE | DATA_TYPE |
+-------------+-----------+
| longtext    | longtext  |
+-------------+-----------+
mariadb> SELECT CHECK_CLAUSE FROM information_schema.CHECK_CONSTRAINTS WHERE TABLE_NAME = 'docs';
+--------------------+
| CHECK_CLAUSE       |
+--------------------+
| json_valid(`body`) |
+--------------------+
mariadb> INSERT INTO docs (body) VALUES ('{not json}');
--------------
INSERT INTO docs (body) VALUES ('{not json}')
--------------

ERROR 4025 (23000) at line 1: CONSTRAINT `docs.body` failed for `demo`.`docs`

In SQLite, add that check yourself. See SQLite JSON.

Concurrency and licensing

SQLite allows one writer at a time. In WAL mode, “readers do not block writers and a writer does not block readers” (Write-Ahead Logging). An SQLite database can reach about 281 TB with 64 KiB pages (Limits in SQLite). SQLite is public domain, and “MariaDB Server will remain Free and Open Source Software licensed under GPLv2” (About MariaDB Server).

When to use SQLite and when to use MariaDB

SQLite’s documentation (Appropriate Uses For SQLite) recommends a client/server database such as MariaDB when:

The data is on a different machine from the code that queries it.
Many writers must write at the same instant. “SQLite only supports one writer at a time per database file.”
The data may reach the terabyte range.

Otherwise, it recommends SQLite. That matches Home Assistant’s default: a single app on one machine, with SQLite unless you have a reason to run a server.

If you already know MySQL

MariaDB started as a fork of MySQL, but the two have drifted apart. The upsert above is one example. See SQLite vs MySQL for the MySQL 8 forms.

Conclusion

SQLite and MariaDB fit different setups. SQLite is a file your program opens, and MariaDB is a server your programs connect to. Start with SQLite for a single app on one machine, and switch to MariaDB when several clients need to write at once.