Database — PostgREST

Supabase exposes your Postgres database over HTTP through a layer called PostgREST — it turns each table into a web endpoint you can read from and write to, with your database’s permission rules (RLS, Row Level Security) enforced on the server so each user only sees what they’re allowed to. The supabase-database module gives you a typed Kotlin way to drive that: think of it as querying your tables in Kotlin instead of writing SQL or hand-building HTTP requests.

It offers a typed CRUD surface (create, read, update, delete), a Kotlin filter DSL for building WHERE conditions, and RPC for calling database functions. Every method comes in two flavours: a typed variant that deserializes the response into your @Serializable model, and a raw variant that hands you back the JSON string for when you need full control.

Start by building a database client from your SupabaseClient, and define a model that matches the shape of a row in your table:

val database = createDatabaseClient(client)
 
@Serializable
data class Todo(val id: String, val title: String, val done: Boolean)

Every call returns a SupabaseResult — see Results & Errors for how to handle the success and failure branches.

Select

Reading rows is the most common thing you’ll do. selectTyped<Todo> fetches rows from a table and deserializes them into a list of your model. The trailing { } block is a query builder: where { } holds the filter, and orderBy / limit / range shape the result — all covered under Filter DSL below.

Columns are referenced through typed Column<T> tokens, so the compiler checks your filters against the column’s type. Declare them by hand, or let codegen emit them:

object Todos {
    val done = Column<Boolean>("done")
    val createdAt = Column<String>("created_at")
}
 
val todos: SupabaseResult<List<Todo>> = database.selectTyped<Todo>(table = "todos") {
    where { Todos.done eq false }
    orderBy(Todos.createdAt, Order.DESC)
    limit(25)
}

Pick the read method that matches how many rows you expect back:

columns accepts PostgREST projection syntax — "*", "id,title", or embedded relations like "*,author(*)". The typed read helpers all accept an optional schema. For PostGIS rows as a GeoJSON FeatureCollection, use selectGeoJson(…) (or pass format = ResponseFormat.GEOJSON to the raw select(…)).

Insert, Update, Upsert, Delete

These are the writes. insert adds new rows, update changes existing ones, delete removes them, and upsert means “insert-or-update” — if a row with the same key already exists it’s updated instead of creating a duplicate (handy for syncing, since running the same upsert twice is safe — it’s idempotent). Note that update and delete take a where block (filter only — no ordering or limit) to choose which rows they affect.

// A partial model for updates — only the columns you want to change:
@Serializable
data class TodoPatch(val done: Boolean)
 
val id = Column<String>("id")
 
database.insertTyped(table = "todos", value = Todo("1", "Ship it", false))
database.insertTypedMany(table = "todos", values = listOf(/* … */))
 
database.updateTyped(table = "todos", value = TodoPatch(done = true)) {
    id eq "1"
}
 
database.upsertTyped(table = "todos", value = todo, onConflict = "id")
 
database.deleteTyped<Todo>(table = "todos") { id eq "1" }

Writes come in typed, raw-string, and *Unit (discard the response body) forms; insert and upsert additionally have *TypedMany for batch inserts. insert accepts upsert; both insert and upsert accept upsertResolution (MERGE_DUPLICATES / IGNORE_DUPLICATES), onConflict, and defaultToNull.

The tuning options returning (REPRESENTATION / MINIMAL), count, and maxAffected live on the raw interface methods (insert / update / delete / rpc) — the typed helpers expose a reduced subset, so drop down to the raw form when you need them.

Filter DSL

This is how you build the WHERE part of a query in Kotlin instead of SQL. The predicate lives in a where { } block (it’s the whole block on update/delete). Operators are infix and run on typed Column<T> tokens — the value type is checked against the column, so age eq "oops" won’t compile. The vocabulary mirrors JetBrains Exposed / Ktorm, so it reads the way Kotlin developers expect. The mental model: each statement is another condition, and multiple statements combine with AND. Use or { } / and { } for explicit grouping.

The examples below assume typed columns are in scope (declared by hand or emitted by codegen):

object Users {
    val status = Column<String>("status"); val role = Column<String>("role")
    val age = Column<Int>("age"); val score = Column<Int>("score")
    val name = Column<String>("name"); val deletedAt = Column<String>("deleted_at")
    val tags = Column<List<String>>("tags"); val body = Column<String>("body")
}

Comparison

The everyday operators — equals, not-equals, and the greater/less-than family (greaterEq/lessEq for >=/<=):

where {
    Users.status eq "active";  Users.role neq "admin"
    Users.age greater 18;  Users.age greaterEq 21;  Users.score less 100;  Users.score lessEq 99
    Users.age within 18..65          // two-sided bound (>= AND <=)
}

Because columns are typed, a boolean column takes a real Boolean — Users.done eq false, never eq("done", "false").

Pattern matching & membership

like/ilike do SQL text matching with % wildcards (ilike is case-insensitive); matches/imatches are POSIX regex; inList checks membership; isNull() / isNotNull() test for NULL:

where {
    Users.name like "%ada%";  Users.name ilike "%ADA%";  Users.name matches "^A"
    Users.name likeAllOf listOf("%a%", "%b%");  Users.name likeAnyOf listOf("%x%", "%y%")
    Users.deletedAt.isNull()
    Users.status inList listOf("active", "pending")
}

Arrays, ranges & full-text

For Postgres-specific column types — array columns and full-text search over text:

where {
    Users.tags contains listOf("kotlin", "kmp")     // also containedBy / overlaps
    Users.body.textSearch("kotlin & multiplatform", type = TextSearchType.WEB_SEARCH)
}

Logical & raw

Group conditions explicitly with or / and, negate a group with not, or drop down to raw to use any PostgREST operator by name when the DSL doesn’t have a dedicated helper:

where {
    or { Users.status eq "active"; Users.status eq "pending" }
    and { Users.age greaterEq 18; Users.age less 65 }
    not { Users.status eq "archived" }
    raw(Users.score, "gte", "10")   // arbitrary PostgREST operator
}

Ordering & pagination

These are query modifiers — they live directly in the read block, outside where { } (which is why they aren’t available on update/delete):

orderBy(Users.name, Order.DESC, Nulls.LAST)   // call again for secondary sort keys
limit(25)
range(0, 24)   // rows 0..24 inclusive (offset pagination)

RPC (stored functions)

RPC (“remote procedure call”) means calling a database function by name — a stored function you’ve written in your database that runs custom SQL or logic server-side. Use it when a single CRUD call isn’t enough: aggregations, multi-step transactions, or anything you’d rather keep in the database. Functions that change data are called with POST and a JSON body; read-only functions can be called with GET.

@Serializable data class StatsReq(val user_id: String)
@Serializable data class Stats(val total: Int)
 
// POST with a JSON body:
val stats: SupabaseResult<Stats> =
    database.rpcTyped<StatsReq, Stats>(function = "get_dashboard_stats", params = StatsReq("123"))
 
// GET (read-only function):
val rows = database.rpcGetListTyped<Todo>(function = "list_todos", queryParams = listOf("limit" to "10"))

RPC mirrors the read API: rpcTyped, rpcListTyped, rpcSingleTyped, rpcMaybeSingleTyped, rpcUnit, rpcCsv, rpcHead — each with a matching rpcGet* GET form. Pass a @Serializable request object or a raw JSON string.

Options reference

The enums you’ll pass to the options mentioned above (count, returning, upsertResolution, and friends), with their accepted values: