Decoupling SQLite query builder from connection

[simolus3]

Backend

SQLite

What do you want to add to diesel?

I'm interested in building SQLite queries with Diesel and then running them against a custom connection implementation. My main motivation is that I want to build auto-updating reactive queries that automatically re-run whenever a table mentioned in a SELECT changes. But this would also be helpful to use Diesel with SQLite-compatible engines like libsql or Turso.

So in the end, I only really need the query-building part of Diesel and I'd write my own connections to actually run statements and to track updates asynchronously.

Getting the SQL text out of an QueryFragment<Sqlite> is easy enough since SqliteQueryBuilder is public and exposes the SQL text after visiting an AST.
However, it looks like it's impossible to extract parameters:

1. SqliteBindCollector is private, and I can only construct it through <<Sqlite as Backend>::BindCollector<'_> as Default>::default(). But there is seemingly no way to get bound values out of the collector outside of the diesel crate.
2. Of course, I could implement my own Backend with a BindCollector that delegates to Sqlite as much as possible. But some fragments are only implemented for the Sqlite backend and I'd have to replicate that logic.

The bind collector for SQLite being async creates a strong coupling between the query builder and the connection, making them impossible to use independently. So my feature request is to expose an API allowing users to inspect bound parameter values generated by the SQL dialect.

Implementation notes

Ideally, I would like InternalSqliteBindValue and SqliteBindCollector to be public.

If that is not feasible, perhaps there could be an API to obtain bound values in general. AstPass::collect_debug_binds_pass is quite close to that already, since I can do this and then wrap any query fragment in that struct to call collect_binds on it:

pub struct ObtainBoundValues<'a, T: QueryFragment<Sqlite>> {
    fragment: &'a T,
}

impl<'a, T: QueryFragment<Sqlite>> From<&'a T> for ObtainBoundValues<'a, T> {
    fn from(value: &'a T) -> Self {
        Self { fragment: value }
    }
}

impl<'a, T: QueryFragment<Sqlite>> QueryFragment<Sqlite> for ObtainBoundValues<'a, T>
{
    fn walk_ast<'b>(&'b self, pass: AstPass<'_, 'b, Sqlite>) -> QueryResult<()> {
        let mut formatter = vec![];
        let with_debugger =
            AstPass::collect_debug_binds_pass(&mut formatter, &Sqlite);

        self.fragment.walk_ast(with_debugger)?;
        for value in formatter {
            let x: &dyn Any = &*value;

            println!("Found {value:?}");
        }

        Ok(())
    }
}

But the values I get are &dyn Debug when I would probably need them to be ToSql to inspect them. But having a similar ast pass configuration to get mapped values would be helpful.

Additional details

No response

Checklist

• I have already looked over the issue tracker and the discussion forum for similar possible closed issues.

[weiznich]

Thanks for writing this proposal. It's very reasonable to abstract this way.
Generally speaking I would be open to expose SqliteBindCollector with some changes around documentation and making sure that we can extend (if necessary) certain types later on. This mostly involves putting a #[non_exhausive] on the types and make sure that there is a resonable way to construct them if necessary. Making InternalSqliteBindValue public is also reasonable, although then it should likely use a different name

In the end we should end up with a similar setup like for the postgres backend. There we have a postgres_backend and a postgres feature. The *_backend variant only provides what is related to the Backend related implementation, without an actual connection. For sqlite the *_backend feature variant doesn't exist, so that would need to be added. The sqlite_backend feature then needs to be implemented in such a way that it doesn't depend on libsqlite3-sys. This should enable you to replace the query construction side. Replacing the loading side might be a bit more involved as it requires decoupling SqliteValue fromlibsqlite3-sys. That type is essentially only a thin wrapper around the sqlite3 c types, so it will likely not work with turso/libsql.

I'm happy to review PR's for both changes, although I currently don't have the capacity to implement it on my own.

Finally as for turso integration: There is already diesel-turso, which implements something in that direction.