Luke Boswell (Feb 07 2024 at 04:14): Luke Boswell (Feb 07 2024 at 04:14):I've submitted a draft PR #35 which adds Sqlite3 to basic webserver.
I'm not sure we want to merge this because it would add a dependency on sqlite3 which is a C library. I think there is a good argument to be made for including this as it is such a useful and popular library, but then I'm not sure if this would be better suited to a fork or an alternative webserver platform implementation?
I've added this as I'm using Sqlite3 for my webapp experiments and it may be something others could benefit from to help get set up with a good (if basic) default for storage.
I've added an example for the draft implementation, though I haven't completed a lot of testing with this yet. Raising here to discuss including this, and if so, does the API look OK?
Richard Feldman (Feb 07 2024 at 04:40):is it possible to make sqlite an optional dynamically linked dependency?
Richard Feldman (Feb 07 2024 at 04:41):in other words, gracefully handle the scenario where the library isn't available at runtime (e.g. by having all the operations fail), such that people who don't have it installed don't necessarily notice
Luke Boswell (Feb 07 2024 at 04:55):Yeah I think so. I'll investigate.
Hannes Nevalainen (Feb 07 2024 at 06:41):I would love to see sqlite built in, my goto database for most things.
Anne Archibald (Feb 07 2024 at 18:29):Is there a way to compose platform-like bits?
Like, basic-web-server might be useful with or without sqlite, and basic-cli might be useful with or without sqlite. Likewise each of these possibilities might be useful with or without FFTW. Or CUDA libraries. Or a LAPACK implementation.
Each of these would have to live in a platform-like space, since they don't provide the sorts of guarantees that a pure-roc codebase can, but many of them are quite heavy dependencies one wouldn't want to add to all platforms where they might be wanted.
Brendan Hansknecht (Feb 07 2024 at 19:16):Is there a way to compose platform-like bits?
Let me start by specifying the probably really unsatisfying (and not even full) solution. In a solid number of cases, os primitives can be exposed to Roc. A Roc library can then use the primitives to implement more complex apis. For example, all web based sql databases can have their protocol implemented in pure roc.
Now for really looking at it from the platform side.
As it stands today (with no plans to change currently), this has be done in the platform language ecosystem. A host language package could be written that creates a nice rust wrapper for sqlite that is made for Roc. That could then be shared between multiple platforms and enabled with feature flags.
That said, the integration wouldn't be amazing cause the platform author still has to manually wire in and copy over any effects. So a solid amount of the work is pretty manual.
All this said, this should get a bit nicer in the future. With the module param update and effect interpreters. With those two features, it should be possible to make a paired library that essentially makes adding new features like these as simple as importing two libraries (plus a line of code or 2).
On the roc platform side, you simply import the Sqlite platform package. It will expose an Sqlite effect tag union. That tag will just be added to the effect interpreter tag union. Secondly, the Sqlite roc library will be simply be reexported to the user. So 2 lines of code and a url import on the roc side.
On the host language side, they will import the paired package. Then in the effect interpreter, they will add a single extra branch. If they see the Sqlite effect tag, simply hand off the entire command to the sqlite library. So an import and one extra branch in a switch/match statement.
Still not automatic, but should be pretty minimal to add any number of supported libraries of this nature.
Potential more heavy handed solutions:
#ideas > FFI and #ideas > allow platforms to do arbitrary C FFI talk about this some.
Anne Archibald (Feb 07 2024 at 19:33):It sounds like feature flags and module parameters are meant to do what I was suggesting: make roc programs able to say "I want basic-cli with FFTW and LAPACK but not CUDA".
It also sounds like the author of any given platform needs to do something, possibly non-trivial, to support each of these flags, even if they are already supported on other platforms. Certainly it is reasonable that a platform author might need to decide how these "extras" get integrated into their platform (or don't); a wasm platform probably can't support any of these, while a CUDA feature needs some scheme for managing possibly exclusive access to GPU resources. It would be nice if it were relatively easy to plug them in with a uniform interface across multiple platforms.
Reading the other threads, it also sounds like there is no way for a platform to promise roc "this matrix multiplication is side-effect-free, you can make it available in pure contexts".
Brendan Hansknecht (Feb 07 2024 at 19:43):A quick note on feature flags. Roc distributes platforms as precompiled binary files. If roc had feature flags that selected which specific version of basic cli to use. And basic cli had 7 feature flags, that would be 2^7 = 128 different basic cli releases to publish. So there is a good chance that feature flags would be left for users who are willing to compile platforms from source. That way, basic cli can just distribute a default.
Brendan Hansknecht (Feb 07 2024 at 19:43):Reading the other threads, it also sounds like there is no way for a platform to promise roc "this matrix multiplication is side-effect-free, you can make it available in pure contexts".
Correct. And definitely no way that Roc could verify it is pure.
Luke Boswell (Feb 10 2024 at 04:24):I'm not sure it's practical to dynamically load the sqlite dependency at runtime. I think it's possible, but from my research I think that requires implementing the unsafe parts of something like the sqlite crate. I'm interested to know if anyone has any ideas for how we might be able to do this otherwise?
I could add a feature flag so users that building the platform from source can use it. I'm not sure if that is the direction we want to go as application authors would then need to have platform toolchains available.
I guess the best app author experience would be to statically link the sqlite dependency. It looks small at 13.1 MB vs 11.5 MB for the pre-built platform macos-arm64.o with optimisations on my system. edit updated sizes
Luke Boswell (Feb 10 2024 at 04:31):One idea I had was to write an object using zig that provides the desired symbols and we include statically. This thing might attempt to load the library dynamically at runtime and if it cant then always returns an error, otherwise just passes things through to sqlite. I don't know if this is possible... just a wacky idea
Brendan Hansknecht (Feb 10 2024 at 04:40):The library is 0.1MB?
Luke Boswell (Feb 10 2024 at 04:41):Maybe I did something wrong
Brendan Hansknecht (Feb 10 2024 at 04:43):Should be about 0.5 to 1 MB: https://sqlite.org/footprint.html
Luke Boswell (Feb 10 2024 at 04:43):As an aside, I'd really like to modify the platforms so that I can run cargo to build the prebuilt binary. I haven't succeeded in that yet
Luke Boswell (Feb 10 2024 at 05:05):Updated the sizes, it looks to be 1.6 MB when we bundle sqlite in the platform
Anne Archibald (Feb 10 2024 at 11:56):Richard Feldman said:
in other words, gracefully handle the scenario where the library isn't available at runtime (e.g. by having all the operations fail), such that people who don't have it installed don't necessarily notice
I have to say, having no way for my script to express that it actually needs sqlite to function, having it load up and run successfully until it tries to execute any sqlite call, all of which then fail, is not at all a graceful situation.
Richard Feldman (Feb 10 2024 at 12:01):well the alternative is to always include it. Sounds like maybe sqlite is small enough to justify in this case!
Anne Archibald (Feb 10 2024 at 12:03):Having just read a lot of sqlite documentation, though, it does seem like some platforms might do something interesting with sqlite's flexible memory handling - it looks like sqlite could be persuaded to work with per-http-request memory arenas or no-heap platforms. So at least some of the platform connections to sqlite would be non-trivial.
It does sound like feature flags will at least allow scripts to express their requirements, though the decision not to support dynamic linking does mean it will be a pain to use any but specific popular combinations of the feature flags.
Anne Archibald (Feb 10 2024 at 12:05):Richard Feldman said:
well the alternative is to always include it. Sounds like maybe sqlite is small enough to justify in this case!
Er, it's about 238k lines of C code. Though it can be used as a single mega C file or 111 reasonably scoped individual C files. I can't speak to the size or complexity of the Rust (Zig?) adapter.
Richard Feldman (Feb 10 2024 at 12:06):I mean 1.6mb
Luke Boswell (Feb 10 2024 at 12:22):If we are open to include sqlite as a special case I'll clean up that PR, investigate the CI issues, and test it more by converting the Todos demo instead of using Commands.
Anton (Feb 10 2024 at 12:29):1.6 MB is good for me as well.
Richard Feldman (Feb 10 2024 at 12:50):sure, let's try it and see how it goes!
Luke Boswell (Feb 10 2024 at 21:23):I dont have a great idea for managing the connection. At the moment it opens a fresh one on each call to sqlite. Does anyone have ideas for a better way to do this? Maybe make a global connection, and set a timer to close it after not being used for some time?
Brendan Hansknecht (Feb 10 2024 at 21:31):Preferably it would be a connection per thread
Brendan Hansknecht (Feb 10 2024 at 21:31):Maybe the user can just register the database file and the host can cache that info
Brendan Hansknecht (Feb 10 2024 at 21:32):Then hold a connection per thread
Brendan Hansknecht (Feb 10 2024 at 21:32):I don't think there is a strict need to ever close the connection while the server is running
Luke Boswell (Aug 12 2024 at 04:46):@Brendan Hansknecht what's the status with https://github.com/roc-lang/basic-webserver/pull/61 ??
Luke Boswell (Aug 12 2024 at 04:46):Can I help you with it?
Luke Boswell (Aug 12 2024 at 04:46):From memory we were looking for a good way to have errors merge nicely
Brendan Hansknecht (Aug 12 2024 at 05:10):Any significant use of the API breaks in alias analysis
Brendan Hansknecht (Aug 13 2024 at 00:00):Just realized that the new basic-webserver init api doesn't quite work for sqlite. Sqlite requires prepared statements to be threadlocal. Init creates global state.
Brendan Hansknecht (Aug 13 2024 at 00:01):So for this to work, we would need to call init on every single thread.
Luke Boswell (Aug 13 2024 at 00:20):Lol, I had to laugh. After all that work. Well, at least we have a nicer API :laughing:
Richard Feldman (Aug 13 2024 at 01:11):couldn't we have init optionally (e.g. via a default record field) a function that returns the desired prepared statements, and then the host could run that on each thread?
Brendan Hansknecht (Aug 13 2024 at 01:34):Would that be equivalent to a perThreadInit and a globalInit field? Just trying to understand the execution model.
Also, I don't think optional records can interact with the platform API.
Richard Feldman (Aug 13 2024 at 12:42):yeah could also do a perThreadInit :thumbs_up:
Richard Feldman (Aug 13 2024 at 18:00):although it's probably easy to mess up compared to having something specific to prepared statements
Richard Feldman (Aug 13 2024 at 18:01):because it would be very easy to think "oh I want prepared statements to be globally available, so obviously don't do them on the per-thread init!"
Richard Feldman (Aug 13 2024 at 18:02):I don't think this is a case where documentation is likely to be an effective fix either :big_smile:
Brendan Hansknecht (Aug 13 2024 at 18:05):For sure. It also is SQLite specific. So I don't think it would get reused in other databases.
Brendan Hansknecht (Aug 13 2024 at 18:06):That said, not sure of a good way to solve it. Would be amazing if I could make it automatic somehow.
Brendan Hansknecht (Aug 13 2024 at 18:07):I guess if we call prepare during init I need to force Tokio to run the command on every possible thread.
Brendan Hansknecht (Aug 13 2024 at 18:15):Oh, but that wouldn't actually work. Cause I would need to return a different model to each different thread to pass in the correct prepared statement. I guess I could add an extra level of indirection, but that feels not great
Brendan Hansknecht (Aug 15 2024 at 23:08):So, I don't actually think a thread local init would work that well. I think we may need prepare to somehow setup threadlocal lazy initialization.
Brendan Hansknecht (Aug 15 2024 at 23:09):Cause we may have 500 blocking threads waiting on sqlite
Brendan Hansknecht (Aug 15 2024 at 23:09):Cause that is how tokio works for spawn blocking
Brendan Hansknecht (Aug 15 2024 at 23:09):Probably don't want 500 call to init or some sort of thread local init. Feels like a likely annoyance
Brendan Hansknecht (Aug 15 2024 at 23:11):But not really sure how to architect this correctly.
Brendan Hansknecht (Aug 15 2024 at 23:12):given sqlite can wait on io or locks, we probably do want to allow for the many many threads. That allows for a lot more concurrency when an sqlite thread is just waiting blocked.
Brendan Hansknecht (Aug 15 2024 at 23:12):Though maybe 500 is still way too much and we want to write our on special thread pool for all things sqlite...not sure though
Luke Boswell (Aug 15 2024 at 23:13):Spawing off the sqlite parts into separate threads sounds promising.
Brendan Hansknecht (Aug 15 2024 at 23:21):yeah, the lazy threadlocal mechanism just sounds kinda complex. Especially given we don't want it to cost much when preparing a statement only for use on a single thread
Brendan Hansknecht (Aug 16 2024 at 22:27):So I think I figured out how to have our cake and eat it too. Basically some tricks around lazily instantiating thread local prepared statements.
Brendan Hansknecht (Aug 17 2024 at 00:33):that said, not exactly sure the cost of this. Like it is extra overhead to manage the queries this way. Won't know for sure how it scales until we have larger uses of sqlite
Brendan Hansknecht (Aug 17 2024 at 00:33):Still is currently a perf gain due to avoiding creating queries many times
Luke Boswell (Aug 17 2024 at 00:37):I'm stoked to test it out. It looks like a massive perf and ergonomics improvement
Last updated: Jun 16 2026 at 16:19 UTC