SQL framework for Kotlin, built on top of JDBC and reflection.
Focus on simplicity and smooth programming workflow.
+290 unit,
+210 e2e,
+30 initialization and
+22 tutorial tests
readme code snippets are
autogenerated
and
tested
+86% code coverage, profiled with benchmarks
|
Extra utils for db-messiah. Support for kotlinx.datetime types, etc... |
GUI for db analysis, to help you create complex JOIN statements for SQL or db-messiah. |
On initialization, all user provided data classes are scanned and hashmaped with reflection.
Base on those hashmaps, user actions like db.table.create<T>(), can be then translated
into prepared SQL statements that are escaped, and free from SQL injections.
Because everything is scanned with reflection, your data classes will
directly represent your whole database structure, without the need of any additional work.
Here is a minimalistic example how would you define database with one table...
data class TestTable(var id: Int? = null, val col0: String, val col1: Double)
val db = SqliteService(
config = Properties().apply { this["jdbcUrl"] = "jdbc:sqlite::memory:" },
ser = SqliteSerializer(
tables = listOf(Table(primaryKey = TestTable::id)),
globalSerializers = BasicTS.sqlite
)
)
Db Messiah provides simplistic but feature complete
clean syntax for database interactions...
db.transaction {
it.table.drop<TestTable>()
it.roolback.all()
it.table.create<TestTable>()
val s0 = it.roolback.savePoint()
it.table.delete<TestTable>()
it.row.insert(TestTable(col0 = "col0", col1 = 1.3))
it.table.delete<TestTable>()
it.roolback.to(s0)
// ...
}
Because it is powered by reflection, it provides simple solution for writing complex queries.
You need to remember only 4 methods: name, column, table, input,
everything else is joust a plain old SQL syntax that you know and love...
val result = it.query.get(output = Out::class, input = parent) {
"""
SELECT ${it.column(Child::value)} AS ${it.name(Out::child_value)},
${it.column(Parent::value)} AS ${it.name(Out::parent_value)}
FROM ${it.table<Child>()}
JOIN ${it.table<Parent>()} ON ${it.column(Parent::pk)} = ${it.column(Child::parent_pk)}
WHERE ${it.column(Child::value)} = ${it.input(Parent::value)}
"""
}
This gives db-messiah unparalleled simplicity which lowers time of development.
The downside is that it provides simplicity and speed at the cost of correctness.
It does not try to imitate proper ORMs like jOOQ or Jetbrains/Exposed,
which are providing more correctness but at the cost of simplicity.
Also the downside is that you will be bound to a specific database,
which is not such big of a problem since developers rarely
switch databases in the middle of development phase...
In addition, it also provides with premium enterprise features like,
query profiler and database export to visualization formats
all that with forever free Apache-2.0 license and
incredible speed, proven by extensive
profiling and benchmark tests.
/** DEPENDENCIES */
implementation("com.urosjarc:db-messiah:0.0.2") // Required
implementation("com.urosjarc:db-messiah-extra:0.0.2") // Optional
implementation("org.apache.logging.log4j:log4j-slf4j2-impl:2.20.0") //Optional
/** DRIVERS */
runtimeOnly("com.ibm.db2:jcc:11.5.9.0")
runtimeOnly("com.h2database:h2:2.2.224")
runtimeOnly("org.apache.derby:derby:10.17.1.0")
runtimeOnly("org.mariadb.jdbc:mariadb-java-client:3.3.2")
runtimeOnly("org.xerial:sqlite-jdbc:3.44.1.0")
runtimeOnly("com.mysql:mysql-connector-j:8.2.0")
runtimeOnly("com.microsoft.sqlserver:mssql-jdbc:12.4.2.jre11")
runtimeOnly("org.postgresql:postgresql:42.7.1")
runtimeOnly("com.oracle.database.jdbc:ojdbc11:23.3.0.23.09")/** TYPE SAFE ID */
@JvmInline
value class Id<T>(val value: Int) {
/** You must override toString! */
override fun toString(): String = this.value.toString()
}
/** TYPE SAFE UID */
@JvmInline
value class UId<T>(val value: UUID = UUID.randomUUID()) {
/** You must override toString! */
override fun toString(): String = this.value.toString()
}/** PARENT */
data class Parent(
var pk: Id<Parent>? = null, // INTEGER Auto-incremental primary key
var value: String // NOT NULL column
)
/** CHILD */
data class Child(
val pk: UId<Child> = UId(), // UUID Manual primary key
val parent_pk: Id<Parent>, // Foreign key, NOT NULL
val value: String? // NULL column
)
/** UNSAFE */
data class Unsafe(
// var pk: Int? = null, // Unsafe INTEGER auto-incremental primary key
val pk: UUID = UUID.randomUUID(), // Unsafe UUID manual primary key
val created: Instant = Instant.now() // Support for java.time.*
)
/** QUERY DTO */
data class Out(val child_value: String, val parent_value: String)/** SQLITE */
val sqliteSerializer = SqliteSerializer(
globalSerializers = BasicTS.sqlite + JavaTimeTS.sqlite + listOf(
IdTS.int(construct = { Id<Any>(it) }, deconstruct = { it.value }),
IdTS.uuid.sqlite(construct = { UId<Any>(it) })
),
tables = listOf(
Table(Unsafe::pk),
Table(Parent::pk),
Table(
primaryKey = Child::pk,
foreignKeys = listOf(Child::parent_pk to Parent::class),
constraints = listOf(
Child::parent_pk to listOf(C.CASCADE_DELETE, C.CASCADE_UPDATE),
Child::value to listOf(C.UNIQUE)
)
),
),
globalOutputs = listOf(Out::class),
)
/** POSTGRES */
val pgSerializer = PgSerializer(
globalSerializers = BasicTS.postgresql + JavaTimeTS.postgresql,
schemas = listOf(
PgSchema(name = "other", tables = listOf(Table(Unsafe::pk)))
),
)
/** CONFIG */
val config = Properties().apply {
this["jdbcUrl"] = "jdbc:sqlite::memory:"
this["username"] = "root"
this["password"] = "root"
}
/** SERVICE */
val sqlite = SqliteService(
config = config,
ser = sqliteSerializer
)| PlantUML | dbdiagram.io |
File("./build/db.pluml")
.writeText(
sqliteSerializer.plantUML(
withOtherColumns = false
)
) |
File("./build/db.txt")
.writeText(
sqliteSerializer.dbDiagramIO(
withOtherColumns = false
)
) |
 |
 |
