Generic SQL bindings

[brson]

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.

[huonw]

If we're really into SQL itself, I believe it tokenises, so a macro like let y = 1; sql!(SELECT * FROM some_table WHERE x = $y); might work. I don't know if this is something sane to be part of the main distro.

cc https://github.com/sfackler/rust-postgres https://github.com/linuxfood/rustsqlite

[emberian]

Something like SQLAlchemy Core would be very nice, although I think anything beyond the expression language is going to be ... difficult, to say the least. It uses every metaprogramming feature Python has to be as nice as it is.

[sfackler]

I'd lean against using SQL itself (i.e. sql!(SELECT * FROM ...)) for a implementation agnostic API since different implementations do not have identical syntax.

[kud1ing]

Maybe we could learn something from "Linq to SQL".

[arjantop]

Slick (scala) is also interesting: http://slick.typesafe.com/doc/2.1.0-M2/gettingstarted.html

[kud1ing]

See also https://mail.mozilla.org/pipermail/rust-dev/2014-June/010154.html

[skade]

I am a huge fan of http://sequel.jeremyevans.net/, especially as it has plugins for any special DB under the sun and is concentrated on SQL only (if you ignore the model plugin).

[steveklabnik]

I gave a presentation over the weekend about Rust in front of @jeremyevans, so maybe I piqued his interest enough ;)

[sergiosgc]

Purely a comment from a user perspective: Please aim for a layered approach. Creating a DSL, and possibly a full-fledged ORM is hard and will almost certainly result in a leaky abstraction. A layer for connecting to any relational database and to submit properly escaped (and prepared) SQL queries is a problem that is very well defined and an abstraction that will probably be not leaky. Rust should have such a layer.

Further, the possible implementation of a DSL like SQLAlchemy's Expression API, database introspection, or even a complete ORM can build on top of the basic abstraction. In fact, that is how SQLAlchemy works. The basic methods for preparing and executing statements with arguments are there.

I'd go for a basic statement execution layer, build on top of that a type handling layer (so that one may, for example, use array or gis types in pgsql), then use it to build a higher level DSL layer that masks SQL differences between database engines (including here database reflection/introspection features) and top it off with an ORM layer.

[skade]

@sergiosgc I totally agree that an outside-in approach is problematic there, though the task falls nicely into many parts with clean boundaries. e.g. sequel is actually more of an SQL builder/generator then some kind of higher level abstraction (which possibly does its own optimizations) which happens to ship with adapters that can handle those datasets.

[sergiosgc]

@skade An SQL builder/generator is the domain specific language I mention. It is useful, but it does not fulfill all needs that a query preparer layer does.

Something like sequel is always leaky, because there are two requirements at odds. On one hand, some developers do not care for database independence and care for leveraging database-specific features. On the other extreme, some developers want to use the DSL as insulation from database specific idiosyncrasies.

It is impossible, at that layer, to cater for both.

Fortunately, one does not preclude the other.

[skade]

Have you had a look at sequel? It shines precisely because it cares about database specific behaviour a lot.

Am 05.06.2014 um 17:58 schrieb Sérgio Carvalho [email protected]:

@skade An SQL builder/generator is the domain specific language I mention. It is useful, but it does not fulfill all needs that a query preparer layer does.

Something like sequel is always leaky, because there are two requirements at odds. On one hand, some developers do not care for database independence and care for leveraging database-specific features. On the other extreme, some developers want to use the DSL as insulation from database specific idiosyncrasies.

It is impossible, at that layer, to cater for both.

Fortunately, one does not preclude the other.

—
Reply to this email directly or view it on GitHub.

[sergiosgc]

@skade
Do not read my comment as somehow stating that something like sequel should not exist. It should. It is useful, and from a cursory glance, sequel looks great.

Read my comment as stating that sequel is one layer of a complete SQL library. It should not be the only layer. More so in the SQL library of a systems programming language, where it is sometimes needed to let go of the hurdles of abstraction and directly "go down to the metal".

