Hi,
Is it possible when importing a SQLite database
to configure Metabase to open a connection to it as read-only
?
For example,
file:sqlite.db?mode=ro
Hi @nskalis
Metabase is by definition read-only already, since it only does analytics. You cannot send INSERT/UPDATE/DELETE queries to any of the databases.
Metabase doesn’t import databases, it connects to them (and caches some content, but that’s a different topic).
Some databases needs write-mode, since that’s the only way to set certain database settings (like timezones, etc). I’m not sure if that applies to SQLite.
As a work-around, since SQLite is file-based, then you could just set the file to be read-only by whatever file-system user that Metabase is trying to access the file with.
I have a background process that populates (INSERT) the sqlite db, when Metabase performs heavy queries, the sqlite gets locked.
I am not sure if Metabase connects to SQLite as read-only, it acquires an exclusive lock with an unknown timeout value, can you point me to the line of code responsible for that ?
@nskalis
Metabase uses drivers to connect to the various databases - the SQLite driver is located here:
https://github.com/metabase/metabase/tree/master/modules/drivers/sqlite
Looks like you need to pass the property open_mode=1 to set it to read-only:
https://stackoverflow.com/questions/4574303/java-sqlite-how-to-open-database-as-read-only
If you set the file-user that Metabase uses to access the SQLite to read-only, then you should get the same result.
thanks @flamber, but why a system setting should be set instead of setting a connection parameter in SQLite (like it is the case with the rest of the drivers, PostgreSQL, etc.) ? wouldn’t that be a desired feature ?
@nskalis
I guess because SQLite doesn’t support connection string parameter, like most databases, but would need some interpretation of a string to set values as properties.
You are welcome to open a feature request for that:
https://github.com/metabase/metabase/issues/new/choose