Ozone

• Ozone Domain Specific Language (DSL)
  • Ozone Table
• Ozone Data Access Object (DAO)
  • Ozone Entity
• Creating Tables and Entities
• Inserting New Entity
  • Batch Inserting Entities
• Finding Entity
  • Finding Or Creating
• Updating Entity
• Deleting Entity
• Default Column Types
• Column Binding Conventions
• Query Logging

Ozone is a thin layer on top of the excellent Ktorm library. Ozone comes bundled with lots of features needed to write a modern web app such as entity commands, migrations, database pool configuration, entity factories, seeding etc.

Ozone Domain Specific Language (DSL)

DSL allows you to interact with a database in the similar fashion of that of the actual SQL statements. Instead of working with strings with raw SQL statements, however, DSL allows you to make and run SQL statements in a very type safe way, as it should!

Ozone Table

To start using DSL for your CRUD SQL statements, you need a table that is mapped to an actual table in the database. You create this "map" table—basically, a schema—by extending the OzoneTable class and defining all your columns.

object Users : OzoneTable<Nothing>("users") {
   val id by long("id").primaryKey()
   val email by varchar("email")
   val name by varchar("name")
   val createdAt by timestamp("created_at")
}

Once the table object is defined, you can start running your CRUD operations on it.

// Create a new user
val id = Users.insertAndGenerateKey {
    it.name to "Jane Doe"
    it.email to "jane@example.com"
    it.createdAt to ZonedDateTime.now().toInstant()
}.toString().toLong()

// Retrieve users and "extract" their email addresses
val emails = Users.select()
    .where { Users.name eq "Jane Doe" }.map { row -> row[Users.email] }

// Update users
Users.update {
    it.name to "Jane M. Doe"
    where {
        Users.id eq id
    }
}

// Delete users
Users.delete { it.id eq id }

/info/Since there is only one table of a given name in the database, it makes sense to only have one such instance of table in your code as well. You do so by representing the table as an object instead of a class. However, this is not a strict requirement. There are certainly a few cases where having multiple instances makes sense — for an example, if there are two tables with an identical schema.

Ozone Data Access Object (DAO)

DSL is very powerful for running queries and doing advanced queries, but it is very convenient to map a result to an actual object and interact with it directly. Ozone DAO APIs allows you to do exactly that.

Ozone Entity

If you want to interact with your database in more of an ORM way than using SQL statements then you need to define an interface that extends the OzoneEntity class, "connect" a table object with this entity, and bind the table's properties with that of the entity's columns.

Once you have set up an entity, every row in the table is mapped to an instance of this entity.

interface User : OzoneEntity<User> {
    val id: Long
    val email: String
    val name: String
    val createdAt: Instant?
    
    // this allows us to create an instance of User interface
    companion object : OzoneEntity.Of<User>()
}

// Don't forget to bind the corresponding table to the entity
object Users : OzoneTable<User>("users") {
    val id by long("id").primaryKey().bindTo { it.id }
    val email by varchar("email").bindTo { it.email }
    val name by varchar("name").bindTo { it.name }
    val createdAt by timestamp("created_at").bindTo { it.createdAt }
}

Let's see how we can interact with the entity and its table.

// Create a new user
val user = User {
    name = "Jane Doe"
    email = "jane@example.com"
    createdAt = ZonedDateTime.now().toInstant()
}

// insert it
Users.add(user)

// Retrieve users
val users = User.find { Users.name eq "Jane Doe" }
// or find a user
val jane = User.findById(5)

// Update users and flush the changes
user.name = "Jane M. Doe"
user.email = "janemdoe@example.com"
user.flushChanges()

// Delete a user
user.delete()

Creating Tables and Entities

As you may have noticed, creating an entity and table with Ozone requires a little boilerplate code. Rather than creating an entity and the corresponding table by hand, you can use make:entity Alpas command. The entity and its table will be put in one file under the entities folder.

$ alpas make:entity Activity

   
interface Activity : OzoneEntity<Activity> {
    val id: Long
    val createdAt: Instant?
    val updatedAt: Instant?

    companion object : OzoneEntity.Of<Activity>()
}

object Activities : OzoneTable<Activity>("activities") {
    val id by bigIncrements()
    val createdAt by createdAt()
    val updatedAt by updatedAt()
}

The name of the table is automatically derived based on the entity's name.

make:entity also takes a -m flag that creates a migration file for the entity.

Inserting New Entity

You can insert a new entity into the database in a number of ways. Let's go through them one-by-one:

Strongly typed create() method

To crate an entity instance simply call create() method, which takes a closure as its only parameter where you need to assign values to the columns. This method returns the new entity that was created.

val user = Users.create {
    it.fullName to "Jane Doe"
    it.email to "jane@example.com"
}

// If you don't need the new entity back, you can use `insertAndGenerateKey()` instead.
// It returns the id of the new entity. This method is more performant than the
// counterpart `create()` method as it only makes one trip to the database.

val userId = Users.insertAndGenerateKey {
    it.fullName to "Jane Doe"
    it.email to "jane@example.com"
}

Creating an entity using an attribute map

If you'd rather create an entity with just a map of attributes, you can use create() method that takes a map instead of a closure. The keys of this attribute map corresponds to the column names in the table. If a corresponding column name doesn't exist for a key, it will simply be ignored.

val attributes = mapOf("full_name" to "Jane Doe", "email" to "jane@example.com")
val user = Users.create(attributes)

Creating an entity using an attribute map + an assignment builder

When you are creating an entity by using an attribute map, you then have to set some additional attributes and then you can use the overloaded create() method that takes an attribute map as the first parameter and an assignment builder closure as the second parameter.

