Statement API

[Virgiel]

Currently, we combine the statement computation and the action of the query into a single function call, which is very ergonomic.

insert_book(&client, &"The Great Gatsby").await?;
books(&client).vec().await?;

This can be suboptimal when, for example, I want to insert several new rows in a single transaction. I'll have to check multiple times for cached statements, which in the worst case results in multiple statement computation. If we decouple the statement from the query action, we could ensure a single cache lookup and statement calculation, the API would be more verbose though.

insert_book().execute(&client, &"The Great Gatsby").await?;
books().vec(&client).await?;

We cache the computed statements, which is a good default because our library encourages the reuse of some SQL queries, but there is no way to execute a query without statement computation or statement caching. We could use the declaration API to prove these features:

insert_book().uncached().execute(&client, &"The Great Gatsby").await?;
books().unprepared().vec(&client).await?;

At the contrary sqlx use an uncached unprepared query as default, using persistent to request a prepared cached statement.

I'm not sure if this is necessary, as a cached prepared statement should suffice. I have also chosen not to show the generated code, but I can provide a hint if necessary.

[LouisGariepy]

statement caching

The statement cache is, as far as I'm aware, only a feature of deadpool, and can be bypassed by unwrapping the deadpool::Client/Transaction into its inner postgres::Client/Transaction, and providing that as GenericClient.

insert_book(&client.client(), &"The Great Gatsby").await?; // No cache with deadpool

[LouisGariepy]

If we decouple the statement from the query action, we could ensure a single cache lookup and statement calculation, the API would be more verbose though.

That's a good idea, I definitely think this would be an improvement.

[Virgiel]

Thinking about all cases, the API could look like this:

// Execute without params
let mut stmt = delete_all_books();
let result = stmt.execute(&client).await?;
delete_all_books().execute(&client).await?;

// Execute with params binding
let mut stmt = insert_book();
let result = stmt.execute(&client, &"The Great Gatsby").await?;
insert_book().execute(&client, &"The Great Gatsby").await?;

// Execute with params struct binding
let mut stmt = insert_book();
let result = stmt.params(&client, Params {name: &"The Great Gatsby"}).await?;
insert_book().params(&client, Params {name: &"The Great Gatsby"}).await?;

// Query without params
let mut stmt = select_all_book();
let mut query = stmt.query();
let result = query.vec().await?;
select_all_book().query().vec().await?;

// Query with params binding
let mut stmt = select_by_name();
let mut query = stmt.query(&client, &"The Great Gatsby");
let result = query.one().await?;
select_by_name().query(&client, &"The Great Gatsby").one().await?;

// Query with params struct binding
let mut stmt = select_by_name();
let mut query = stmt.params(&client, Params {name: &"The Great Gatsby"}));
let result = query.one().await?;
select_by_name().params(&client, Params {name: &"The Great Gatsby"}).one().await?;

The mutability is necessary to lazily prepare the statement, to have a single point of error/await while making it possible to reuse the statement. I don't think this is problematic because I don't think anyone uses the same statement multiple times at the same time.

let mut stmt = select_by_name();
let mut query = stmt.params(&client, Params {name: &"The Great Gatsby"}));
let result = query.one().await?;
// vs
let stmt = select_by_name(&client).await?;
let query = stmt.params(Params {name: &"The Great Gatsby"}));
let result = query.one().await?;

Example using the same statement:

let mut stmt = select_by_name(); 

let query = stmt.query(&client, &"The Great Gatsby"});
let books = query.vec().await?;
let ids = query.map(|book| book.id).vec().await?;

let author = stmt.query(&client, &"In Search of Lost Time"}).map(|book| book.author.to_string()).one().await?;

This is a little more verbose than what we currently have:

// Execute without params
delete_all_books().execute(&client).await?;
// vs
delete_all_books(&client).await?;

// Execute with params binding
insert_book().execute(&client, &"The Great Gatsby").await?;
// vs
insert_book(&client, &"The Great Gatsby").await?;

// Execute with params struct binding
insert_book().params(&client, Params {name: &"The Great Gatsby"}).await?;
// vs
Params {name: &"The Great Gatsby"}.insert_book(&client).await?;

// Query without params
select_all_book().query().vec().await?;
// vs
select_all_book().vec().await?;

