How to get the next auto-increment id in Android room?

Here is my room entity object:

@Entity(tableName = "user_account", indices = [Index(value = ["user_name", "user_type"], unique = true)])
data class DataUserAccountEntity(
    @PrimaryKey(autoGenerate = true) @ColumnInfo(name = "auto_id")    val autoId:    Int,
    @NonNull                         @ColumnInfo(name = "user_name")  val userName:  String,
    @NonNull                         @ColumnInfo(name = "user_photo") val userPhoto: String,
    @NonNull                         @ColumnInfo(name = "user_type")  val userType:  Int,
)

Here is my Dao entity object:

@Dao
interface DataUserAccountDao {
    @Query("SELECT * FROM user_account WHERE auto_id = :autoId LIMIT 1")
    fun getUserAccount(autoId: Int): DataUserAccountEntity

    @Query("SELECT * FROM user_account ORDER BY auto_id ASC")
    fun getAllUserAccounts(): List<DataUserAccountEntity>
}

Since auto_id is set to @PrimaryKey(autoGenerate = true), how would I query room for the next value?

(i.e. I am looking for the auto_id that would be generated if I insert a new row into the local database right now)

Solution

Although I appreciate the response, this does not solve my problem. I need the number BEFORE insertion.

If autoGenerate=true is coded then you can use:-

@Query("SELECT seq+1 FROM sqlite_sequence WHERE name=:tableName")
fun getNextRowidFromTable(tableName: String): Long

HOWEVER, there is no guarantee that the next allocated value will be 1 greater than the last and thus the value obtained from the query. As per:-

The behavior implemented by the AUTOINCREMENT keyword is subtly different from the default behavior. With AUTOINCREMENT, rows with automatically selected ROWIDs are guaranteed to have ROWIDs that have never been used before by the same table in the same database. And the automatically generated ROWIDs are guaranteed to be monotonically increasing.

and

Note that "monotonically increasing" does not imply that the ROWID always increases by exactly one. One is the usual increment. However, if an insert fails due to (for example) a uniqueness constraint, the ROWID of the failed insertion attempt might not be reused on subsequent inserts, resulting in gaps in the ROWID sequence. AUTOINCREMENT guarantees that automatically chosen ROWIDs will be increasing but not that they will be sequential.

What coding autoGereate=true does is include the AUTOINCREMENT keyword. This doesn't actually cause auto generation rather that for every table (using Room at least) a value is generated an placed into a hidden column rowid. If a column is specified with a type of INTEGER and the column is the PRIMARY KEY (not part of a composite primary key) then that column is an alias of the rowid. If such a column has a value specified for the column when inserting the row then that value (as long as it is unique) is assigned to the column (and therefore rowid).

AUTOINCREMENT is a constraint (rule) that enforces the use of a value higher than any that have been assigned (even if such rows are deleted).

AUTOINCREMENT handles this subtle difference by using the sqlite_sequence table to store the assigned rowid or alias thereof obviously updating the value to always be the highest. The sqlite_sequence table will not exist if AUTOINCREMENT aka autoGenerate=true is not coded in any @Entity annotated classes (which are passed to the @Database annotated class via the entities parameter of the annotation)

You may wish to refer to https://www.sqlite.org/autoinc.html

For a solution that is less likely to result in missed sequence numbers you could instead not use AUTOINCREMENT aka autoGenerate= true. This does mean another subtle change to cater for the auto generation and that is making the auto_id nullable with a default value of null.

e.g.

@Entity(tableName = "user_account", indices = [Index(value = ["user_name", "user_type"], unique = true)])
data class DataUserAccountEntity(
    @PrimaryKey/*(autoGenerate = true)*/ @ColumnInfo(name = "auto_id")    val autoId:    Int?=null /*<<<<< CHANGED*/,
    @NonNull @ColumnInfo(name = "user_name")  val userName:  String,
    @NonNull                         @ColumnInfo(name = "user_photo") val userPhoto: String,
    @NonNull                         @ColumnInfo(name = "user_type")  val userType:  Int,
)

As sqlite_sequence will not exist or not have a row for this table then you cannot use it to ascertain the next auto_id value.

So you could have:-

@Query("SELECT COALESCE(max(auto_id),0)+1 FROM user_account")
fun getNextAutoId(): Long

This would work, due to the COALESCE function changing null into 0, even if there were no rows and return 1.

Even still there is still no guarantee that the value will be in sequence. However, more likely and predictable than if using AUTOINCREMENT as the issue with AUTOINCREMENT is due to sqlite_sequence being updated but then the row not being inserted (rolled back).

However, IF the sequence number reaches the value of 9223372036854775807 then instead of an SQLITE_FULL error that would happen with AUTOINCREMENT (it cannot break the rule and cannot have a larger value) SQLite will try to find an unused value (and therefore lower value (unless getting even deeper and using negative values)).

You could mimic sqlite_sequence by defining a table with two columns (only one could be used but two name and seq would cater for other tables). You could compliment this with a TRIGGER so that an INSERT automatically sets the new value (prone to misuse). Room doesn't support TRIGGERS but doesn't complain if you include them (e.g. via a callback).

Saying all that when it boils down to it. The intended purpose of the rowid or an alias thereof is for the unique identification of a row. SQLite has been written with this in mind (such as up to twice as fast location of rows as the rowid can be considered a super/optimized index). Other uses of the rowid/alias thereof will always have some potential issues.

As such it is not recommended to use them for anything other than their intended use.