Skip to content
Hacking with RustHacking with Rust
Select theme

How to insert a row and get its id

Short answer: add RETURNING id to the INSERT and read the statement like a SELECT: sqlx::query!("INSERT INTO orders (dish_id, qty) VALUES ($1, $2) RETURNING id", dish_id, qty) followed by .fetch_one(&pool) hands the new id back in the same round trip the insert made. What does not work is asking afterwards: Postgres has no last_insert_rowid(), and a second query through a pool can land on a different connection than the insert did.

A table that numbers its own rows

Section titled “A table that numbers its own rows”

The previous page ends with SELECTs the compiler checks against the schema. Inserting raises a question none of those queries had to answer: where does the new row’s id come from? The chapter’s dishes table sidestepped it — its seed migration typed every id by hand. A table that takes real inserts lets the database do the numbering:

CREATE TABLE orders (
    id      INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    dish_id INT NOT NULL,
    qty     INT NOT NULL
);

GENERATED ALWAYS AS IDENTITY is Postgres’s current spelling of “number this column for me” — every insert without an explicit id gets the next number from a sequence the database owns. It would arrive as a migration file, the way the migrations page sets schemas up. The runnable examples on this page create and empty the table themselves first, so the ids come out the same on every run; treat that block as the page’s reset switch. The CASCADE earns its place: another page in this section puts a payments table in the same database with a foreign key back to orders, and Postgres refuses to empty a table that something still points at.

What execute() hands back

Section titled “What execute() hands back”

An insert is a query with no rows expected back, and sqlx runs it with execute instead of fetch:

src/main.rs
use sqlx::postgres::PgPoolOptions;
use sqlx::PgPool;


#[tokio::main]
async fn main() {
    let pool = PgPoolOptions::new()
        .connect("postgres://postgres:dev@localhost:5432/shop")
        .await
        .expect("failed to connect to Postgres");


    reset(&pool).await;


    let result = sqlx::query("INSERT INTO orders (dish_id, qty) VALUES (2, 1)")
        .execute(&pool)
        .await
        .expect("insert failed");


    println!("{} row affected", result.rows_affected());
}


async fn reset(pool: &PgPool) {
    sqlx::query(
        "CREATE TABLE IF NOT EXISTS orders (
            id      INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
            dish_id INT NOT NULL,
            qty     INT NOT NULL
        )",
    )
    .execute(pool)
    .await
    .expect("create table failed");
    sqlx::query("TRUNCATE orders RESTART IDENTITY CASCADE")
        .execute(pool)
        .await
        .expect("truncate failed");
}
1 row affected

rows_affected counts the rows the statement touched, which is useful for an UPDATE and useless here: the insert always touched one row, and the one number the program actually wants — the new id — is nowhere in the result. sqlx never saw the row; the server inserted it and only reported the count. The insert worked, but the program cannot refer to what it just wrote.

The last_insert_rowid trap

Section titled “The last_insert_rowid trap”

Coming from SQLite, the habit is to insert and then ask the connection: conn.last_insert_rowid() in rusqlite. MySQL has LAST_INSERT_ID(). Postgres has neither function, and asking the server for it stops the build before there is a program to run:

src/main.rs
use sqlx::postgres::PgPoolOptions;


#[tokio::main]
async fn main() {
    let pool = PgPoolOptions::new()
        .connect("postgres://postgres:dev@localhost:5432/shop")
        .await
        .expect("failed to connect to Postgres");


    sqlx::query!("INSERT INTO orders (dish_id, qty) VALUES (2, 1)")
        .execute(&pool)
        .await
        .expect("insert failed");


    let row = sqlx::query!("SELECT last_insert_rowid() AS id")
        .fetch_one(&pool)
        .await
        .expect("query failed");


    println!("id {}", row.id);
}
error: error returned from database: function last_insert_rowid() does not exist at line 636
  --> src/main.rs:15:15
   |
15 |     let row = sqlx::query!("SELECT last_insert_rowid() AS id")
   |               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
   |
   = note: this error originates in the macro `$crate::sqlx_macros::expand_query` which comes from the expansion of the macro `sqlx::query` (in Nightly builds, run with -Z macro-backtrace for more info)


error: could not compile `verify` (bin "verify") due to 1 previous error