// Query with params binding
select_by_name().query(&client, &"The Great Gatsby").one().await?;
// vs
// Query with params binding
select_by_name(&client, &"The Great Gatsby").one().await?;

// Query with params struct binding
select_by_name().params(&client, Params {name: &"The Great Gatsby"}).one().await?;
 Params {name: &"The Great Gatsby"}.select_by_name(&client).one().await?;

[LouisGariepy]

The potential pitfall of recomputing the statement many times is worth making the change imo. I think this API is suitable, even if its more verbose.

[LouisGariepy]

I'm starting to think maybe we should work on this before the release since it will inevitably introduce breaking changes. I guess it depends how much work is involved. What do you think?

[LouisGariepy]

I'm not sure about uncached for the reasons stated above. I guess unprepared could be useful if you have a query that is only ever going to be called once or twice. Pretty specific, but it doesn't hurt the API if prepared is the default :

books().vec(&client).await?; // prepared
books().unprepared().vec(&client).await?; // unprepared

[Virgiel]

Thinking more about it, if it is only called once, the cost of preparing the statement should be negligible. I think we should wait until someone really needs it before implementing it.

[jacobsvante]

The current API is very "unverbose" in my view, so I wouldn't mind a few more extra characters to type if it would mean that the API becomes more clear.

[LouisGariepy]

@jacobsvante

One quirk that I noticed while trying out the lib is that .bind() immediately executes the query for INSERTs without a returning clause. Adding a .execute() call after that would make the API feel more coherent.

Agreed. I'll open an issue right away :) (#154)

Another thing that I think would make the API more natural to use is if the parameter values where provided via a separate function instead of .bind(). E.g. .params (although I understand that this is currently an alternative version of .bind()).

Do I understand correctly that you'd like to pass the connection separately from the bind parameters? If so, I'd have to look more into it, but we could potentially pass the connection in the finalizer method instead (one, all, etc.). Would that make the API clearer?

Hope to contribute one day soon.

Contributions are much appreciated! Feel free to join us on Discord if you want to discuss ideas or guidance with the project's internals.

[jacobsvante]

Thanks I'll do that! I think I might be able to help from October onward.

[jacobsvante]

Do I understand correctly that you'd like to pass the connection separately from the bind parameters? If so, I'd have to look more into it, but we could potentially pass the connection in the finalizer method instead (one, all, etc.). Would that make the API clearer?

Personlly I'd prefer a separate .params() for that.

Or another suggestion which I think would make it even more clear:

.execute() is used for inputting the EXECUTE parameter values and executing the query. Then .opt(), .one() etc just takes out the resulting row(s) from the executed query. The .execute() part would make the API more close to how "raw" Postgres EXECUTE commands are called.

[LouisGariepy]

Sorry, I'm having a bit of trouble understanding. When you feel comfortable doing so, I'd love to see some hypothetical examples using your proposed API 😄

[jacobsvante]

Okay, I'll try to clarify with some examples for the API that I have in mind.

Example 1: Simple select with two filters

// First we create the statement (this example: `SELECT * FROM people WHERE gender = :gender AND age >= :min_age`)
let stmt = find_people();
// Now we bind the parameter values to the statement (note that `.params()` takes on a new role here compared to the current API)
let bound_stmt = stmt.params(&"female", &65);
// Here we decide what type of results we want to be returned
let executable_stmt = bound_stmt.all();
// And finally we execute the query with the given client, in this case returning what `.query()` would do in rust-postgres
let rows = executable_stmt.execute(&client);

Example 2: Optional row

// First we create the statement (this example: `SELECT * FROM people WHERE id = :id`)
let stmt = find_person();
// Binding the parameter values as before...
let bound_stmt = stmt.params(&1337);
// Now we specify that we want an optional result to be returned
let executable_stmt = bound_stmt.opt();
// Execute and return optional result
let optional_row = executable_stmt.execute(&client);

Example 3: Simple insert without returning

// First we create the statement (this example: `INSERT INTO users (email, password) VALUES (:email, :password)`)
let stmt = insert_user();
// Now we bind the parameter values to the statement
let bound_stmt = stmt.params(&"my@email.com", &"totally-safe-password");
// For statements that don't return any rows we use `finalize()`
let executable_stmt = bound_stmt.finalize();
// And then we execute
let num_inserted = executable_stmt.execute(&client);