[sql] lastInsertId return null after insert

[fanesz]

tauri v2, tauri-plugin-sql v2

async create(product: ProductCreate): Promise<number> {
  const query = `
    INSERT INTO products (sku, name, description, brand, price, cost, stock_quantity, stock_threshold, is_active)
    VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9::boolean)
    RETURNING product_id as id
  `;
  const res = await this.db.execute(query, [
    product.sku,
    product.name,
    product.description,
    product.brand,
    product.price,
    product.cost,
    product.stock_quantity,
    product.stock_threshold,
    product.is_active ?? true,
  ]);

  console.log(res);
  return res.lastInsertId;
}

Using postgresql, lastInsertId always return null, according to this document, I did add RETURNING clause, but still return a null.

Created data:

Result log:

[fanesz]

missread, suppose to be ~db.execute~ db.select

[FabianLars]

I'll reopen this for now since we should document this behavior at least.

Right now, the lastInsertId is based on sqlx' methods on the query result https://docs.rs/sqlx/latest/sqlx/?search=last_insert and there is none for postgres (couldnt find out why on first glance).

P.S. would you mind sharing your solution/workaround? It may be interesting for others.

[fanesz]

As documented here, to get any return value from a query, you should use db.select("INSERT INTO ...") instead of db.execute("INSERT INTO ...").

By using this approach, the return value becomes a normal select function, which returns an array of objects.

For example, if you specify RETURNING product_id at the end of the query, it will return [ { "product_id": 1 } ]. Similarly, if you specify RETURNING *, it will return the entire created data.

The conclusion that I get is that it’s not possible to get the lastInsertId when using PostgreSQL with the db.execute function, based on the documentation.

@FabianLars

[FabianLars]

Thanks! Didn't know we had this documented already 😅 that said, I changed it from 0 to null recently so the docs are still wrong.

Thanks again :)