Hi everyone,

I’ve got a little hobby project (https://plaintexttides.com) that I’m working on and playing with sqlite a bit with it. I’m really only using a database to save myself some computation and API calls and speed up things, so I’m looking to use a sqlite database to essentially do that “work” ahead of time by bulk downloading the predictions at dev time vs runtime..

Essentially I’m bulk downloading and storing the tide predictions from NOAA for all the tide stations in the US for this calendar year. 4 tides, 365 days, and about 3300 stations. Not a huge amount of data, but instead of reaching out to NOAA for each time I have a user request to show the info, I have a little (~100Mb) sqlite database to query from using ecto.

I currently have it live in “priv/” and all seems to work nicely, but I don’t have a great strategy for how to “build” that database from the ground up. Using migrations, which is what I have now, doesn’t seem like quite like the “right” approach since I’ll never be doing it at runtime, but maybe it is.

Currently this is the only Repo in my app, but there’s a possibility that I could add a postgresql (or another sqlite) in the future so I want to keep options open, and since it’s a hobby project I don’t have to worry as much about YAGNI.

I was wondering if any of you had suggestions or approaches that you’ve had success with in building sqlite databases as something like static assets, maybe as a mix task or otherwise?

Thanks for any suggestions and thoughts.