sqlx4k

When using coroutines, TransactionContext allows you to propagate a transaction through the coroutine context. This enables composable suspend functions to participate in an existing transaction without explicitly passing Transaction or Driver objects.

• TransactionContext.new(db) { ... }: Starts a new transaction scope.
• TransactionContext.current(): Retrieves the active transaction from the current coroutine context.
• TransactionContext.withCurrent { ... }: Executes a block within the ongoing transaction.
• TransactionContext.withCurrent(db) { ... }: Runs the block in an ongoing transaction if one exists; otherwise, starts a new one with the provided db.

fun main() = runBlocking {
    TransactionContext.new(db) {
        execute("insert into sqlx4k ...").getOrThrow()
        doBusinessLogic()
    }
}

suspend fun doBusinessLogic() {
    val tx = TransactionContext.current()
    tx.execute("update sqlx4k ...").getOrThrow()
}

sqlx4k uses KSP (Kotlin Symbol Processing) to generate CRUD implementations and RowMappers based on your data classes and interfaces.

1. Setup KSP

Add the KSP plugin and the sqlx4k-codegen dependency to your project. You must configure the output-package argument.

2. Define Entities and Repositories

• Annotate data classes with @Table("table_name").
• Annotate interfaces with @Repository and extend CrudRepository<T>.
• Use @Query("SQL") to define custom queries. The method name prefix determines the return type (e.g., findAll returns Result<List<T>>, findOneBy... returns Result<T?>).

3. Usage

Access generated methods via the implementation class (e.g., Sqlx4kRepositoryImpl).

// KSP Configuration
ksp {
    arg("output-package", "io.github.smyrgeorge.sqlx4k.examples.postgres")
}

// Entity
@Table("sqlx4k")
data class Sqlx4k(
    @Id(insert = true) val id: Int,
    val test: String
)

// Repository
@Repository
interface Sqlx4kRepository : CrudRepository<Sqlx4k> {
    @Query("SELECT * FROM sqlx4k WHERE id = :id")
    suspend fun findOneById(context: QueryExecutor, id: Int): Result<Sqlx4k?>
}

// Usage
val res: Sqlx4k = Sqlx4kRepositoryImpl.insert(db, record).getOrThrow()

Android targets require manual setup of native SQLite libraries because the transitive sqlite-jdbc dependency does not include them for Android.

1. Download the required .so files from the xerial/sqlite-jdbc repository for your target architectures (aarch64, arm, x86, x86_64).
2. Place them in the following jniLibs directory structure within your Android module:

src/
└── main/
    └── jniLibs/
        ├── arm64-v8a/
        │   └── libsqlitejdbc.so
        ├── armeabi-v7a/
        │   └── libsqlitejdbc.so
        ├── x86/
        │   └── libsqlitejdbc.so
        └── x86_64/
            └── libsqlitejdbc.so

Mapping Table:

• Linux-Android/aarch64/libsqlitejdbc.so $\rightarrow$ jniLibs/arm64-v8a/libsqlitejdbc.so
• Linux-Android/arm/libsqlitejdbc.so $\rightarrow$ jniLibs/armeabi-v7a/libsqlitejdbc.so
• Linux-Android/x86/libsqlitejdbc.so $\rightarrow$ jniLibs/x86/libsqlitejdbc.so
• Linux-Android/x86_64/libsqlitejdbc.so $\rightarrow$ jniLibs/x86_64/libsqlitejdbc.so

# Example: Setup for ARM64
mkdir -p src/main/jniLibs/arm64-v8a

curl -L https://github.com/xerial/sqlite-jdbc/raw/master/src/main/resources/org/sqlite/native/Linux-Android/aarch64/libsqlitejdbc.so \
  -o src/main/jniLibs/arm64-v8a/libsqlitejdbc.so

To use PGMQ, set up a PostgreSQL connection, initialize a PgmqClient with a PgmqDbAdapterImpl, and manage queues. You can enable enableNotifyInsert on a queue to use PostgreSQL LISTEN/NOTIFY for near real-time processing.

Key operations include:

• create(): Initializes the client and validates/installs PGMQ.
• send(queueName, payload): Sends a message to a specific queue.
• read(queueName, quantity, vt): Reads messages with a specific visibility timeout (vt).
• ack(queueName, msgId): Acknowledges a message so it is removed from the queue.

// Set up a PostgreSQL connection
val db = PostgreSQL(
    url = "postgresql://localhost:5432/test",
    username = "postgres",
    password = "postgres"
)

// Create client (auto-installs/validates PGMQ)
val pgmq = PgmqClient(
    pg = PgmqDbAdapterImpl(db),
    options = PgmqClient.Options(autoInstall = true, verifyInstallation = true)
)

// Create a queue (enable NOTIFY for near real-time consumers)
val queue = PgmqClient.Queue(name = "my_queue", enableNotifyInsert = true)
pgmq.create().getOrThrow()

// Send
pgmq.send("my_queue", "{\"orderId\": 1}").getOrThrow()

// Read + ack
pgmq.read("my_queue", quantity = 1, vt = 30.seconds).getOrThrow().forEach { msg ->
    // process(msg)
    pgmq.ack("my_queue", msg.msgId).getOrThrow()
}