# sqlx4k

A coroutine-first SQL toolkit with compile-time query validations for Kotlin Multiplatform. PostgreSQL, MySQL/MariaDB, and SQLite are supported.

![sqlx4k](<https://smyrgeorge.github.io/sqlx4k/assets/banner.svg>)

**sqlx4k** is not an ORM. Instead, it provides a comprehensive toolkit of primitives and utilities to communicate directly with your database. The focus is on giving you control while catching errors early through compile-time query validation, preventing runtime surprises before they happen (see [SQL syntax and schema validation](<https://smyrgeorge.github.io/sqlx4k/codegen/validation/index.md>)).

The library is designed to be extensible, with a growing ecosystem of tools and extensions like [PGMQ](<https://smyrgeorge.github.io/sqlx4k/extensions/pgmq/index.md>) (PostgreSQL Message Queue), [SQLDelight](<https://smyrgeorge.github.io/sqlx4k/extensions/sqldelight/index.md>) integration, and more.

## Quick look

The suggested way to use sqlx4k: map your tables with `@Table`, declare your queries in a `@Repository` interface, and let the [code generator](<https://smyrgeorge.github.io/sqlx4k/codegen/index.md>) write the implementation (with the CRUD operations), the row mappers, and [validate](<https://smyrgeorge.github.io/sqlx4k/codegen/validation/index.md>) every query while it builds:

```kotlin
@Table("users")
data class User(
    @Id
    val id: Long = 0, // Generated by the database, and read back on insert.
    val name: String,
    @Column(name = "mail_address")
    val email: String,
)

@Repository
interface UserRepository : ContextCrudRepository<User> {
    @Query("SELECT * FROM users WHERE mail_address = :email")
    context(context: QueryExecutor)
    suspend fun findOneByEmail(email: String): Result<User?>
}

// The transaction returns the value of its block: here, the updated user.
val updated: User = db.transaction {
    val alice = UserRepositoryImpl.insert(User(name = "Alice", email = "alice@example.com")).getOrThrow()
    UserRepositoryImpl.update(alice.copy(name = "Alice Smith")).getOrThrow()
}

with(db) {
    val alice: User? = UserRepositoryImpl.findOneByEmail("alice@example.com").getOrThrow()
}
```

See [Repositories](<https://smyrgeorge.github.io/sqlx4k/codegen/repositories/index.md>) for the details.

Or run your SQL directly with the driver:

```kotlin
val db = PostgreSQL(
    url = "postgresql://localhost:15432/test",
    username = "postgres",
    password = "postgres",
)

db.execute("insert into users(id, name) values (1, 'Alice');").getOrThrow()

db.transaction {
    execute("update users set name = 'Bob' where id = 1;").getOrThrow()
    fetchAll("select * from users;").getOrThrow().forEach { println(it) }
}
```

## Features

- **[Supported databases](<https://smyrgeorge.github.io/sqlx4k/databases/postgresql/index.md>)**

  PostgreSQL, MySQL, MariaDB, SQLite and SQLCipher (encrypted SQLite).
- **[Coroutines everywhere](<https://smyrgeorge.github.io/sqlx4k/guide/index.md>)**

  Every query API is a `suspend` function returning a `Result`, on non-blocking I/O on every platform.
- **[Gradle plugin](<https://smyrgeorge.github.io/sqlx4k/getting-started/gradle-plugin/index.md>)**

  One `sqlx4k { }` block adds the driver, the extensions and the code generator.
- **[Code generation](<https://smyrgeorge.github.io/sqlx4k/codegen/index.md>)**

  CRUD and `@Repository` implementations, row mappers, batch operations, optimistic locking.
- **[Compile-time validation](<https://smyrgeorge.github.io/sqlx4k/codegen/validation/index.md>)**

  `@Query` SQL is checked for syntax, columns, tables and statement kind during the build.
- **[In-memory repositories](<https://smyrgeorge.github.io/sqlx4k/codegen/in-memory-repositories/index.md>)**

  Generated, thread-safe in-memory implementations of your repositories for unit tests.
- **[Transactions](<https://smyrgeorge.github.io/sqlx4k/guide/transactions/index.md>)**

  Block-scoped transactions, savepoints and transaction propagation through the coroutine context.
- **[Migrations](<https://smyrgeorge.github.io/sqlx4k/guide/migrations/index.md>)**

  Run pending migrations and validate the ones already applied.

### Supported databases

- ![MariaDB](<https://img.shields.io/badge/MariaDB-003545?logo=mariadb&amp;logoColor=white>)
- ![MySQL](<https://img.shields.io/badge/MySQL-00758F?logo=mysql&amp;logoColor=white>)
- ![PostgreSQL](<https://img.shields.io/badge/PostgreSQL-336791?logo=postgresql&amp;logoColor=white>)
- ![SQLite](<https://img.shields.io/badge/SQLite-003B57?logo=sqlite&amp;logoColor=white>)
- ![SQLCipher](<https://img.shields.io/badge/SQLCipher-003B57?logo=sqlite&amp;logoColor=white>) (encrypted SQLite, `sqlx4k-sqlite-cipher`)

sqlx4k runs on the JVM, Android, iOS, macOS, Linux and Windows: see [Supported targets](<https://smyrgeorge.github.io/sqlx4k/platforms/index.md>).

## Articles

Short deep-dive posts covering Kotlin/Native, FFI, and Rust ↔ Kotlin interop used in sqlx4k, published on the author's [homepage](<https://smyrgeorge.github.io/>) (under construction):

- *Introduction to the Kotlin Native and FFI:* [\[Part 1\]](<https://smyrgeorge.github.io/posts/sqlx4k---introduction-to-the-kotlin-native-and-ffi-part-1/>), [\[Part 2\]](<https://smyrgeorge.github.io/posts/sqlx4k---introduction-to-the-kotlin-native-and-ffi-part-2/>)
- *Interoperability between Kotlin and Rust, using FFI:* [\[Part 1\]](<https://smyrgeorge.github.io/posts/sqlx4k---interoperability-between-kotlin-and-rust-using-ffi-part-1/>), (Part 2 soon)

## License

MIT, see [LICENSE](<https://github.com/smyrgeorge/sqlx4k/blob/main/LICENSE>).