SQLite Forum

Feature Request: ATTACH DB readonly?

Feature Request: ATTACH DB readonly?

(1) By midijohnny on 2021-11-24 17:30:35 [link] [source]

Apologies if this is wrong place to request features (if so: is there somewhere else to do that?).

Could we have a extension to the ATTACH DATABASE command to allow a secondary DB to be attached as readonly?

I have a use-case where I want to access information from another DB, but want to avoid making any changes by accident; I also want to signal to others that the DB should be opened-up as readonly (or 'reference' etc).

(I am aware that you can make the file readonly : but this would also make the same data readonly for all other access to the same file; which isn't what I want to do).

(2) By ddevienne on 2021-11-24 17:44:59 in reply to 1 [source]

Are you aware of URIs in SQLite?
I think this allows to do what you want.

(3) By Larry Brasfield (larrybr) on 2021-11-24 17:45:09 in reply to 1 [link] [source]

See URI query parameters, then use the URI file specification syntax for your ATTACH. (A compile-time option to enable URIs may be necessary, depending on where you get SQLite bits.)

(4) By Keith Medcalf (kmedcalf) on 2021-11-24 21:54:55 in reply to 1 [link] [source]

(5) By anonymous on 2021-11-24 22:09:53 in reply to 4 [link] [source]

That applies globally to the main, temp, and all attached databases.

(6) By Keith Medcalf (kmedcalf) on 2021-11-24 22:37:02 in reply to 5 [link] [source]

Ooops. You are correct. It applies to the entire connection (it does not recognize the <schema> prefix.)

(7) By midijohnny on 2021-11-25 13:39:16 in reply to 2 [link] [source]

That works - thanks !

sqlite> .version
SQLite 3.36.0 2021-06-18 18:36:39 5c9a6c06871cb9fe42814af9c039eb6da5427a6ec28f187af7ebfb62eafa66e5
zlib version 1.2.11
sqlite> attach database "file:reference.db?mode=ro" as reference;
sqlite> PRAGMA database_list;
sqlite> .tables
sqlite> drop table reference.x;
Error: attempt to write a readonly database

(8) By midijohnny on 2021-11-25 13:39:57 in reply to 3 [link] [source]

Thanks !