I have a diesel question and am hoping someone can point me at an example I can learn from. I want to have a sqlite file as a cache. Before doing an expensive calculation see if it is in the database. If not (make and set up the database if needed and) add it to the file. The examples I have found use migrations to set up the database, I was hoping for an example where the program makes sure the database is setup correctly. Thanks!
Sqlite doesn't have fancy features like SELECT … FOR UPDATE, so caching with sqlite isn't anything more than a regular SELECT followed by INSERT.
Depending on how your workload looks like, you may want to have some kind of lock between the select and insert to avoid multiple cache misses wastefully doing the same work. However, I wouldn't recommend trying to do that with sqlite transactions. Sqlite doesn't have row-level locking, so it's going to be slow and/or throw "database locked" errors instead.
Also beware that writes to sqlite are single-threaded, and block reads. Sqlite ruins performance of multi-threaded programs.
2 Likes
Thank you! That is all very good information. I am stuck at a more basic question, the examples assume that the file exists and is set up by the migrations with the CLI. I was hoping for some guidance on creating the files if it does not exist from within main. With how great all the other docs are, I was hoping there is an example of that I missed.
Have a look at the Polaris repo, they migrate the DB automatically for the user: polaris/mod.rs at master · agersant/polaris · GitHub
1 Like
If the database file doesn't exist, sqlite will create it automatically. You can then unconditionally run CREATE TABLE IF NOT EXISTS to set up your schema. Instead of migrations, you can just delete the file to have it re-created with a new schema 
1 Like
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.