kwery

Kwery is an SQL library for Kotlin

Programming language: Kotlin

License: MIT License

Tags: Database    

README

Kwery Overview

Kwery is an SQL library for Kotlin.

Kwery consists of three major modules (core, mapper and fetcher) that when combined provide similar functionality to a traditional ORM.

Kwery's manifesto:

• Your domain model is sacred. No annotations or modifications to your model are required. Immutable models are fully supported.
• No implicit fetching. Joins and graph fetches are explicit for predictable performance.
• No magic. No proxies, interceptors, reflection or implicit saves. Explicit functions with sensible defaults control everything.
• Useful logging. Logged statements are valid SQL with inline parameters for your dialect.

Core

The [core module](core) is a fairly thin wrapper over JDBC, providing support for named parameters, logging and transactions.

class Actor(val firstName: String, val lastName: String, val lastUpdate: Timestamp)

val session = DefaultSession(connection, HsqlDialect()) // Standard JDBC connection

val sql = "select * from actor where first_name = :first_name"

val actors = session.select(sql, mapOf("first_name" to "Brad")) { row ->
    Actor(row.string("first_name"), row.string("last_name"), row.timestamp("last_update"))
}

Mapper

The [mapper module](mapper) module builds on core to provide typical DAO (Data Access Object) functionality.

As Kwery believes your domain model shouldn't be tainted by mapping annotations, it uses a Table object to define the mapping between rows and objects.

// We'll map to standard immutable classes, grouping name fields into a class
class Name(val firstName: String, val lastName: String)
class Actor(val id: Int, val name: Name, val lastUpdate: LocalDateTime)

// A table object defines the mapping between columns and models
// Conversions default to those defined in the configuration but may be overridden
object actorTable : Table<Actor, Int>("actor"), VersionedWithTimestamp {
    val ActorId    by col(Actor::id, id = true)
    val FirstName  by col(Name::firstName, Actor::name)
    val LastName   by col(Name::lastName, Actor::name)
    val LastUpdate by col(Actor::lastUpdate, version = true)

    override fun idColumns(id: Int) = setOf(ActorId of id)

    override fun create(value: Value<Actor>) = Actor(value of ActorId,
            Name(value of FirstName, value of LastName), value of LastUpdate)
}

// Given a table object, a generic dao is a one-liner, including standard CRUD operations
class ActorDao(session: Session) : AbstractDao<Actor, Int>(session, actorTable, Actor::id)

// Now we can use the DAO
val dao = ActorDao(session)
val inserted = dao.insert(Actor(1, Name("Kate", "Beckinsale"), LocalDateTime.now()))
val actors = dao.findAll()

See FilmDao.kt for a more comprehensive example.

Graph Fetcher

DAOs only fetch data from their linked table by default. To fetch an object graph, using a [graph fetcher](fetcher) is the recommended method.

Given a graph specification, the fetcher attempts to fetch the graph in the minimum number of queries possible. It does this by batching together requests for the same type into a single query. As it fetches by ids, it also provides an ideal mechanism to insert a cache layer.

// Given the following domain model
data class Actor(val id: Int, val firstName: String, val lastName: String)

data class Language(val id: Int, val name: String)

data class Film(val id: Int, val language: Language, val actors: Set<Actor>,
                val title: String, val releaseYear: Int)

// Define types with functions describing how to fetch a batch by ids
val language = Type(Language::id, { languageDao.findByIds(it) })
val actor = Type(Actor::id, { actorDao.findByIds(it) })

// For types that reference other types describe how to apply fetched values
val film = Type(Film::id, { filmDao.findByIds(it) }, listOf(
        // 1 to 1
        Property(Film::language, language, { it.language.id }, { f, l -> f.copy(language = l) }),

        // 1 to many requires a function to describe how to fetch the related objects
        CollectionProperty(Film::actors, actor, Film::id,
                { f, a -> f.copy(actors = a.toSet()) },
                { actorDao.findByFilmIds(it) })
))

val fetcher = GraphFetcher(setOf(language, actor, film))

// Extension function to fetch the graph for any List using fetcher defined above
fun <T> Collection<T>.fetch(node: Node) = fetcher.fetch(this, Node(node))

// We can now efficiently fetch various graphs for any list of films
// The following fetches the films with actors and languages in 3 queries
val filmsWithAll = filmDao.findFilmsReleasedAfter(2010).fetch(Node.all)

// The graph specification can also be built using properties
val filmsWithActors = filmDao.findFilmsReleasedAfter(2010).fetch(Film::actors.node())

