Generic SQL bindings

[steveklabnik]

Issue by brson Wednesday Jun 04, 2014 at 22:17 GMT

For earlier discussion, see https://github.com/rust-lang/rust/issues/14658

This issue was labelled with: A-libs, I-enhancement in the Rust repository

---

We need a generic library for SQL with implementations for at least MySQL and PostgreSQL.

Prefer a design that has a typed AST that can be checked at compile time. I'm told SqlAlchemy core does this well.

cc @kmcallister

Start out of tree. This crate would ideally be distributed by cargo, not as part of the main distro.

[dckc]

The sqlite bindings I maintain, rust-sqlite3, are heavily influenced by rust-postgres, but the ownership patterns are very different. Any API that abstracts across them is going to have to do a bunch of copying/cloning, I'm afraid.

I'm not very familiar with Rc nor RefCell, on the other hand. Perhaps they would reduce the friction?

[s-panferov]

I have an experiment in https://github.com/deuterium-orm and there is some success. The projects lagged behind Rust's nightlies, but I'm going to refactor them in the next couple of days. (Note that only rust-postgres is supported as a backend now).

[koeninger]

I personally would prefer not to be forced into compile time checking of sql. Every system I've used that attempted such ended up causing problems getting my job done in some important corner case.

[ivanceras]

Plugging in my ORM that I have been working on. https://github.com/ivanceras/rustorm

Supported Database

• [x] postgres
• [x] mysql
• [x] sqlite

And the code generator build on top of the ORM https://github.com/ivanceras/codegenta

Contributions and feedbacks are much welcome.

[dckc]

There's "lots of demand for this recently" according to the Production user research summary.

[oberien]

I actually like the approach of C# (linq) and would love to see this kind of syntactic sugar in rust, including the support to both use it for queries on lists/enumerables and directly transform it into DB-Queries.

[glaebhoerl]

FWIW the state of the art on the Haskell side seems to be Opaleye and Relational Record - just for reference.

EDIT: See also!

[rumatoest]

I think that you should not focus on implementations at all. Just create specification and define core traits for managing DB connection, execute queries and working with result set. Let implementation to other developers. Later you can add better implementation into Rust core.

Think that main approach should be taken from Go https://golang.org/pkg/database/sql/ But also you should mind JDBC from java, especially when treating row from rowset (java approach looks better for me here)

And I think that Rust can do more in this area:

1. You should think about non standard types like array and JSON. Yes I know that some databases do not support them, but it is much better to have ability get JSON/Array from result row for some databases (mariaDb, Postgresl, Oracle) than do not have this ability at all.
2. Maybe should describe API for handling DB queries in async mode

[GildedHonour]

Is anybody working already? Is this https://github.com/deuterium-orm/deuterium it?

[Ygg01]

@GildedHonour No? And deuterium probably isn't that. Deuterium is a project built on top of r2d2 and connection pool it provides.

[rickprice]

What is happening with this RFC? Is there a concrete implementation proposal?

[rumatoest]

I hope that after @sfackler stabilize postgresql library then this work will be used as a basis for future RFC.

[steveklabnik]

@rickprice this isn't a full RFC, only an issue. This means that there hasn't been a concrete proposal yet.

[Centril]

There are now quite a few crates in this area now such as:

• http://tql-rs.ml/
• http://diesel.rs/

also, this isn't our business, so I'm closing this issue.

[rinconjc]

Very sad and disappointing that Rust doesn't have something equivalent to Java JDBC or Golang SQL.

[rumatoest]

