Mysql as Vito's default database

[saeedvaziry]

Vito uses SQLite for its database driver.

Since version 3, Vito received a lot of features and the number of write attempts to the database has increased significantly.

This ends up causing "database is locked" errors when multiple write attempts are made simultaneously.

Migrating to Mysql will solve this issue.

[heyjorgedev]

Hi @saeedvaziry, have you tried to enable WAL mode on sqlite before doing such migration?

there are params like that could reduce or even eliminate the locks entirely.

PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;
etc...

Don't know if you can reproduce the database is locked issue somehow but I will submit a PR with simple changes that should improve the locks.

btw, vito is amazing

[saeedvaziry]

Its a good idea but WAL creates additional files and during time can slow down the read queries and might be challenging to maintain in a self-hosted app

[heyjorgedev]

Hi @saeedvaziry,

I can see your points, from my experience I can tell you that reads are actually faster using the WAL, journal_mode WAL creates 2 more files, the WAL and a shared memory file to help concurrency. so 3 files in total (and not at all times, when a checkpoint happens and the WAL becomes empty sqlite actually deletes the WAL).

The WAL is like an append-only log to keep reads and writes separated helping the sqlite file to not be locked by writers.

You're the boss, I honestly think that it is worth a try before including another daemon on the server that needs to be taken care of, spends memory on the server and a backup is not just copying a file.

[saeedvaziry]

@heyjorgedev you're welcome for a PR of course. Vito already supports mysql but it is still my first prio to keep the instance itself as light as possible to be able to boot up vito on low resources