Skip to content

Repositories

Annotate an interface with @Repository and the code generator writes its implementation: the CRUD operations of its entity, and every @Query method you declare. For an interface named Sqlx4kRepository, the implementation is named Sqlx4kRepositoryImpl.

@Table("sqlx4k")
data class Sqlx4k(
    @Id(insert = true) // Will be included in the insert query.
    val id: Int,
    val test: String
)

@Repository
interface Sqlx4kRepository : CrudRepository<Sqlx4k> {
    // The processor will validate the SQL syntax in the @Query methods.
    // If you want to disable this validation, you can set the "validate-sql-syntax" arg to "false".
    @Query("SELECT * FROM sqlx4k WHERE id = :id")
    suspend fun findOneById(context: QueryExecutor, id: Int): Result<Sqlx4k?>

    @Query("SELECT * FROM sqlx4k")
    suspend fun findAll(context: QueryExecutor): Result<List<Sqlx4k>>

    @Query("SELECT count(*) FROM sqlx4k")
    suspend fun countAll(context: QueryExecutor): Result<Long>

    @Query("SELECT * FROM sqlx4k WHERE id = :id")
    suspend fun existsById(context: QueryExecutor, id: Int): Result<Boolean>
}

Then in your code you can use it like:

// Insert a new record.
val record = Sqlx4k(id = 1, test = "test")
val res: Sqlx4k = Sqlx4kRepositoryImpl.insert(db, record).getOrThrow()
// Execute a generated query.
val res: List<Sqlx4k> = Sqlx4kRepositoryImpl.findAll(db).getOrThrow()

Every method takes a QueryExecutor as its first argument: the database itself, a connection, or a transaction, so repository calls compose with the rest of your queries. To pass it as a context parameter instead, see Context parameters.

CRUD operations

Because the interface extends CrudRepository<T>, the generated implementation also provides the CRUD operations of the entity:

Method Returns What it does
insert Result<T> Inserts the entity and returns it as stored, with the columns generated by the database read back via RETURNING (e.g. an auto-incremented id).
update Result<T> Updates the row matched by the entity's @Id and returns the entity as stored.
delete Result<Unit> Deletes the row matched by the entity's @Id.
save Result<T> Inserts the entity if it is new, updates it otherwise: an Int/Long id of 0 (or null, for a nullable id) marks a new entity.
batchInsert Result<Long> Inserts many entities with a single statement; see Batch operations.
batchUpdate Result<Long> Updates many entities with a single statement; see Batch operations.

How the entity maps to the table (column names, columns excluded from writes, optimistic locking) is described in Entities.

Note

save decides between insert and update from the id, so it needs an Int or Long @Id. With any other id type (e.g. a Uuid set by the application), call insert or update explicitly.

Query methods

A @Query method runs the SQL of its annotation, with the method's parameters bound by name (:id binds the id parameter). Its name prefix determines its shape and expected return type:

Prefix Return type Notes
findAll / findAllBy… Result<List<T>>
findOneBy… Result<T?>
countAll / countBy… Result<Long>
existsBy… Result<Boolean>
deleteAll / deleteBy…, execute… Result<Long> The number of affected rows.
streamAll / streamAllBy… Flow<T> Declared as non-suspend functions.

stream* methods stream the rows instead of loading them all: the query runs when the flow is collected, and a failure fails the collection. They fetch rows in chunks of @Query(fetchSize = …) rows (the driver's default when unset).

@Repository
interface EventRepository : CrudRepository<Event> {
    @Query("SELECT * FROM events WHERE type = :type", fetchSize = 500)
    fun streamAllByType(context: QueryExecutor, type: String): Flow<Event>

    @Query("DELETE FROM events WHERE created_at < :before")
    suspend fun deleteByCreatedAtBefore(context: QueryExecutor, before: Instant): Result<Long>
}

The SQL of every @Query is checked during the build: the method name must match the statement kind (a find* must be a SELECT, a delete* a DELETE, …), the columns must exist on the entity, and more. See Validation for the checks, and for @Query(checkSyntax = false) and @Query(checkSchema = false), which turn them off for a single query.

Row mapping

By default, the generated repository maps rows with the auto-generated RowMapper of its entity (e.g., Sqlx4kAutoRowMapper). You can override this behavior by explicitly providing a custom mapper:

@Repository(mapper = CustomRowMapper::class)
interface Sqlx4kRepository : CrudRepository<Sqlx4k>

See Mapping rows for how to write one.

Repository interfaces

Pick the base interface by how the methods take their QueryExecutor and report their results:

Every one of them extends CrudRepositoryHooks<T>, whose hooks you can override for logging, metrics and tracing. For unit tests, the generator can also write an in-memory implementation of every repository.

For more details, take a look at the examples.