Disel is Alive, but it is ORM and ORMs are evil :( TQL - is also ORM and can be considered as abandoned/dead right now.

@Centril You are absolutely wrong. It is Rust business to provide basic API to work with at least with relational DB. There are many places where you can get basic ideas. Even JDBC like API is a good choice as a draft. What is not your business is implementation of this API for each DB.

[burdges]

It's not the core language team's job to design an SQL interface. We all happily use the rand crate because Rust does not provide even random numbers. And some folks want functionality like HashMap relegated to external crates.

[rumatoest]

I think, that such standardization is required to make Rust platform more attractive for corporate apps development. And from this side ugly Golang way more interesting. If you would have standardized low level API that support async calls to DB, than anyone can do it's some framework over it, and there will be guarantees that it is possible to change DB driver without altering top level framework. It is crucial when you are dealing with DB. And all you have to do is just define some king of interface.

P.S. Look at Java world. There are tons of different frameworks that are based on the same DB driver API. The only one issues with JDBC it that it does not support async calls. There are some alternatives DB async drivers incompatible with JDBC, but they are not used by the most popular frameworks, because it is not JDBC.

[Ygg01]

Diesel is Alive, but it is ORM and ORMs are evil :(

I disagree that ORMs are evil. But I'd prefer a bit way more options when it comes to Rust DB options.

P.S. Look at Java world. There are tons of different frameworks that are based on the same DB driver API.

Not just Java. There is Python as well. But what I fear is a large obstacle is that borrow rules make this more complicated than it should be.

[rumatoest]

Diesel is Alive, but it is ORM and ORMs are evil :(

I disagree that ORMs are evil. But I'd prefer a bit way more options when it comes to Rust DB options.

ORMs are dangerous in many cases, because they use different object approach to treat your data and this is not what you really need. But any ORM better should use standartd low level DB API.

P.S. Look at Java world. There are tons of different frameworks that are based on the same DB driver API.

Not just Java. There is Python as well. But what I fear is a large obstacle is that borrow rules make this more complicated than it should be.

But it without it DB API would be extremely fragmented. Developers would not be able switching between DB providers without rewriting all API calls, because thit is how the things are when there is no standard. As for framework developers, right now for any DB they should ether write their own DB drivers or write wrappers to make 3rd party drivers compatible with framework. And this simply reduce amount of DBs that can be supported at the same time.

[Ixrec]

While there's obviously some potential value in a standardized SQL binding layer to make ORMs and DB drivers interchangeable, that's largely a matter of developing such a layer and then convincing the rest of the Rust ecosystem that the value is real. As far as I know, no one has argued that any of this work needs any changes to the Rust language itself, or needs to be done within std, so it seems pretty clearly out of scope for the core Rust teams.

This seems like the sort of thing a Database Working Group would do, if one existed. I'd suggest anyone who feels strongly about this should try forming such a working group to actually develop these bindings and prove they'd be worth standardizing on.

[rumatoest]

that's largely a matter of developing such a layer and then convincing the rest of the Rust ecosystem that the value is real

That is why I think that rust developers from Mozilla must be involved. It should no be the part for std crate. It must be backed up by Mozilla in some way.

Having even RFC draft of DB API backed by Rust developers is way more better, than waiting that rust ecosystem accidentally came to some standard by itself. Probably it would come to some standard in 10 years or so, but this is unacceptable time wasting and huge stopper for pushing Rust into masses.

P.S. I would gladly participate in such project but only if it will be backed by Rust team. In other case such initiative would likely to fail.

[Ixrec]

I don't think the pessimism about non-formally-Rust-backed initiatives is supported by the track record of the Rust ecosystem thus far. serde and http and wasm-pack and even diesel and many others seem like clear cases of the ecosystem standardizing on something that needed standardizing in far less than a decade. It is true that many of these standards were developed by someone on a Rust team, but many of them were not, and even the ones that were seem like they could've succeeded regardless (in particular, serde displaced the rustc-serialize crate that Rust teams were obviously involved with).

[mamcx]

I have made a ORM in each language I have worked on (at least 7). I have used at least as much others ORMs in many platforms. I like databases so much, that I'm trying to build a relational language!

1- No "ORM" or "micro-orm" will work for all. None. Is just a fact that you will learn on The Vietnam of Computer Science.

This is focused on OOP, but the impedance mismatch is much more generalized. No language that is not relational will be good enough.

2- But a lot can be automated/simplified. Mapping, for example. With the From trait rust have something very powerful that is not matched elsewhere (that I know or remember).

3- Instead, do similar to Python Database API Specification. Give some traits and features to be used as foundation for any database/orm layer. I think python have almost the best API specification around, and also is kind of easy of fulfill (other languages have things similar, but much more arcane to plug into).

4- The main thing is have a narray-like interface tailored for tabular data. That is all. Give From traits to convert the data and let each ORM layer to build on top.

[Ygg01]

I like databases so much, that I'm trying to build a relational language!

Don't like all databases come with a relational language (SQL)?

As for the rest of the argument, I'd consider it more plausible if there weren't tools that manage to abstract the divide in a really elegant manner like jOOQ and SqlAlchemy.

[mamcx]

Don't like all databases come with a relational language (SQL)?

Yes, but I'm not saying "building a RDBMS", instead "a language" like python, Lua, etc...

As for the rest of the argument, I'd consider it more plausible if there weren't tools that manage to abstract the divide in a really elegant manner like jOOQ and SqlAlchemy.

Pay attention to how them do that. Is far from simple. Sql Alchemy/jooq use all the tricks in the book to try to make a decent interface. Something like the DB-API is just a step towards build something like sql alchemy, but exist a lot to do before get there.

[rdbc-root]

A new org/repo has just been created to design and build the basic DB connectivity layer for Rust.

https://github.com/rust-dbc/rdbc

Please request membership and contribute

Thank you

[weiznich]

As diesel core team member I want to use this possibility to clarify some common misunderstandings about diesel.

@rumatoest

Disel is Alive, but it is ORM and ORMs are evil :(

I see diesel not as ORM in it's core functionality. Yes there are certain ORM functionalities, but even those are a bit different than most traditional ORM's. For me diesel is mostly a query builder with some attached data mapping functionality. You have several layers of abstraction there:

• An abstract notation of backend representing different database system
• A set of connetion implementations that allow to connect to a specific backend
• A set of type mappings between SQL types and rust types. Those depend on the backend, because different backends may support different types. (For example SQLite supports much less types than PostgreSQL).
• An abstract notation of different QueryFragments that represents various SQL expressions. Those are fully typed in that way that for each expression the SQL type is known at compile time.
• An abstract way to execute queries that may return a result (Where the type of the result is known, because we know the corresponding SQL types of all involved query fragments.)
• Several ways to map "objects" to tables: (Those are mostly useful if you work for example on some sort of REST API where you easily can put an additional #[derive(Serialize)] (or #[derive(Deserialize)] on those structs).
  • Queryable to map query results to structs. (Note the name. The idea here is not to map a table to a struct, but to map the result of a specific query to a struct. Therefore multiple structs implementing Queryable can exist for the same table and a struct implementing Queryable can contain values from multiple tables. (Implementation wise this is noting more than mapping a tuple of values to a struct with a compatible set of fields))
  • Insertable and AsChangeset to simplify insert and update statements. Those are only useful if you directly get those data from some sort of serialization format. Otherwise just prefer writing explicit value clauses.
• Additionally a raw sql interface is provided that could be used as a fallback if something is not supported by the query dsl. (To make that clear: This is not just there as an ugly workaround, that's an equal part of our api that should be used.)

The backend and connection abstraction are fundamental, all other parts could be combined as needed (or wanted). So if someone is just interested in a common interface to execute raw sql queries just grab sql_query and ignore the rest and so on.

@mamcx

1- No "ORM" or "micro-orm" will work for all. None. Is just a fact that you will learn on The Vietnam of Computer Science.

Sure no approach will work for everyone. (That's a bold argument that some SQL binding does not belong into a standard library!). Therefore we have several ways in diesel to provide hand written SQL pieces. That said: The linked article is quite old(2006) so some points have already been solved in my opinion. So let's get quickly through those points:

• The Object-to-Table Mapping Problem and The Partial-Object Problem and the Load-Time Paradox -> As noted above: diesel does not map objects to tables, but rather queries to structs. The difference here is that this is now not a 1:1 mapping but more a n:m mapping, meaning that each table could be related to multiple structs and each struct could be related to multiple tables. This allows the database schema and the "object" layer to evolve into different directions.
• The Schema-Ownership Conflict and The Dual-Schema Problem -> For diesel the schema is owned by the database. All modifications are directly done in the database using sql. Diesel mirrors the relevant part for the application as compile time information. Those information could be received directly from the database at compile time and contain basically the same information you must assume if your write your queries manually as raw sql (table names, column names and types).
• Entity Identity Issues -> Diesel does not add any caching here. Also I don't see how this problem applies to diesel because a struct is either a input for a query or the result of a query. Not both at the same time (unless that's explicitly choosen by the developer). For results the "identity" is provided by the DBMS: Each record represents a entry in the DBMS. For inputs the "identity" is provided by the query taking the import.
• The Data Retrieval Mechansim Concern -> Diesel basically chooses the second approach described there, with a "minor" modification: We do not pass strings as argument by zero sized types. This enables several opertunities: a) It allows us to type check the query at compile time. b) It allows us to get the result type of the query at compile time -> We can easily map that to a struct c) It allows us to skip the query building in some cases completely (by using TypeId and prepared statements In my experience complex queries are not becoming more complex than the corresponding SQL.

That written: Diesel has currently it's own set of issues when it comes to highly dynamic queries where not even the type and number of output values is known at compile time. (That is fixable, just needs some work). Also it is currently required to know the used backend at compile time. (It's possible to write code that supports multiple backends but that could be easier (== not requiring that much trait bounds))

@rdbc-root

A new org/repo has just been created to design and build the basic DB connectivity layer for Rust.

Obviously I'm biased here but that I think that's already done by the connection trait in diesel. Beside of that I'm happy to see an alternative approach on that problem :wink:

Finally my opinion to the question if something like a basic DB connectivity layer should be part of the standard library: No, because that would basically froze the design and prevent in cooperating possible future improvements. (The DBMS development by itself is not frozen so I assume that there will be changes over time how such an interface could look like.) Additionally it's possible to write that as third party crate and it's not something that is fundamental for all kinds of applications. That said: There are several things that could improved on the language itself to simplify the implementation as crate. Those things are mostly related to the trait system (in detail specialisation and tweaks to the coherence rules).

[rumatoest]

@rdbc-root

A new org/repo has just been created to design and build the basic DB connectivity layer for Rust.

It would not work for you this way. Only until you pull a hell of the effort to create 100% API + some implementation by yourself. And only if your vision would be at least at 50% compatible with others needs, that maybe you'll have a chance to make it standard.

@weiznich Your connection trait not bad, but only for your framework:

• QueryResult returned in synchronous way, which nowadays are not good for a standard. I think that it is better to implement only async API calls, because you are able to make/simulate sync calls using async API but not opposite
• Compile time query builder is not a bad idea, but all query builders are not good at supporting specific DB features like SELECT * FROM students WHERE scores @> '{97}' AND address->'city' = 'SYRACUSE'::jsonb;
• Also you presume that query can be only string, which is true for many DB including NoSQL, but there are some DB that may have binary protocol and maybe passing some trait as a query is not so bad idea, but I have a doubts about how rust would handle such behavior.

[weiznich]

@rumatoest

• QueryResult returned in synchronous way, which nowadays are not good for a standard. I think that it is better to implement only async API calls, because you are able to make/simulate sync calls using async API but not opposite

I think it's not the right solution to just make the abstraction async. Not every database system out there provides an async API (Notably sqlite does not for example.) Additionally async adds some overhead, both mentally for the implementation and performance wise if the implementing type does not support it. That brings me to the conclusion that it's probably better to have both: An async and a sync interface that could be implemented depending on the actual support. The diesel team actually works on the first one, but to make a long story short: It's not that simple than just make every function returning a future. To have an actual ergonomic usable API much more needs to be done. (For example: Common connection implementations are not Sync, that means returning a future that references such a connection makes that future !Sync which prevents that it is executed using tokio which is basically a deal breaker.)

• Compile time query builder is not a bad idea, but all query builders are not good at supporting specific DB features like SELECT * FROM students WHERE scores @> '{97}' AND address->'city' = 'SYRACUSE'::jsonb;

There is nothing in diesel preventing someone from writing the needed operators to support those query on a specific backend. It's just build in there to say that a given expression is only valid for certain backends. For example see the implementation of the PostgreSQL any function (It's just not done for some of them because nobody had enough interest to do that.)

• Also you presume that query can be only string, which is true for many DB including NoSQL, but there are some DB that may have binary protocol and maybe passing some trait as a query is not so bad idea, but I have a doubts about how rust would handle such behaviour.

The target of diesel is to provide some kind of abstraction for SQL based relational databases. This implies that the query language is text based. This does not assume anything about the actual transport protocol used to talk to the database. (For example PostgreSQL uses some sort of (partially) binary protocol there.)

[bluetech]

A little note on this comment:

Common connection implementations are not Sync, that means returning a future that references such a connection makes that future !Sync which prevents that it is executed using tokio which is basically a deal breaker.

Unless I have it wrong, tokio's multithreaded runtime requires Send, not Sync. I checked the connection types of rust-postgresql and rusqlite, and they are Send. In rust-postgresql's case, it looks like it is already implemented using tokio/futures. Finally, diesel's Connection trait itself requires Send.

[weiznich]

@bluetech

Unless I have it wrong, tokio's multithreaded runtime requires Send, not Sync. I checked the connection types of rust-postgresql and rusqlite, and they are Send. In rust-postgresql's case, it looks like it is already implemented using tokio/futures. Finally, diesel's Connection trait itself requires Send.

That's all right, but I may have simplified things a bit to much above. So let me elaborate on that a bit more:

In the end it boils down to the following: The potential user of such an async implementation will need to write some code that gets a connection and does something with that connection in some async way. Eventually this work will be split up into several function because of reusability, …. Now we want to share the connection between those single methods. We as diesel team think something like the following could be a "common" pattern:

async fn do_stuff() -> QueryResult<Stuff> {
    let connection = await!(get_connection())?;
    let stuff1 = await!(do_stuff1(&connection))?;
    await!(do_stuff2(&connection))?
}

async fn do_stuff1(conn: &PgConnection) -> QueryResult<Stuff> {
}

async fn do_stuff2(conn: &PgConnection) -> QueryResult<Stuff> {
}

So now we pass a reference into a async function, that means the returned future somehow contains that reference. If we now look into the documentation for Send we see that references are only Send if the raw type is Sync, therefore this pattern requires the connection to be Sync to return a future that is Send.

[bluetech]

Thanks for the explanation @weiznich! So, the return type of do_stuff is something like impl Future<Output=_> + 'static, that is the lifetime of the reference doesn't leak, but the !Syncness of the reference does leak, because there is internally some future which holds it. That's interesting. In analogy to synchronous code it is unexpected, but when thinking about how async/await is implemented (state machine enum) it makes sense. (I found another mention in the async book).

[ssokolow]

Also, I may be misremembering but, in the lead-up to Rust 1.0, wasn't there a bit of a big deal made of the realization that, on a theoretical level, if something can implement Send, it should also be possible for it to implement Sync?

[rumatoest]

@weiznich @bluetech We are talking about low level API and it should not be perfect. My point is that if you have async API by default that you can use other library which wraps it in sync interface.

@weiznich acquiring connection probably should be done in a sync way, or it has to be hidden for user. You should have some kind of connection pool and that you run query over it. Because executing query is async you can do getting connection stuff there like let result = await!(connection_poll.execute("SELECT NOW()"))

BTW because low level API should no be extremely pretty we can hide 3rd party async libraries API behind callbacks (something like JS dudes do :( )

connections.executeStringQuery("SELECT now()", | r: ResultSet | {
// Do some async stuff
})

Looks not pretty, but it is only std Rust without 3rd party libraries. And now it is possible to create wrapper/adapter over it with sync or async (tokio) APIs

[rumatoest]

Actually I see it even in more complex way like.

// This part is not async and should be used in other wrapper as it is
let query = db_driver.stringQuery("select * from customers where login=:login, email = ?2");
query.param("login", "vovaput");
query.arg(2, "hacker@kremlin.com");

// And here it the tricky part that can be wrapped.
driver.pool.executeQuery(query, | r: ResultSet | {
// Do some async stuff
});

[andygrove]

I just stumbled across this RFC. I am currently working on an RDBC crate [1] so wanted to share that here. I think it would be good for Rust to have a standardized library like this.

[1] https://github.com/andygrove/rdbc

[Ygg01]

Yeah, I think it might be really good to converge on a shared set DB Trait everyone can use.

[Lokathor]

So, recently the gamedev-wg made a crate called raw-window-handle which is deliberately as small as possible and just provides a simple trait and enum so that the different windowing libs can offer up their OS handle value so a graphic lib that needs it. This way the window lib and the graphics lib can both depend on this intermediate crate and the graphics lib can accept an argument like &impl HasRawWindowHandle and we can avoid having an unstable window lib and unstable graphics lib directly depending in either direction and causing trouble when new releases go out.

Databases might be able to adopt a similar thing where there's some basic trait made and that's put in a stand alone crate that's kept stable and then people can just interact via that agreed upon trait.

However, a database is a lot more complicated thing than "gimme that pointer", so I wouldn't expect it to be so immediately easy.

[mamcx]

I agree. The thing is that in the moment you implement something concrete, you start to bend the API towards how you need/like things.

The problem is how to be sure this abstraction is truly good enough to cover almost all the cases. That is what make this kind of stuff hard.

So I think we need at least 3 parties that agree to work 1)together and at the same time 2)alone; and share a common crate for the specification, where is discussed any change requiere to fulfill the tasks to do.