Sqlite Support (presently not possible)

[justinturpin]

Currently the crate doesn't work with sqlite, or if it does the interface is different for it:

use sqlx::Connection;
use tide::prelude::*;
use tide::Request;
use tera::Tera;
use tide_tera::prelude::*;
use sqlx::Acquire;
use sqlx::sqlite::Sqlite;
use sqlx::postgres::Postgres;

use tide_sqlx::SQLxMiddleware;
use tide_sqlx::SQLxRequestExt;

#[async_std::main]
async fn main() -> tide::Result<()> {
    let mut tera = Tera::new("templates/**/*.html")?;
    tera.autoescape_on(vec!["html"]);

    let mut app = tide::with_state(tera);

    app.with(SQLxMiddleware::<Sqlite>::new("sqlite3://testdatabase.sqlite3").await?);

    app.at("/").get(|req: Request<Tera>| async move {
        let tera = req.state();
        tera.render_response("index.html", &tera::Context::new())
    });

    app.listen("127.0.0.1:8080").await?;
    Ok(())
}

Fails to compile with:

error[E0599]: no function or associated item named `new` found for struct `SQLxMiddleware<Sqlite>` in the current scope
  --> src\main.rs:51:40
   |
51 |     app.with(SQLxMiddleware::<Sqlite>::new("sqlite3://testdatabase.sqlite3").await?);
   |                                        ^^^ function or associated item not found in `SQLxMiddleware<Sqlite>`
   | 
  ::: C:\Users\Justin\.cargo\registry\src\github.com-1ecc6299db9ec823\sqlx-core-0.4.0\src\sqlite\connection\mod.rs:23:1
   |
23 | pub struct SqliteConnection {
   | --------------------------- doesn't satisfy `SqliteConnection: Sync`
   |
   = note: the method `new` exists but the following trait bounds were not satisfied:
           `SqliteConnection: Sync`

error[E0277]: `NonNull<libsqlite3_sys::bindings::sqlite3>` cannot be shared between threads safely
   --> src\main.rs:51:14
    |
51  |     app.with(SQLxMiddleware::<Sqlite>::new("sqlite3://testdatabase.sqlite3").await?);
    |              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ `NonNull<libsqlite3_sys::bindings::sqlite3>` cannot be shared between threads safely
    | 
   ::: C:\Users\Justin\.cargo\registry\src\github.com-1ecc6299db9ec823\tide-sqlx-0.3.1\src\lib.rs:196:28
    |
196 |     DB::Connection: Send + Sync + 'static,
    |                            ---- required by this bound in `SQLxMiddleware`
    |
    = help: within `SqliteConnection`, the trait `Sync` is not implemented for `NonNull<libsqlite3_sys::bindings::sqlite3>`
    = note: required because it appears within the type `sqlite::connection::handle::ConnectionHandle`
    = note: required because it appears within the type `SqliteConnection`

I'm guessing this is do to weirdness around Sqlite itself not being thread safe, and Sqlx does some thread-wrapping trickery. I'd be happy to take a look and try and fix this if someone had some ideas about where to go to do this. I'm playing around with a small Tide web app right now to try and figure out the best approach to having an app that uses Sqlite for many readers but only one writer.

[Fishrock123]

This is mentioned in https://github.com/eaze/tide-sqlx/pull/4/commits/570dbc13de940054ab1a19dae57cb3a5754fc98d, but should be in the docs. This is unfortunately impossible to support in current Tide. All middleware must be able to be Send + Sync + 'static - that is, it must be thread safe, due to async-std's threaded futures executor.

One day, when we can do borrows for futures properly and tide's middleware signature looks more like

AsyncFn(&mut Request, ...)

then perhaps this will be possible, although the general consensus is that doing so requires async closures in the language, something which has been mentioned but for which there is not yet an RFC to my knowledge.

I have opened https://github.com/launchbadge/sqlx/issues/920 over on the SQLx repo, but it is up to you for any investment if you want this!

[justinturpin]

Cool thanks, I figured it was something non-trivial like this. I'll poke around the Sqlx codebase and see what it might take to make the Sqlite connection sync. I think theoretically Sqlite can have multiple threads be read-only, and only one thread can get write access, so maybe theres something with an Arc<RwLock> that can be done. Thanks for looking into this!