It is near impossible to have sequel be complete, in terms of implementing what specific databases do. It is extensive work, and made more complex by the conflict with the requirements of isolation of database idiosyncrasies. I am not saying the library is not excellent in what it accomplishes. In fact, I'm stating that, even in an excellent library, it is trivial to find unimplemented specific database features. For example: In sequel, how do you create a tablespace? How do you define the tablespace a table is to be created in?

Instead of aiming for an impossible target (coverage of every feature of every supported database), accept that this is not the layer of the SQL library, but it is a layer (possibly the preferred layer, much as SQLAlchemy does).

[ghost]

I have had relatively good experiences using this for C++ database access: http://soci.sourceforge.net/

At its lowest level, you bind each column to a chunk of memory that you yourself have created. These can be stack-allocated. It's obviously more efficient than forcing allocations for each row and could be a very good low-level library for other people to build things on top of. If you don't know the shape of the data before executing the query (common on the project I work on), you can bind to a "row" class which is basically an untyped tuple.

This would be the layer that @sergiosgc is talking about and the other things could be built on top of it. Unlike something like JDBC, this does not handle retrieving metadata from the database and I think being able to query for metadata in a consistent manner would be a definite win as a mid-level layer. I think Django's ORM, SQLAlchemy, Storm and SQLObject have all reinvented this wheel separately. (see JDBC's java.sql.DatabaseMetaData for inspiration).

If we build something too SQL-specific, we may preclude the use of databases like Cassandra so this layer could just handle connection, statement preparation & execution, parameter binding and reading results.

I would be interested in helping with this effort but I've had a total of about 4 hours' worth of exposure to rust so I'm not sure how much help I could be. For example, I don't know enough about Rust's type system to understand how to let users extend the library with additional column types (for example, PostGIS geometry types).

[pschorf]

Myself and a colleague would also be interested in contributing to this project, but we don't have a ton of rust experience as well. Is anyone with a little more rust experience willing to help get the project started?

[kud1ing]

See also https://mail.mozilla.org/pipermail/rust-dev/2014-June/010332.html

[kud1ing]

Would someone volunteer coordinating the ideas and actionables?

[Wilhansen]

As what @sergiosgc, for an inital "SQL" library, I'd recommend on sticking to creating a DBAL (similar to ODBC/JDBC). That is, don't aim for an ORM, or a DSL for generating SQL queries; because those abstractions tend to leak, and, in addition to taking a lot of time to develop, tend to be supplanted by "the next hip thing".

I'd recommend something along the lines of Anorm: http://www.playframework.com/documentation/2.2.x/ScalaAnorm something which goes along with the query language instead of trying to fight it by abstracting it away.

It should focus on abstracting the following:

1. Connection management. Probably include transactions (?).
2. Command submission. The command could be SQL, JS, or whatever language the backend runs on. This thing is more or less a smarter string interpolation library which prevents injection attacks.
3. Result parsing. For example, quickly turning a row result into a structure.
4. Type serialization (related to 3). It should easily degrade from database-specific types (like PostGIS types) or even application-specific ones (like Postgres enums) to generic ones ("datetime", "string", "int4", etc).

Then ideally, ORMs, SQL-DSL (or whatever the next craze that someone will invent), would build on top of this in the future.

I'm willing to help but I'm not too familiar with Rust and its volatility is holding me back.

[jeltz]

@sergiosgc I am pretty sure the author of sequel would agree with you here since it too has several layers. You can use sequel without touching the query building or ORM parts and just use the connection management, commands submission, and data serialization/deserialization.

@Wilhansen It also needs to handle transactions and prepared statements or else the connection pooling wont work. Another thing which needs to be handled in a generic way is errors.

I too think we should start with providing a basic layer which abstracts all different SQL databases behind one API, and the build ORMs and query DSLs on top of this.

[sergiosgc]

@jeltz @Wilhansen I wholeheartedly agree with both of you.

What is needed now is someone who is familiar with idiomatic Rust. I could write something, but this is not the kind of project to be taken as a learning experience (which would be my case). I'll gladly pickup dredge tasks like documentation or testing (if they somehow get left behind).

[skade]

