Get row id for inserted row in sqldelight

Question

I'm trying to use sqldelight to insert an item, but to also return the id of the item after it's inserted. There doesn't seem to be a way to do this in sqldelight.

I can call something like SELECT last_insert_rowid(); after I do the insert, but is this the best way to move forward?

Glans

If my answer helped you, could you mark it as accepted? Thanks. – Ehrlich

Answer 1

You can add this command to your .sq file:

lastInsertRowId:
SELECT last_insert_rowid()
;

And it will return you an id of last inserted row.

Please refer to this issue on Github for more info.

Ehrlich

Thanks for this answer, I was looking for a bit. – Polinski

Answer 2

The question was asked 5 years ago, but is still relevant))

My solution is that, within one transaction, we insert data and immediately request the maximum value of the PRIMARY KEY field.

Here's my example.

DayliForecast.sq:

import kotlin.Int;

CREATE TABLE IF NOT EXISTS DailyForecast (
    pid             INTEGER PRIMARY KEY AUTOINCREMENT DEFAULT NULL,
    year            INTEGER AS Int  NOT NULL,
    month           INTEGER AS Int  NOT NULL,
    day_of_month    INTEGER AS Int  NOT NULL,
    hours_of_sun    REAL            NOT NULL DEFAULT 0,
    forecast_key    TEXT            NOT NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS DailyForecastDate ON DailyForecast (
    year            DESC,
    month           DESC,
    day_of_month    DESC,
    forecast_key    DESC
);

insert:
REPLACE INTO DailyForecast
    VALUES (?,?,?,?,?,?);

lastPid:
SELECT MAX(pid) FROM DailyForecast;

selectByDateRange:
SELECT * FROM DailyForecast
    WHERE
        forecast_key = ?
        AND year >= ?
        AND month >= ?
        AND day_of_month >= ?
        AND year <= ?
        AND month <= ?
        AND day_of_month <= ? LIMIT 5;

ForecastDbRepositoryImpl:

class ForecastDbRepositoryImpl(private val database: Database): ForecastDbRepository() {

    override suspend fun insertDailyForecast(dailyForecast: DailyForecast): Long {
        return database.transactionWithResult {
            insertForecast(dailyForecast)
        }
    }

    override suspend fun insertDailyForecast(dailyForecasts: List<DailyForecast>): List<Long> {
        return database.transactionWithResult {
            dailyForecasts.map { insertForecast(it) }
        }
    }

    private fun insertForecast(dailyForecast: DailyForecast): Long {
        database.dayliForecastQueries.insert(
            pid = dailyForecast.pid.takeIf { it >= 0L },
            year = dailyForecast.year,
            month = dailyForecast.month,
            day_of_month = dailyForecast.day_of_month,
            hours_of_sun = dailyForecast.hours_of_sun,
            forecast_key = dailyForecast.forecast_key
        )
        return database.dayliForecastQueries.lastPid().executeAsOne().MAX ?: 0L
    }
}

I just tested this myself and it works great!

Important addition. If you want auto-increment to work in this example, you need to use just such a constructor: REPLACE INTO DailyForecast VALUES (?,?,?,?,?,?); And pass null to argument named pid

For everything to work as it should, you cannot use the following insertion description:

insert:
REPLACE INTO DailyForecast
     VALUES ?;

Since in the generated class the pid field is not nullable!

Diorio