DAOs and graph fetching aim to cover 95% of a typical application data retrievals. For the remaining performance critical sections, use specialised methods on the DAOs using partial selects and joins as required.

Example

The [example module](example) demonstrates using Kwery to expose a simple model via RESTful web services via Dropwizard.

Transactional

The [transactional module](transactional) adds general purpose transaction interceptors. e.g.

@Transactional open class MyService(val session: Session) {
    open fun foo() {}
}

val session = ManagedThreadLocalSession(dataSource, HsqlDialect())
val service = transactionalFactory.fromClass(MyService(session), MyService::session)
service.foo() // Now calls to service automatically occur within a transaction

See the [readme](transactional) for more information.

Transactional for Jersey

The [transactional-jersey module](transactional-jersey) adds transaction annotations for Jersey.

Registering [TransactionListener](transactional-jersey/src/main/kotlin/com/github/andrewoma/kwery/transactional/jersey/transactional.kt) as a Jersey provider allows the transactional attribute to declare resource classes or methods as transactional.

Path("/films")
@Transactional class FilmResource : Resource {
    GET fun find(): List<Film> {
        ...
    }
}

See the [readme](transactional-jersey) for more information.

Status

Kwery is unstable. It's currently being developed for a side project, so features are added as required.

Kwery is available in Maven Central

0.17 Compatible with Kotlin 1.1.3-2.

• Fix #14 - Incorrect parameter positions for collections
• Lazily set Statement.poolable

0.16 Compatible with Kotlin 1.1.0.

0.15 Compatible with Kotlin 1.0.4.

• Mapper: Support ThreadLocalSessions in Dao by creating implicit transactions (thanks @brianmadden)

0.14 Compatible with Kotlin 1.0.4.

0.13 Compatible with Kotlin 1.0.3.

0.12 Compatible with Kotlin 1.0.2.

• Core: QueryBuilder
• Core: Fix collection binding when not first parameter
• Mapper: Add Dao.findByIdForUpdate

0.11 Compatible with Kotlin 1.0.2.

• Core: Fix logging of statements with bound values containing $
• Core: Add experimental sqlite support
• Mapper: Support generated keys for MySQL in DAOs

0.10 Compatible with Kotlin 1.0.2.

0.9 Compatible with Kotlin 1.0.0.

• Mapper: add Table.optionalCol to construct optional types via paths

0.8 Compatible with Kotlin 1.0.0-rc-1036.

• Mapper: support PreUpdate and PreInsert events (thanks @davemaple)
• Remove tomcat pool module as Postgres drivers now support prepared statement caching

0.7 Compatible with Kotlin 1.0.0-beta-3595.

• Add MySQL dialect

0.6 Compatible with Kotlin 1.0.0-beta-1038.

0.5 Compatible with Kotlin M14.

0.4 Compatible with Kotlin M13:

• Provide a consistent set of defaults and converters for mapping standard types
• Add defaults and converters for OffsetDateTime and ZonedDateTime

0.3 Compatible with Kotlin M13:

• Improved docs
• Simplified transaction listeners
• Made transactions re-entrant
• Renamed ThreadLocalSession to ManagedThreadLocalSession and introduced a new ThreadLocalSession for use without interceptors and annotations.

0.2 Compatible with Kotlin M12, adding transactional interceptors.

0.1 Compatible with Kotlin M11.

Building

git clone https://github.com/andrewoma/kwery.git
cd kwery
./gradlew check install

Note: The tests require a local postgres and mysql database named kwery. e.g. On OS X

brew install postgres
launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
createdb kwery

brew install mysql
ln -sfv /usr/local/opt/mysql/*.plist ~/Library/LaunchAgents
launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
mysql -uroot -e 'create database kwery'
mysql -uroot -e "create user 'kwery'@'localhost' identified by 'kwery'"
mysql -uroot -e "grant all privileges on *.* to 'kwery'@'localhost'"

To open in IntelliJ, just open the build.gradle file and IntelliJ will generate the project automatically.

Roadmap

Core:

• Support direct execution (currently everything is via a PreparedStatement)
• Add more robust named parameter replacement (ignore patterns inside comments, strings, etc)

DAO:

• Documentation

Fetcher:

• Documentation
• General review - code seems overly complicated for what it does

Modules:

• Dropwizard metrics integration
• Generator - Generate initial Table and domain objects from reading JDBC metadata

Robustness/Performance:

• Soak test - check for leaking connections/resources over extended usage
• Profile array based in clauses on large tables

Misc:

• Better IDE support for highlighting inline SQL. Vote for KT-6610

License

This project is licensed under a MIT license.