sqlite.autocommit {
/** CREATE */
it.table.create<Parent>()
it.table.create<Child>()
it.table.create<Unsafe>()
/** INSERT */
val parent = Parent(value = "Hello World!")
it.row.insert(row = parent)
assert(parent.pk != null)
/** BATCH INSERT */
val children = arrayOfNulls<Child>(3000).mapIndexed { i, _ ->
Child(pk = UId(), parent_pk = parent.pk!!, value = "value_$i")
}
it.batch.insert(rows = children) // INSERT 1000 rows / batch.
/** SELECT */
val parents = it.table.select<Parent>()
assert(parents.contains(parent))
/** SELECT PAGE */
val page = it.table.select<Child>(
page = Page(
number = 2, limit = 5,
orderBy = Child::pk,
order = Order.DESC
)
)
assert(page.size == 5)
/** SELECT CURSOR */
val cursor = it.table.select<Child, UId<Child>>(
cursor = Cursor(
index = children[3].pk, limit = 5,
orderBy = Child::pk, order = Order.ASC
)
)
assert(cursor.size == 5)
/** UPDATE */
parent.value = "value_3"
it.row.update(parent)
/** WHERE */
val someChildren = it.query.get(output = Child::class, input = parent) {
""" ${it.SELECT<Child>()} WHERE ${it.column(Child::value)} = ${it.input(Parent::value)} """
}
assert(someChildren == listOf(children[3]))
/** JOIN */
val moreChildren = it.query.get(output = Child::class, input = parent) {
"""
${it.SELECT<Child>()}
INNER JOIN ${it.table<Parent>()} ON ${it.column(Parent::pk)} = ${it.column(Child::parent_pk)}
WHERE ${it.column(Child::value)} = ${it.input(Parent::value)}
"""
}
assert(moreChildren == listOf(children[3]))
sqlite.transaction { // Any exception inside will trigger rollback ALL!
//...
val savePoint = it.roolback.savePoint()
//...
it.roolback.to(point = savePoint)
//...
}
/** ISOLATION */
sqlite.transaction(isolation = Isolation.READ_UNCOMMITTED) {
//...
}val DURATION = TypeSerializer(
kclass = Duration::class,
dbType = "INTEGER",
jdbcType = JDBCType.INTEGER,
decoder = { rs, i, _ -> Duration.ofSeconds(rs.getLong(i)) },
encoder = { ps, i, x -> ps.setLong(i, x.toSeconds()) }
)
/** REGISTRATION */
SqliteSerializer(
globalSerializers = BasicTS.sqlite + JavaTimeTS.sqlite + listOf(DURATION),
tables = listOf(Table(Unsafe::pk))
)sqlite.autocommit {
Profiler.active = true
repeat(10) { _ -> it.table.select<Parent>() }
repeat(5) { _ -> it.table.select<Child>() }
repeat(2) { _ -> it.table.select<Unsafe>() }
Profiler.active = false
}
val profilerLogs = Profiler.logs.values
.filter { !it.sql.contains("DROP TABLE") }
.filter { !it.sql.contains("CREATE TABLE") }
.sortedByDescending { it.duration / it.repetitions }
profilerLogs.forEach {
println("\n* Query: ${it.sql}")
println("* type: ${it.type}")
println("* exec: ${it.repetitions}")
println("* time: ${it.duration / it.repetitions}")
}
/**
* Query: SELECT * FROM "main"."Parent"
* type: QUERY
* exec: 10
* time: 285.528us
* Query: SELECT * FROM "main"."Unsafe"
* type: QUERY
* exec: 2
* time: 65.601us
* Query: SELECT * FROM "main"."Child"
* type: QUERY
* exec: 5
* time: 40.525us
*/
All tutorials are tested and defined as kotlin files inside src/tutorials/kotlin directory.
Sample project is inside src/chinook/kotlin directory and it implements,
for all supported databases, famous Chinook sample database.
User provided config property object is on initialization, passed directly to the HikariCP library,
which handles everything around database connection pooling and configuration.
For customizing SQL generation, see the tutorial for
creating custom database serializers.
Library uses Kotlin reflection only at initialization, to introspect the user provided db structure.
System will create mapping rules on how kotlin classes will be mapped into db tables.
System will also test resulting rules, in order to find any user errors or inconsistency.
The list of all tests can be found inside SerializerTests.kt.
System will for E2E testing use db servers defined inside docker compose.yaml file.
You can start those servers with docker-compose up.
The recommend logging configuration is located here src/test/resources/log4j2.xml,
where you can find logger for async rolling log files (one log file per session)
and async console logger with pretty padding for maximum readability.
For detailed explanation read about Log4j Appenders.
| Schema | Serializer | Service | Basic types | java.time.* types | |
|---|---|---|---|---|---|
| DB2 | Db2Schema | Db2Serializer | Db2Service | BasicTS.db2 | JavaTimeTS.db2 |
| Derby | DerbySchema | DerbySerializer | DerbyService | BasicTS.derby | JavaTimeTS.derby |
| H2 | H2Schema | H2Serializer | H2Service | BasicTS.h2 | JavaTimeTS.h2 |
| Maria | MariaSchema | MariaSerializer | MariaService | BasicTS.maria | JavaTimeTS.maria |
| MS SQL | MssqlSchema | MssqlSerializer | MssqlService | BasicTS.mssql | JavaTimeTS.mssql |
| MySQL | MysqlSchema | MysqlSerializer | MysqlService | BasicTS.mysql | JavaTimeTS.mysql |
| Oracle | OracleSchema | OracleSerializer | OracleService | BasicTS.oracle | JavaTimeTS.oracle |
| Postgres | PgSchema | PgSerializer | PgService | BasicTS. postgresql | JavaTimeTS. postgresql |
| Sqlite | ❌ | SqliteSerializer | SqliteService | BasicTS.sqlite | JavaTimeTS.sqlite |
| Escape | Schema | Auto INT PK | Auto UUID PK | UUID column | Many queries | Cascade | Procedure | |
|---|---|---|---|---|---|---|---|---|
| DB2 | "%s" | 🔶 | ✅ | ❌ | CHAR(36) | ❌ | ❌ | 🔶 |
| Derby | "%s" | ✅ | ✅ | ❌ | CHAR(36) | ❌ | ❌ | ❌ |
| H2 | "%s" | ✅ | ✅ | ✅ | UUID | ❌ | ✅ | ❌ |
| Maria | `%s` | ✅ | ✅ | ❌ | UUID | ❌ | ❌ | ✅ |
| MS SQL | [%s] | ✅ | ✅ | ❌ | UNIQUEIDE... | ✅ | ❌ | ✅ |
| MySQL | `%s` | ✅ | ✅ | ❌ | CHAR(36) | ❌ | ❌ | ✅ |
| Oracle | "%s" | 🔶 | ✅ | ❌ | VARCHAR2(36) | ❌ | ✅ | ✅ |
| Postgres | "%s" | ✅ | ✅ | ✅ | UUID | ✅ | ✅ ✅ | ❌ |
| Sqlite | "%s" | ❌ | ✅ | ❌ | CHAR(36) | ❌ | ❌ | ❌ |
| KClass | COLUMN | Databases | db-messiah | db-messiah-extra |
|---|---|---|---|---|
| Boolean | BOOL | ✅ | ✅ | ❌ |
| Char | CHAR | ✅ | ✅ | ❌ |
| String | VARCHAR(100) | ✅ | ✅ | ❌ |
| Float | FLOAT | ✅ | ✅ | ❌ |
| Double | DOUBLE | ✅ | ✅ | ❌ |
| Byte / UByte | TINYINT | ✅ | ✅ | ❌ |
| Short / UShort | SMALLINT | ✅ | ✅ | ❌ |
| Int / Uint | INTEGER | ✅ | ✅ | ❌ |
| Long / ULong | BIGINT | ✅ | ✅ | ❌ |
| Instant | DATETIME | Sqlite, Mysql, MSSql, Maria, H2, DB2 | Java ✅ | kotlinx ✅ |
| Instant | TIMESTAMP | Derby, Postgres, Oracle | Java ✅ | kotlinx ✅ |
| LocalDateTime | ❌ | ❌ | Java ❌ | kotlinx ❌ |
| LocalDate | DATE | ✅ | Java ✅ | kotlinx ✅ |
| LocalTime | TIME | ✅ but Oracle | Java ✅ | kotlinx ✅ |
| LocalTime | NUMBER(5, 0) | Oracle | Java ✅ | kotlinx ✅ |
| ROW UPDATE | BATCH UPDATE | ROW DELETE | BATCH DELETE |
 |
 |
 |
 |
| CASCADE DELETE | CASCADE UPDATE | NOT NULL | UNIQUE |
 |
 |
 |
 |
src/main/kotlin/com/urosjarc/dbmessiah/
|-- builders...........................| Builders to help you build type safe queries.
| |-- ProcedureBuilder.kt............| Builder for procedures.
| |-- QueryBuilder.kt................| Builder for custom queries.
| |-- RowBuilder.kt..................| Builder for creating row queries.
| `-- SqlBuilder.kt..................| Builder for SQL templates.
it.query.get<Child> { sb: SqlBuilder ->
""" ${sb.SELECT<Child>()} """
}
|-- ConnectionPool.kt..................| For getting db autocommit or transactional connections.
|-- data...............................| Internal data representations of db elements.
| |-- BatchQuery.kt
| |-- Column.kt
| |-- DbValue.kt
| |-- DecodeInfo.kt..................| Additional info for Decoder about the decoding value.
| |-- Decoder.kt.....................| TypeSerializer callback for decoding value.
| |-- Encoder.kt.....................| TypeSerializer callback for encoding value.
| |-- ForeignColumn.kt
| |-- OtherColumn.kt
| |-- PrimaryColumn.kt
| |-- ProcedureArg.kt
| |-- Procedure.kt
| |-- Query.kt
| |-- QueryValue.kt
| |-- TableInfo.kt
| `-- TypeSerializer.kt..............| Structure that holds information on how some KClass will be mapped to db value.
|-- domain
| |-- C.kt...........................| All user available constraints, other are inferred with the reflection.
| |-- Cursor.kt
| |-- Isolation.kt
| |-- Order.kt
| |-- Page.kt
| |-- QueryLog.kt....................| Log structure used for profiling.
| |-- Rollback.kt
| `-- Table.kt
|-- Driver.kt..........................| Driver for preparing and executing Query on JDBC.
|-- exceptions
| |-- base
| | |-- IssueException.kt..........| Exception that needs to be reported on issue tracker.
| | |-- UnknownException.kt........| Exception when system does not know if this is an issue or a warning.
| | `-- WarningException.kt........| Warning to the user that he did something wrong.
| |-- ConnectionException.kt
| |-- DbValueException.kt
| |-- DriverException.kt
| |-- MapperException.kt
| |-- QueryException.kt
| `-- SerializerTestsException.kt
|-- Exporter.kt........................| Logic for exporting db structure to PlantUML, dbdiagram.io, ...
|-- extend
| |-- Iterable.kt....................| All extends functions attached to Iterable.
| |-- KClass.kt......................| All extends functions attached to KClass.
| `-- KProperty1.kt..................| All extends functions attached to KProperty1.
|-- impl
| |-- db2
| | |-- Db2Schema.kt...............| Structure that will user use to create schema.
| | |-- Db2Serializer.kt...........| Serializer for mapping user command to appropriate SQL Query string.
| | `-- Db2Service.kt..............| Service for executing user commands on database connection.
| |-- derby
| | |-- DerbySchema.kt
| | |-- DerbySerializer.kt
| | `-- DerbyService.kt
| |-- h2
| | |-- H2Schema.kt
| | |-- H2Serializer.kt
| | `-- H2Service.kt
| |-- maria
| | |-- MariaSchema.kt
| | |-- MariaSerializer.kt
| | `-- MariaService.kt
| |-- mssql
| | |-- MssqlSchema.kt
| | |-- MssqlSerializer.kt
| | `-- MssqlService.kt
| |-- mysql
| | |-- MysqlSchema.kt
| | |-- MysqlSerializer.kt
| | `-- MysqlService.kt
| |-- oracle
| | |-- OracleSchema.kt
| | |-- OracleSerializer.kt
| | `-- OracleService.kt
| |-- postgresql
| | |-- PgSchema.kt
| | |-- PgSerializer.kt
| | `-- PgService.kt
| `-- sqlite
| |-- SqliteSerializer.kt
| `-- SqliteService.kt
|-- MapperCache.kt.....................| Internal mapping cache containing hashmaps like KClass to TableInfo, KProperty1 to TypeSerializer, etc...
|-- Mapper.kt..........................| User friendly API for MapperCache.
|-- Profiler.kt........................| Simple static class that logs every query executed on Driver.
|-- queries............................| Execution logic for db queries.
| |-- BatchQueries.kt
| |-- GetManyQueries.kt..............| Some databases support multiple db calls per query.
| |-- GetOneQueries.kt...............| For databases that only support one db call per query.
| |-- NoReturnProcedureQueries.kt
| |-- ProcedureQueries.kt
| |-- RowQueries.kt
| |-- SchemaCascadeQueries.kt........| Some databases support cascading queries on schema.
| |-- SchemaQueries.kt...............| For databases that does not support cascading queries.
| |-- TableCascadeQueries.kt
| `-- TableQueries.kt
|-- Schema.kt..........................| Interface defining common schema logic.
|-- Serializer.kt......................| Interface defining common serializer logic.
|-- serializers........................| Here are all type serializers (TS) that are supported by the system.
| |-- BasicTS.kt.....................| User friendly API for accessing all TS for specific database.
| |-- BooleanTS.kt
| |-- CharTS.kt
| |-- DecimalTS.kt
| |-- IdTS.kt........................| User friendly API for creating custom inline primary key type serializer.
IdTS.int(construct = { Id<Any>(it) }, deconstruct = { it.value })
IdTS.uuid.sqlite(construct = { UId<Any>(it) })
| |-- InstantTS.kt
| |-- JavaTimeTS.kt..................| User friendly API for accessing all java.time.* supported TS for specific database.
| |-- LocalDateTS.kt
| |-- LocalTimeTS.kt
| |-- NumberTS.kt
| |-- StringTS.kt
| |-- UNumber.kt
| `-- UUIDTS.kt
|-- SerializerTests.kt.................| Initialization tests for serializer to find any error and inconsistency in user defined db schema.
`-- Service.kt.........................| Interface defining common service logic.
src/
|-- main...............................| Already described in architecture.
|-- chinook............................| Implementation of chinook sample database for all supported databases.
| |-- kotlin
| | |-- domain
| | | |-- Album.kt
| | | |-- Artist.kt
| | | |-- Customer.kt
| | | |-- Employee.kt
| | | |-- Genre.kt
| | | |-- Invoice.kt
| | | |-- InvoiceLine.kt
| | | |-- MediaType.kt
| | | |-- Playlist.kt
| | | |-- PlaylistTrack.kt
| | | `-- Track.kt
| | |-- Id.kt
| | |-- Schemas.kt
| | |-- Seed.kt....................| INSERT elements to tables.
| | |-- Serializers.kt
| | |-- Services.kt
| | `-- Test_Chinook.kt............| Main testing entry.
| `-- resources
| `-- log4j2.xml
|-- e2e
| |-- kotlin
| | |-- domain.kt
| | |-- Test_Benchmarks.kt.........| Testing speed of the system.
| | |-- Test_Contract.kt...........| Testing interface for all db tests.
| | |-- Test_Db2.kt
| | |-- Test_Derby.kt
| | |-- Test_H2.kt
| | |-- Test_Maria.kt
| | |-- Test_Mssql.kt
| | |-- Test_Mysql.kt
| | |-- Test_Oracle.kt
| | |-- Test_Postgresql.kt
| | `-- Test_Sqlite.kt
| `-- resources
| `-- log4j2.xml
|-- test
| |-- kotlin
| | `-- com
| | `-- urosjarc
| | `-- dbmessiah
| | |-- builders
| | | |-- Test_ProcedureBuilder.kt
| | | |-- Test_QueryBuilder.kt
| | | |-- Test_RowBuilder.kt
| | | `-- Test_SqlBuilder.kt
| | |-- data
| | | |-- Test_Column.kt
| | | |-- Test_OtherColumn.kt
| | | |-- Test_PrimaryColumn.kt
| | | |-- Test_ProcedureArg.kt
| | | |-- Test_Procedure.kt
| | | `-- Test_Query.kt
| | |-- domain
| | | `-- Test_Table.kt
| | |-- extend
| | | |-- Test_Iterable.kt
| | | |-- Test_KClass.kt
| | | `-- Test_KProperty.kt
| | |-- impl
| | | |-- Test_Db2Serializer.kt
| | | |-- Test_DerbySerializer.kt
| | | |-- Test_H2Serializer.kt
| | | |-- Test_MariaSerializer.kt
| | | |-- Test_MssqlSerializer.kt
| | | |-- Test_MysqlSerializer.kt
| | | |-- Test_OracleSerializer.kt
| | | |-- Test_PgSerializer.kt
| | | `-- Test_SqliteSerializer.kt
| | `-- Test_Serializer.kt
| `-- resources
| `-- log4j2.xml
`-- tutorials
|-- kotlin
| |-- 000_basic_sqlite.kt
| |-- 001_basic_postgresql.kt
| |-- 002_query_sqlite.kt
| |-- 003_query_postgresql.kt
| |-- 004_primary_keys.kt
| |-- 005_constraints.kt
| |-- 006_transactions.kt
| |-- 007_procedures.kt
| |-- 008_indexing_and_profiling.kt
| |-- 009_exceptions.kt
| |-- 010_custom_type_serializers.kt
| |-- 011_custom_database_serializers.kt
| |-- Test_README.kt...............................| Code from where README.md is generated.
| |-- Test_README.md...............................| Template from where README.md is generated.
| `-- Test_Tutorials.kt............................| Main testing entry.
`-- resources
`-- log4j2.xml