Database Connections and Sessions

last_insert_rowid() is a built-in SQLite function that returns the ID of the most recently inserted row in your current database connection.

Programming

The Missing ID After INSERT

When a row is inserted into a table with an auto-incrementing primary key, the database assigns an ID automatically. The INSERT statement creates the row, but the statement alone does not tell the application which ID was assigned. That becomes a problem when the application must immediately connect the new row to another record, display its ID, construct a URL, or store the ID for later use.

The essential idea is a two-part operation: first insert the row, then ask the same database connection for the ID it most recently received. In SQLite, SELECT last_insert_rowid() returns that connection's most recently inserted row ID.

How does an application connect a newly inserted parent row to later data when the database generated the row's ID?

The Insert-to-Retrieval Sequence

The database updates its record of the most recent inserted ID as part of the INSERT process. Conceptually, it validates the supplied data, finds the next available ID, writes the row, and updates the most-recent-ID tracking for the current connection. A following SELECT last_insert_rowid() reads that tracking value and returns it.

What happens next when an INSERT creates a row and the application retrieves and uses the generated ID immediately afterward?

Worked example

Recovering the ID of a newly inserted row

An application inserts a new row whose primary key is assigned automatically. It needs that ID to connect the row to related data.

1
Create the row
The application executes its INSERT through a database connection. SQLite assigns an ID to the new row.
2
Ask for the most recent ID
Immediately after the INSERT, the application executes SELECT last_insert_rowid() using the same connection.
3
Capture the result
The returned ID is stored in application code rather than left only in the database response.
4
Use the ID
The application can use the stored ID to link related records, construct a URL, display it to a user, or perform another operation.

Result

The application knows which database-generated ID belongs to the row it just inserted and can use that ID immediately.

Connection-Local Tracking

Definition

last_insert_rowid() is a built-in SQLite function that returns the ID of the most recently inserted row in your current database connection.

Connection-local means that each database connection maintains its own record of the most recently inserted row ID. The function does not ask for the most recent ID across every user or every connection. It asks for the value tracked by the connection making the SELECT.

If two connections insert rows at nearly the same time, which newly inserted ID does last_insert_rowid() return for each connection?

In the real world

Suppose User A inserts a row and receives ID 100 while User B inserts another row at nearly the same time and receives ID 101. A call to last_insert_rowid() through User A's connection returns 100, while a call through User B's connection returns 101. The inserts do not replace one another's connection-local tracking values.

Using the Retrieved Value

Retrieving the ID is only useful if the application captures the returned value. In application code, the usual pattern is to execute the INSERT, immediately execute SELECT last_insert_rowid() on the same connection, and store the result in a variable or another application value.

Stage	What happens	Why it matters
INSERT	The database creates the row and assigns its ID	The row now has an identifier, but the application does not yet know it
SELECT last_insert_rowid()	The current connection returns its most recently inserted row ID	The application can identify the new row
Capture	Application code stores the returned value	The ID remains available for immediate use
Follow-up	The application uses the ID for a related record, URL, display, or later operation	The new row can participate in the next task

The ID moves from database-generated state into an application-managed value.

Use SELECT last_insert_rowid() immediately after the INSERT.
Run the retrieval through the same database connection that performed the INSERT.
Capture the returned ID in application code.
Use the captured value for related records, URLs, user displays, or subsequent operations.

Mistakes with Generated IDs

Common mistake
Assuming the INSERT statement alone tells the application the assigned ID.
What it looks like
The row is inserted, but the application never asks for the generated identifier.
Why it fails
The INSERT statement alone does not reveal which ID the database assigned.
Do this instead
Immediately execute SELECT last_insert_rowid() through the same connection.
Common mistake
Retrieving the ID from a different connection.
What it looks like
The INSERT runs through one connection, but last_insert_rowid() runs through another.
Why it fails
The function returns the most recently inserted ID tracked by the current connection.
Do this instead
Perform the INSERT and the retrieval on the same database connection.
Common mistake
Retrieving the ID but failing to store it in application code.
What it looks like
The database returns the value, but the application discards it before the next operation.
Why it fails
The application cannot use the ID later if it has not captured the returned value.
Do this instead
Store the returned ID in an application variable or other application-managed value.
Common mistake
Treating the function as a global latest-ID lookup.
What it looks like
An application expects the function to return whichever row was most recently inserted by any user.
Why it fails
The tracking value is connection-local rather than shared across all users and connections.
Do this instead
Interpret the result as the most recently inserted row ID for the connection making the call.

Edge case

Near-simultaneous inserts by different users do not change the meaning of the function. Each connection keeps its own most recently inserted ID, so the result belongs to the connection that performs SELECT last_insert_rowid().

Check Your Understanding

MEDIUM

An application inserts a row through Connection A. Before retrieving the ID, another user inserts a row through Connection B. Which connection's ID should Connection A receive when it executes SELECT last_insert_rowid(), and what should the application do with the returned value?

Hints

Focus on the phrase current database connection.
The source describes separate results for User A and User B.
The returned value should be captured before it is needed for a follow-up operation.

Your attempt (not saved)

Worked example

Answering the connection-scope question

Determine what Connection A receives after Connection A and Connection B insert rows at nearly the same time.

1
Identify the relevant scope
last_insert_rowid() is connection-local, so the lookup uses Connection A's tracking record.
2
Select the result
Connection A receives the ID of the row most recently inserted through Connection A, not the ID inserted through Connection B.
3
Preserve the result
The application should store the returned ID so it can link related records, construct a URL, display the ID, or perform another operation.

Result

Connection A receives its own most recently inserted row ID, and the application should capture that value for immediate or later use.

Key Takeaways

1
An auto-incrementing primary key is assigned by the database, and the INSERT statement alone does not tell the application which ID was assigned.
2
SELECT last_insert_rowid() retrieves the most recently inserted row ID for the current SQLite connection.
3
The INSERT and the retrieval should occur on the same connection, with the retrieval performed immediately afterward.
4
Connection-local tracking prevents simultaneous inserts by different connections from interfering with one another's ID retrieval.
5
The application should capture the returned ID so it can link related records, construct URLs, display the ID, or perform subsequent operations.

At a glance

The database assigns the new row's ID, but an INSERT statement alone does not reveal that ID to the application.
Use SELECT last_insert_rowid() immediately after the INSERT through the same connection.
The function is connection-local, so each connection retrieves its own most recently inserted row ID.
Capture the returned value in application code for linking, displaying, storing, constructing URLs, or performing follow-up operations.

More concepts starting with D