WASM support for Multi-DB

[blazeroni]

It appears custom WASM endpoints do not have the additional databases attached when Trailbase initializes. I've done some experimenting and it seems that I'm able to have a dedicated WASM endpoint attach the database and all other requests can call queries referencing it, including to other WASM endpoints.

Since Trailbase appears to make internal HTTP requests for queries in WASM endpoints, I was concerned that it would only apply to a single connection and would randomly fail. Based on some basic testing it appears to apply to all requests, but I could just be getting the same connection each time. So, I made a second WASM endpoint that made repeated queries and made a script to hit that endpoint with simultaneous requests. The result was that all queries saw the attached database.

So, a few questions:

• What is the intended way to use ATTACH DATABASE within custom WASM endpoints?
• Is every request guaranteed to see the attached database after it's attached?
• Since ATTACH DATABASE is connection local, is there only a single connection serving WASM endpoints or is the attachment being propagated somehow?
• Can this be made simpler -- either automatically attach databases already linked in the UI, or allow this to be done during initialization and not require an initial request?

[ignatz]

Hey, thanks for the issue, this needs improvement 👍 🙏

Is every request guaranteed to see the attached database after it's attached?

As it's wired up, it shouldn't. If I had to guess: your test queries are all mutating, reads are distributed over a pool of connections.

Since ATTACH DATABASE is connection local, is there only a single connection serving WASM endpoints or is the attachment being propagated somehow?

No magic.

Can this be made simpler -- either automatically attach databases already linked in the UI, or allow this to be done during initialization and not require an initial request?

Not only does it need to be simpler, it needs rework to not turn into a chore since a connection can only handle up to 127 DBs, i.e. at some point you may need to implement a lifecycle and management (also the approach should already not work for reads).

Since Trailbase appears to make internal HTTP requests for queries in WASM endpoints, ...

You stumbled upon an implementation detail/hack. WASIp2 doesn't have a good way to do async computation. This will be rectified by WASIp3. I'm basically just tapping into WASIp2's workaround to-do async HTTP requests. I have a whole bunch of features/APIs lined up, which I've been holding off on, until WASIp3 becomes available.

Adding support for other DBs should be one of them. Would an API work for you where one can simply specifiy the DB name as an optional argument to the query/execute?

🙏

[blazeroni]

your test queries are all mutating

They aren't mutating - just SELECTs.

No magic.

Hmm, that's a bit concerning for my setup then. I haven't seen a request fail due to the database being attached (once I've initialized it), but perhaps I need to rethink my strategy.

Would an API work for you where one can simply specifiy the DB name as an optional argument to the query/execute?

That could work, though in my case the separate DBs aren't for multi-tenancy purposes. They are secondary DBs generated by other systems. I'll never never get close to 127, so my preferred setup would be to initialize it in a single place for all endpoints. I know that won't work well for the case you outlined, but perhaps it could be an option.

[ignatz]

your test queries are all mutating

They aren't mutating - just SELECTs.

That's a bit sus. I'll have a look. Are you using Rust or JS/TS?

No magic.

Hmm, that's a bit concerning for my setup then. I haven't seen a request fail due to the database being attached (once I've initialized it), but perhaps I need to rethink my strategy.

Would an API work for you where one can simply specifiy the DB name as an optional argument to the query/execute?

That could work, though in my case the separate DBs aren't for multi-tenancy purposes. They are secondary DBs generated by other systems. I'll never never get close to 127, so my preferred setup would be to initialize it in a single place for all endpoints. I know that won't work well for the case you outlined, but perhaps it could be an option.

At least in the interim, maybe permanently, there could be a config setting to define which DBs should be attached to the main connection. Let me sleep over it

[blazeroni]

Are you using Rust or JS/TS?

Rust

[ignatz]

Let me sleep over it

I've woken from my slumber - sorry for the delay.

That's a bit sus. I'll have a look. Are you using Rust or JS/TS?

I take everything back I said before. All requests from WASM go through the same "write" connection, since we don't distinguish between mutating and non-mutating queries. Otherwise, we'd have to parse every request first (which we could).

Long story short - your ATTACH DATABASE is guaranteed to work and I have no intent of changing that. I would argue it's even the way to go for now (i.e. during component init). Eventually, with WASIp3 I would like to provide richer APIs that also work with arbitrary numbers of databases. If acceptable, I would avoid adding a new, limited-use config option that may become obsolete.

Does that work for you? And sorry again for the confusion 😅

[blazeroni]

Long story short - your ATTACH DATABASE is guaranteed to work and I have no intent of changing that.

Oh, that's good news!

All requests from WASM go through the same "write" connection

Hmm, that's less good, though not surprising since ATTACH DATABASE worked. I re-ran my stress test with a more complex query and started seeing a portion of the requests fail, which I presume were due to the single connection being busy.

It would be helpful to have the ability to use read-only connections. Parsing could work, but even something as simple as calling a specific read-only variant of query seems OK (developers would need to know the implications of using one vs the other, which may not be ideal).

Multiple connections complicates the story around ATTACH DATABASE, though. Subsequent queries would have to use the same connection.

[ignatz]

Just curious, what's the failure mode? Very open to improves things.

As for multiplexing over multiple connections, just keep in mind that it would skrew your ATTACH DATABASE approach.

[blazeroni]

Looked a bit closer at the errors and many were due to low resource limits on my machine. After fixing that I was left with a handful of ErrorCode::ConnectionReadTimeout errors being returned immediately from the internal WASM HTTP call.

[ignatz]

Looked a bit closer at the errors and many were due to low resource limits on my machine. After fixing that I was left with a handful of ErrorCode::ConnectionReadTimeout errors being returned immediately from the internal WASM HTTP call.

This helps a lot 🙏 . For context, the SQLite connection should never throw a busy timeout, which is an SQLite thing due to how we do in-process locking. What your're seeing is an HTTP timeout, which is an artifact of how the sqlite connection is hacked into the WASI HTTP system :/. We could extend the HTTP client's timeouts in the Rust WASM guest if necessary but at some point you're going to run into limits one way or the other then the question becomes what's the best failure mode for users...

Let me try and see if I can be a bit smarter in terms of connection usage....

[ignatz]

I just committed 9ecd7c1, which parses queries to handle attaches, detaches, and selects specially. The duplicate parsing should be fairly efficient, while avoiding the complications of having multiple read-only and write APIs putting the burden and correctness on the user.

Are you comfortable with building from source? Want to give it a spin? Naively, I would expect much better throughput

[blazeroni]

Apologies for the delay (I see you've already made a release with the changes).

I tried out the latest release and ATTACHing no longer works for me. It works as expected in v0.25.2, but v0.25.4 fails with an Unexpected Type error.

[ignatz]

Thanks for trying it out and apologies. Added explicit test coverage and will be fixed in the next release.