# 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`.

```kotlin
@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:

```kotlin
// 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](<https://smyrgeorge.github.io/sqlx4k/guide/transactions/index.md>), so repository calls compose with the rest of your queries. To pass it as a context parameter instead, see [Context parameters](<https://smyrgeorge.github.io/sqlx4k/codegen/context-parameters/index.md>).

## 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](<https://smyrgeorge.github.io/sqlx4k/codegen/batch-operations/index.md>). |
| `batchUpdate` | `Result<Long>` | Updates many entities with a single statement; see [Batch operations](<https://smyrgeorge.github.io/sqlx4k/codegen/batch-operations/index.md>). |

How the entity maps to the table (column names, columns excluded from writes, optimistic locking) is described in [Entities](<https://smyrgeorge.github.io/sqlx4k/codegen/entities/index.md>).

> [!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).

```kotlin
@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](<https://smyrgeorge.github.io/sqlx4k/codegen/validation/index.md>) 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`](<https://smyrgeorge.github.io/sqlx4k/codegen/#auto-generated-rowmapper>) of its entity (e.g., `Sqlx4kAutoRowMapper`). You can override this behavior by explicitly providing a custom mapper:

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

See [Mapping rows](<https://smyrgeorge.github.io/sqlx4k/guide/mapping/index.md>) for how to write one.

## Repository interfaces

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

- [CrudRepository](<https://github.com/smyrgeorge/sqlx4k/blob/main/sqlx4k/src/commonMain/kotlin/io/github/smyrgeorge/sqlx4k/CrudRepository.kt>): an explicit `QueryExecutor` argument, `Result` return types.
- [ContextCrudRepository](<https://github.com/smyrgeorge/sqlx4k/blob/main/sqlx4k/src/commonMain/kotlin/io/github/smyrgeorge/sqlx4k/ContextCrudRepository.kt>): the `QueryExecutor` as a [context parameter](<https://smyrgeorge.github.io/sqlx4k/codegen/context-parameters/index.md>).
- [ArrowCrudRepository](<https://github.com/smyrgeorge/sqlx4k/blob/main/sqlx4k-arrow/src/commonMain/kotlin/io/github/smyrgeorge/sqlx4k/arrow/ArrowCrudRepository.kt>) (using the `sqlx4k-arrow` package)
- [ArrowContextCrudRepository](<https://github.com/smyrgeorge/sqlx4k/blob/main/sqlx4k-arrow/src/commonMain/kotlin/io/github/smyrgeorge/sqlx4k/arrow/ArrowContextCrudRepository.kt>) (using the `sqlx4k-arrow` package)

Every one of them extends `CrudRepositoryHooks<T>`, whose [hooks](<https://smyrgeorge.github.io/sqlx4k/codegen/hooks/index.md>) you can override for logging, metrics and tracing. For unit tests, the generator can also write an [in-memory implementation](<https://smyrgeorge.github.io/sqlx4k/codegen/in-memory-repositories/index.md>) of every repository.

For more details, take a look at the [examples](<https://smyrgeorge.github.io/sqlx4k/getting-started/examples/index.md>).