Insert data and return the ID of that piece of data

[jing-951]

When I am using sqlx to insert data into the Postgres database, I want to obtain the ID or other information of the data after successful insertion.
For example:

let res=pool. db. execute（
"INSERT INTO t_role_oapi (role_id,oapi_id,oapi_name) VALUES ($1,$2,$3)"
).await;

But execute returns a Result<PgQueryResult, Error>, while the PgQueryResult structure only has a u64 attribute and no corresponding method to achieve my goal.
This problem has been bothering me for several days because I am dealing with a multi table and complex operation. If I cannot obtain the ID during insertion, I need to query again, but this will generate a lot of IO operations. I don't know if there is any way to solve it.

[andrew-caines]

I also have a very simple request of this nature and can not find any documentation anywhere about how to handle it, i can find however, documentation on when it ISNT used.

The query is this simple:

INSERT INTO customers (contact_name,phone_no,email,address,city, province)
VALUES ("test","test","test","test","test","AB")
RETURNING customer_id;

if i use, query_as! with a matching Struct, it doesnt get the customer_id,
if i use a long format

let insert_customer_query = sqlx::query(&insert_cust_sql)
                .bind(job_details.customer_details.name.clone())
                .bind(job_details.customer_details.phone.clone())
                .bind(job_details.customer_details.email.clone())
                .bind(job_details.customer_details.address.clone())
                .bind(job_details.customer_details.city.clone())
                .bind(job_details.customer_details.province.clone())
                .fetch_all(&app_state.db_conn)
                .await;
            match insert_customer_query {
                Ok(data) => println!("ID: {:#?}", data),
                Err(e) => println!("e: {e:?}"),
            }

None of them actually return the values it just show up empty as such

[2024-07-04T01:10:02Z DEBUG sqlx::query] summary="INSERT INTO customers (contact_name,phone_no,email,address,city, …" db.statement="\n\nINSERT INTO\n  customers (\n    contact_name,\n    phone_no,\n    email,\n    address,\n    city,\n    province\n  )\nVALUES\n  (?, ?, ?, ?, ?, ?) RETURNING customer_id as id;\n" rows_affected=0 rows_returned=1 elapsed=4.8858ms elapsed_secs=0.0048858
ID: [
    MySqlRow {
        row: Row {
            storage: b"\0\x1a\0\0\0\0\0\0\0",
            values: [
                Some(
                    1..9,
                ),
            ],
        },
        format: Binary,
        columns: [
            MySqlColumn {
                ordinal: 0,
                name: id,
                type_info: MySqlTypeInfo {
                    type: LongLong,
                    flags: ColumnFlags(
                        NOT_NULL | PRIMARY_KEY | UNIQUE_KEY | UNSIGNED | AUTO_INCREMENT,
                    ),
                    char_set: 63,
                    max_size: Some(
                        20,
                    ),
                },
                flags: Some(
                    ColumnFlags(
                        NOT_NULL | PRIMARY_KEY | UNIQUE_KEY | UNSIGNED | AUTO_INCREMENT,
                    ),
                ),
            },
        ],
        column_names: {},
    },
]

I can see it thinks row returned should be 1, but there is nothing here? There is no value.
I can not belive, inserting a row and returning the id of the created entry is this hard, please tell me what I am missing.
Please link me to or give me hints on how to search the documentation.
On this page
https://docs.rs/sqlx/latest/sqlx/macro.query.html
I can see it specifically calls out to use execute when NOT RETURNING.
None† | .execute(...).await | sqlx::ResultDB::QueryResult | For INSERT/UPDATE/DELETE without RETURNING.

Is there a discord, or chat server or anything where people who use SQLx hang out?

I know this can not be as hard as it seems, and I'm very clearly missing something, please advise,

Thank you,
Andrew

[jing-951]

I have solved this problem, as shown in the example below. Hope to help you.

struct User{
       id:i32,
       name:String,
       age:i8,
}

let ins = UserInsert{name:"xxxxx",age:12};

let user = sqlx::query_as::<_,User>(
       "INSERT INTO t_user (name,age) VALUES ($1,$2)"
).bind(ins.name).bind(ins.age).fetch_one(&pool.db).await;