(The crate name is the one the check built with; yours will differ.) The macro sent the SQL to the real server during the build, and the server said no. On databases where a function like this does exist, the second problem survives the rename: last_insert_rowid and LAST_INSERT_ID() are questions about a connection, and a pool borrows a connection per statement. The insert may go out on connection A, the follow-up SELECT on connection B, and B’s answer describes some other request’s insert. Postgres’s nearest equivalents, lastval() and currval(), are session-local too — and with a pool, a session is one statement wide. The fix is not to ask afterwards at all.

RETURNING: the insert hands the row back

Section titled “RETURNING: the insert hands the row back”

Postgres can return rows from the statement that wrote them. Adding RETURNING id turns the INSERT into a statement that produces a result set, and the id arrives from the same statement, on the same connection, in the same round trip:

src/main.rs
use sqlx::postgres::PgPoolOptions;
use sqlx::PgPool;


#[tokio::main]
async fn main() {
    let pool = PgPoolOptions::new()
        .connect("postgres://postgres:dev@localhost:5432/shop")
        .await
        .expect("failed to connect to Postgres");


    reset(&pool).await;


    let cart = [(2, 1), (3, 2), (1, 3)];
    for (dish_id, qty) in cart {
        let order = sqlx::query!(
            "INSERT INTO orders (dish_id, qty) VALUES ($1, $2) RETURNING id",
            dish_id,
            qty
        )
        .fetch_one(&pool)
        .await
        .expect("insert failed");


        println!("order {} (dish {} x{})", order.id, dish_id, qty);
    }
}


async fn reset(pool: &PgPool) {
    sqlx::query(
        "CREATE TABLE IF NOT EXISTS orders (
            id      INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
            dish_id INT NOT NULL,
            qty     INT NOT NULL
        )",
    )
    .execute(pool)
    .await
    .expect("create table failed");
    sqlx::query("TRUNCATE orders RESTART IDENTITY CASCADE")
        .execute(pool)
        .await
        .expect("truncate failed");
}
order 1 (dish 2 x1)
order 2 (dish 3 x2)
order 3 (dish 1 x3)

This is the same query! from the previous page, now on a write. The macro described the statement at build time, saw that RETURNING id produces one INT4 column, and made order.id an i32 — a typo in a returned column would stop the build the way a typo in a SELECT does. RETURNING is ordinary Postgres SQL, not something sqlx invented: the same statement runs in psql and in every other language and driver. And because the id comes from the insert itself, there is no second query whose answer can be someone else’s row.

The rest of the row

Section titled “The rest of the row”

RETURNING is not limited to the id. It takes any column list, including columns the statement did not mention — defaults the server filled in, computed values. RETURNING * hands back the whole row:

src/main.rs
use sqlx::postgres::PgPoolOptions;
use sqlx::PgPool;


#[tokio::main]
async fn main() {
    let pool = PgPoolOptions::new()
        .connect("postgres://postgres:dev@localhost:5432/shop")
        .await
        .expect("failed to connect to Postgres");


    reset(&pool).await;


    let order = sqlx::query!(
        "INSERT INTO orders (dish_id, qty) VALUES ($1, $2) RETURNING id, dish_id, qty",
        3,
        2
    )
    .fetch_one(&pool)
    .await
    .expect("insert failed");


    println!("order {}: {} of dish {}", order.id, order.qty, order.dish_id);
}


async fn reset(pool: &PgPool) {
    sqlx::query(
        "CREATE TABLE IF NOT EXISTS orders (
            id      INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
            dish_id INT NOT NULL,
            qty     INT NOT NULL
        )",
    )
    .execute(pool)
    .await
    .expect("create table failed");
    sqlx::query("TRUNCATE orders RESTART IDENTITY CASCADE")
        .execute(pool)
        .await
        .expect("truncate failed");
}
order 1: 2 of dish 3

The same clause answers “which rows did my UPDATE actually change” and “what did that DELETE take away” — put RETURNING * on either statement and the affected rows come back before they are gone. One statement at a time, though, is as far as this page goes. Placing a real order usually means writing two rows — the order and its line items, the second insert needing the id the first one just returned — where both should land or neither. That is a transaction, and it is the next page in this chapter.