This is very helpful when you are creating a new entity with some user inputs but you also have to, for an example, set some foreign key values.

fun savePost(call: HttpCall) {
    val attributes = call.params("title", "body", "slug")
    val authorId = call.user.id
    val post = Posts.create(attributes) {
        it.author_id to authorId
    }

    call.reply("success")
}

/alert/Be careful creating an entity using attribute map when the values come from an external source. You maybe exposed to mass assignment attacks.

Adding an entity to a table

Even though OzoneEntity is an interface, thanks to the companion OzoneEntity.Of<>() object, it is possible to create an instance of it as if it was a class. Once created, you can call add() method on its table to insert this entity into the database.

val user = User {
    fullName = "Jane Doe"
    email = "jane@example.com"
}

Users.add(user)

Batch Inserting Entities

If you have multiple instances to save in the database, you might want to consider using batchInsert rather than inserting one entity at a time.

Users.batchInsert {
    item {
        it.name to "Jane"
        it.email to "jane@example.com"
    }

    item {
        it.name to "John"
        it.email to "john@example.com"
    }
}

Finding Entity

To find multiple entities that meet a condition, use the findList() method and pass a predicate closure.

val users = Users.findList {
    it.email like "%example.com%"
}

Use findAll() method to fetch all the records from a table. This auto left joins all the referencing tables as well.

Use findOne() method to find only one record if it exists or return null. If more than one record exists, then this method will throw an exception.

If you already have a primary ID of an entity, you can use findById() method to fetch an entity. If you have a list of IDs and you want to fetch the corresponding entities, you can use findListByIds() method instead.

Finding or creating an entity

Sometimes you might be trying to find an entity with some given constraints, and if it doesn't exist, you might want to insert a new record instead. While you can do this by manually using one of the find() methods and then use one of the insert() methods to create a new record. Or you can use one of the Ozone's findOrCreate() methods.

val slug = param("slug")
val whereAttributes = mapOf("slug" to slug)
val extraAttributes = params("title", "body")

// Try to find a post by given. If it doesn't exist, create a new post by combining
// both 'whereAttributes' and 'extraAttributes' maps.
val post = Posts.findOrCreate(whereAttributes, extraAttributes)

// You can also pass the 'extraAttributes' using an assignment builder
// by passing a closure to take advantage of strong typing
Posts.findOrCreate(whereAttributes) {
    it.title to "this is a new title"
    it.body to "this is new body"
}

Updating Entity

You can update an entity or multiple entities using a table, or you can update an entity directly and flush the changes, or you can update an entity using an attribute map.

Updating using a table

Update an entity or entities by calling update() on the table and passing a closure.

Users.update {
    it.name to "Jane M. Doe"
    it.email to "newjane@example.com"
    where {
        it.id eq 1
    }
}

Updating an entity directly

If you already have an instance an entity, you can set its new values and then call flushchanges() to persist the changes to the database.

Keep in mind that this entity must be associated with a table first and it also should have a primary key defined.

val user = Users.findById(1)

user.name = "Jane M. Doe"
user.email = "janemdoe@example.com"
user.flushChanges()

Updating an entity using an entity map

If you have an attribute map of new values, you can update an entity or entities using this map and passing a closure that conditionally selects the entities to be updated.

fun updatePost(call: HttpCall) {
    val attributes = call.params("title", "body")
    val postId = call.longParam("id").orAbort()

    Posts.update(attributes) {
        it.id eq postId
    }

    call.reply("success")
}

Deleting Entity

To delete multiple entities you can use delete() method on the table and pass a closure.

Users.delete {
    it.email like "%example.com%"
}

To delete an entity that is already associated with a table and also has a primary key defined, you can call delete() method on it directly.

val user = Users.findById(1)
user?.delete()

Default Column Types

Your table can define different types of out-of-the-box supported column types.

Column Binding Conventions

There are some columns that show up regularly in a database, especially with a web application — auto incrementing primary key, a created_at timestamp field, and an updated_at timestamp field. Ozone has a shortcut for declaring all of these fields within a table.

object Users: OzoneTable<User>() {

// Instead of declaring a primary this way...
val id by bigInt("id").autoIncrement().unsigned().primaryKey().bindTo{ it.id }
val id by int("id").autoIncrement().unsigned().primaryKey().bindTo{ it.id }

// ...you can declare it like this
val id by bigIncrements()
val id by increments()

// You can override the name of the column and the 
// consequently the name of the field it binds to
val id by bigIncrements("uid")

// Instead of declaring created_at and updated_at fields this way...
val createdAt by timestamp("created_at").nullable().useCurrent().bindTo { it.createdAt }
val updatedAt by timestamp("updated_at").nullable().useCurrent().bindTo { it.updatedAt }

// ...you can declare them like this:
val createdAt by createdAt()
val updatedAt by updatedAt()

// You can customize some of its properties as well
val createdAt by createdAt(name="created_date", nullable=false, useCurrent=false)

// ...

}

Query Logging

One major downside of using a database framework like Ozone/Ktorm is that the SQL queries that actually get run on your database are opaque to you. You may be making a small innocent mistake like lazy loading a relation instead of eagerly loading and running into the trap of N+1 query problem.

One quick way to see behind-the-scenes database queries is by logging them, which, fortunately, is already done for you. All you need to do is print out the actual queries that were ran by adding the following 1 line to your logging configuration files.

<!-- ... -->

<logger name="me.liuwj.ktorm.database" level="debug"></logger>

<!-- ... -->

For advanced DAO usage, please checkout the DAO wiki. For advanced DSL usage, please checkout the DSL wiki.

/power/Ozone is proudly supercharged by Ktorm*.