@sergiosgc Be aware that "idiomatic Rust" does not exist yet. There is a wide range of styles currently, ranging from "C with a different syntax" over "Ruby-inspired" to "I'm not comfortable if a definition doesn't include at least 3 traits". Getting started and trying out what "feels well" is something most Rust developers are doing currently and every such endeavor will probably end up with a lib that will face substantial changes in the future. That's just the nature of a new language!

@steveklabnik puts it quite well, here:
http://www.reddit.com/r/rust/comments/28qfjz/for_a_beginner_what_is_a_good_way_to_learn_how_to/cidhg92

[jeltz]

@kud1ing I looked at your blog and I do not find your proposal very interesting since for an SQL library to be useful you want it to be trivial and smooth to drop down to using raw SQL. This is what makes Sequel stand out compared to many I have used. Sequel also supports lots of database specific features.

But a couple of comments anyway

• Your examples of table definitions and queries are too simple for me to be convinced your idea will scale. I would like to see some unique constraints, exclusion constraints, parameters to foreign keys, etc to be convinced that something will work. I worry that you are severely underestimating the problem. Have you looked at libraries like Sequel and SQLAlchemy?
• The ORM should be an optional library and not a core part of an SQL library.

[kud1ing]

@jeltz: > I looked at your blog and I do not find your proposal very interesting
That's not surprising since i don't have a blog. :)

[kud1ing]

cc @kimhyunkang

[DAddYE]

@brson do you know if there is someone working on this?

@skade, @steveklabnik yep, sequel is awesome we use it in prod and it's perfect.

However, I think to reproduce something equal we need years.

I was thinking to create a very thin layer but easy to extend release by release.

TBH, (don't hate me :D) something very clever and small is http://golang.org/pkg/database/sql/

What about starting from there then see how we can improve it?

One thing that IMHO is also good is:

type Person struct {
    FirstName string `db:"first_name"`
    LastName  string `db:"last_name"`
    Email     string
}

I don't know yet there is a way in rust to assign metadata to struct fields. Maybe:

#[db(automap)]
struct Person {
    first_name: String,
    last_name: String
}

[sfackler]

You can tag fields with attributes as well. For example,

#[db_automap]
struct Person {
    #[primary_key]
    id: i64,
    first_name: String,
    #[db(name="last")]
    last_name: String,
}

[DAddYE]

@sfackler yes!

[lukemurray]

Looks like there is a bit of interest in this. I was looking at a typed safe ORM library similar to Slick, Sequel (Scala) or EntityFramework (.net). But I ran into the issue of the Syntax extensions not having any type checking.

Agree that it should be layered, Ideally something simple like Anorm where we can just use SQL script to and map to types, then we can build other things on top of that.

Is there anyone working on any part of this? Seems like we could all pull together?

[ghost]

i wrote a similar thing for a team of 70 devs. It worked rally well, and was easy for people to get the nag ouff because it was so stupid simple.

1. from a generic xmlish scheme, code gen the serialize types. Many ERD tools will output this BTW. SO you can design it all in an ERD visual tool.
2. often for the rpc layer, you end up having to group the types. You can code gen this layer TOO, from the xml. Big win.
3. code gen the sql
4. code gen the rust that calls the sql
5. generic patterns for paging can easily be put in, and code generatd from it.

it was written in c#. way back 7 years ago.

the code gen approach works as long as you have inheritance. your code gening the base class and the inheriting class (if its NOT there). The inheritting class is where you can write any extra code.
validation code can be gode generated, but busness logic cant :)
So inheritance was super useful for this.

when you compile, you see all the little errors VERY quickly.

we were hitting an oracle database.
you tend to very quickly see patterns in your basic crud sql procs, and can quickly refactor them to be code to be gened.

the main thing is started with a generic XML description, rather than code, so you have a huge amount of scope.

Um did i mention i like the code gen approach :)

[steveklabnik]

I'm pulling a massive triage effort to get us ready for 1.0. As part of this, I'm moving stuff that's wishlist-like to the RFCs repo, as that's where major new things should get discussed/prioritized.

This issue has been moved to the RFCs repo: rust-lang/rfcs#798