Embedded database is so lack of choice. SQLite, might be best, if you want stability / ACIDity.

Again, SQL means normalize everything, if I've ever want to index it...

Then, ON DELETE CASCADE? TRIGGER? Also, MANY-to-MANY kills.

Well... you could use MS Access...

Can't you use an ORM that generates the SQL for you?

I feel the same. I hate the need to convert my data from hierarchical to relational and back again. But I can't find an alternative for secure, fast and reliable data storage.

@rooter

You forgot that indexing is important. SQLite can only index normalized tables, and string indexes do not search fast with LIKE '%xxx%'. (Or json1 extension).

@polv I'm not aware LIKE '%...%' performs well in any database... Have you considered using the full text search extension instead?

https://www.sqlite.org/fts5.html

@sbiewald fts, at least in SQLite, requires sentences to be tokenize properly; as well as doesn't support wildcards.

Some implementations I don't know might support wildcards.

As for LIKE adaptation I am talking about, is, '% '||?||' %' for ` ${tags.join(' ')} `

I do know a solution - normalized Tag table, with COLLATE NOCASE.

@sbiewald MongoDB does support string array indexing. Not sure about postgres; but definitely not SQLite.