Synthesize `INSERT` statements for structs

[borchero]

Is your feature request related to a problem? Please describe.

sqlx is an amazing crate and I love the ability to write compile-time-checked queries. The only thing that bothers me whenever I'm using the library is that INSERT statements are very verbose. As tables get wider, queries feel ever more repetitive, and as schemas evolve, updates are required in a bunch of places. I always thought that there should be a "write-equivalent" to the "read-only" interface query_as! that allows me to automatically map Rust types to the underlying tables for INSERT statements.

Describe the solution you'd like

The most natural way to handle this would, in my opinion, be the implementation of a trait (let's call it sqlx::Insert) on structs that can be inserted into a database. The trait implementation can easily be synthesized via a macro. For example, the user could implement

#[derive(sqlx::Insert)]
#[sqlx(table_name = "users")]
struct User {
    email: String,
    name: String,
    age: u8,
}

which then synthesizes a method sqlx_insert which executes

sqlx::query!("INSERT INTO users (email, name, age) VALUES ($1, $2, $3)", self.email, self.name, self.age)

and which is compile-time-checked!

The real power of sqlx can be leveraged by synthesizing ON CONFLICT ... UPDATE queries by querying a table's primary key during compilation. Assuming email is a primary key, we can synthesize (again, compile-time-checked) queries like this:

sqlx::query!(r#"
    INSERT INTO users (email, name, age) VALUES ($1, $2, $3)
    ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name, age = EXCLUDED.age
"#, self.email, self.name, self.age)

The user would not be required to do anything but define the struct above.

Describe alternatives you've considered

n/a

Additional context

The main purpose of this issue is to gauge whether the proposal (or something along these lines) would be in scope for sqlx. If so, I would be happy to contribute, @delsner and I already implemented this as a third-party crate (which is, unfortunately, not yet published). Besides our shared love for open-source, a major driver for the desire to contribute is that synthesizing the ON CONFLICT ... UPDATE queries requires storing the primary key information for offline compilation which currently requires us to duplicate much of the logic in sqlx-cli.

[tobz1000]

@borchero this sounds really useful. If you end up publishing your own crate instead of submitting to sqlx, would you mind updating the issue, so anyone interested can try it out?

[tobz1000]

Related to this functionality - from #2399 (comment):

#328 and sqlxinsert may be of interest also.

[TheGP]

Any progress on this?

[borchero]

Creating a separate crate does not make sense as

synthesizing the ON CONFLICT ... UPDATE queries requires storing the primary key information for offline compilation which currently requires us to duplicate much of the logic in sqlx-cli

I'd be happy to add the functionality to this crate but this would require one of the maintainers to generally approve this idea first :)

[TheGP]

I see, I made my own ORM already thats automatically generates save/update/select/delete statements and maps. I think no need to wait for those slowpokes, we can do it faster.

The problem is Im new in Rust so the implemetation maybe doesnt look good inside but if anyone wants to improve on that I can upload it.

Currently attaching it to struct looks like this

#[derive(Debug, sqlx::FromRow, Deserialize, Serialize, ORM)]
#[orm(table = "cakes")]
struct Cake {
    #[orm(primary_key)]
    id: Option<i32>,
    name: String,
    flavor: Option<String>,
    #[sqlx(skip)]
    test: Option<String>,
}

synthesizing the ON CONFLICT ... UPDATE queries requires storing the primary key information for offline compilation which currently requires us to duplicate much of the logic in sqlx-cli

What does it mean exactly? I didnt use qlx-cli