CREATE DATABASE ... ENGINE = SQLite should create the file

CREATE DATABASE lite ENGINE = SQLite('/data/dim.db') failed in early 2022 if the file did not exist, so a setup script had to create it with another tool. I asked for ClickHouse to create it. A developer asked the fair question of what an empty file is for, since the engine could not create tables. My use case: a few small dimension tables that change often and need real UPDATE and DELETE, with a file as the only infrastructure. Someone suggested EmbeddedRocksDB; I replied it has no relational integrity.

I closed the issue myself that day, thinking an empty file made no sense without table creation; it was reopened. PR #34907, “Allow to open empty sqlite db if it does not exist”, by kssenii, merged on 2022-02-28 and shipped in 22.3.

The file part works. The part I needed does not. On 26.4:

CREATE TABLE lite.countries (code String, name String) ENGINE = SQLite('/data/dim.db', 'countries');
-- Code: 48. There is no CREATE TABLE query for DatabaseSQLite. (NOT_IMPLEMENTED)

Tables created with the sqlite3 tool are readable and writable from ClickHouse, but defining them is still a manual step, hence “partially implemented”.