Add support for limbo (A complete rewrite of SQLite in Rust)

[Choochmeque]

Hi 👋

It would be great if diesel_async could support limbo, the async SQLite driver maintained by Turso.

limbo is a modern, fully asynchronous SQLite driver written in pure Rust. It offers:

• Fully asynchronous I/O
• Bytecode compatibility with SQLite
• No C bindings
• All the safety and performance benefits of a Rust-native implementation

It should perfectly fit to integrate with diesel_async.

Thanks for your time!

[weiznich]

Thanks for opening this. I converted this to a discussion as this is no bug in diesel-async, but a request for a new feature.

I think this would be really interesting to have, but I certainly have neither the capacity nor time to work on this on my own anytime soon.

I quickly looked through the limbo docs and it seems like it would need some adjustments in diesels sqlite backend + limbo to make this work. I'm happy to provide more details on that if someone is interested on working on this.

For the beginning I would suggest to implement the actual connection implementation in a third party crate.

[Choochmeque]

@weiznich,
I could try to implement this part in my spare time, cause I am working on pet project which is using diesel + sqlite.

[weiznich]

Thanks for your interested. Let me provide some more details what need to be done:

• There is some refactoring in the sqlite backend in diesel requires as that currently requires using the sqlite data structures (from the c library) internally. The following data structures would need to be refactored to be more flexible:
  • SqliteValue
  • We would need to make sure that the public interface stays the "same".
• There is some refactoring/extension required for limbo. We would need to have some method that allows us to convert this type to a limbo prepared statement parameter. It also might work to just utilize limbo::param::IntoParams for that
• You would need to have a struct LimboConnection that wraps the limbo connection type and implement AsyncConnection
  • That's likely easy to have at a high level, but requires the points before to unblock this
  • You need to deal with the following parts there:
    • Construct a new connection from a database URL, I would assume that this is straight forward
    • Construct a new prepared statement via the limbo API: https://docs.rs/limbo/0.0.20/limbo/struct.Connection.html#method.prepare
    • Make sure to use StatementCache for diesel to cache these statements as required. (You might want to look at how the caching is implemented for the other connections:

      diesel_async/src/pg/mod.rs

      Lines 585 to 604 in c6f9dc8

      	let instrumentation = Arc::clone(&instrumentation);
      	stmt_cache
      	.cached_statement_non_generic(
      	query_id,
      	&helper,
      	&Pg,
      	&bind_collector.metadata,
      	raw_connection.clone(),
      	prepare_statement_helper,
      	&mut move |event: InstrumentationEvent<'_>| {
      	// we wrap this lock into another callback to prevent locking
      	// the instrumentation longer than necessary
      	instrumentation.lock().unwrap_or_else(|e| e.into_inner())
      	.on_connection_event(event);
      	},
      	)
      	.await?
      	.0
      	.clone()
      	};

      )
    • Collect bind params from diesel queries via the sqlite bind collector (might need to be exposed from diesel?)
    • Turn these "diesel binds" into limbo params
    • Execute the query via query or execute depending on the context
    • For load/query: Implement a custom Row and Field type for this connection that turns the limbo field value into a SqliteValue from above.

Hopefully that's "everything" that's required to get this working. As can be seen at the amount of points this is not a trivial change. I'm happy to help with details if you get stuck somewhere or need other help, although for that it would be helpful to have some code somewhere to discuss the relevant details there.

[Choochmeque]

Maybe this is stupid question, but why can't we add new db backend without touching sqlite?
I mean: "pg", "mysql", "sqlite" and "limbo". Or maybe I am missing something and diesel will not allow this?

[weiznich]

Technically that's certainly a possibility. This would allow to skip the first step in the list above, anything else would be very similar. Additionally you would need to reimplement a lot of existing functionality (support for certain sql statements, data serialization/deserialization for any type you want to support, etc). By reusing the sqlite backend you would get all of that "for free". On the other hand it might be reasonable to go with a different backend if it's expected that limbo is just really different to sqlite in terms of supported sql syntax and underlying data format.

[Choochmeque]

Well, will check the complexity for both cases then...
Anyway LimboConnection must be implemented, no matter what approach will be used.
Thank you for the information.