1

So i have a sqlite database i run queries on using better-sqlite3, this database is being updated constantly, which will cause the queries i am running to mess up if certain data was changed while it was finishing all the queries. To combat this i am currently reading the sqlite file, and writing it into a different location, and then just accessing the data off of that since it won't be updated till i read and write it again. But i would rather not be constantly reading and writing the file to the disk if i don't need to. Is there a way to load the file into cache? The main issue would be i would need some path to that cached file, since better-sqlite3 uses the files path to start loading data, unless there's an option in better-sqlite3 I am unaware of, any advice would be appreciated, thanks.

I Have heard of something called memfs, but cannot get it to work with better-sqlite3 since it requires a path.

C Perkins
  • 3,733
  • 4
  • 23
  • 37
kyle hoell
  • 117
  • 2
  • 13
  • What journal mode is your database using? – C Perkins Aug 07 '19 at 22:31
  • I'm a little confused: if you don't want to work with up to date data, why are you working with a database at all? The fact that the sqlite3 db gets written to should be effectively irrelevant, all you should worry about is whether or not your queries are correct _given the database when you query it_? (sqlite3 uses journaling by default, so the database can't really change mid-query) – Mike 'Pomax' Kamermans Aug 07 '19 at 22:31
  • See https://www.sqlite.org/isolation.html – C Perkins Aug 07 '19 at 22:32
  • Sqlite can open a database in memory, but it must load it from a file. I think that it would not load a currently open database, so you'd have to close all connections first. To "load the file into cache" is not going to improve the overall process that you described, rather it will likely complicate it. You should be able to configure the database journaling mode and connection properties to achieve what you want without doing this yourself. – C Perkins Aug 07 '19 at 22:41

0 Answers0