Welcome to Exposed, an ORM framework for Kotlin. Exposed offers two levels of database access: typesafe SQL wrapping DSL and lightweight data access objects. Our official mascot is Cuttlefish, which is best known for its outstanding mimicry abilities letting it blend seamlessly in any environment. Just like our mascot, Exposed can mimic a variety of database engines and help you build database applications without hard dependencies on any specific database engine, and switch between them with very little or no changes in your code.

Supported Databases

• H2
• MySQL
• MariaDB
• Oracle
• PostgreSQL
• PostgreSQL using the pgjdbc-ng JDBC driver
• SQL Server
• SQLite

Links

Exposed is currently available for maven/gradle builds at Maven Central (read Getting started).

• Wiki with examples and docs.
• Roadmap to see what's coming next.
• Change log of improvements and bug fixes.

If you have any questions feel free to ask at our #exposed channel on kotlinlang.slack.com.

Examples

SQL DSL

importorg.jetbrains.exposed.sql.*importorg.jetbrains.exposed.sql.transactions.transactionobject Users : Table() {
val id = varchar("id", 10) // Column<String>val name = varchar("name", length =50) // Column<String>val cityId = (integer("city_id") references Cities.id).nullable() // Column<Int?>overrideval primaryKey =PrimaryKey(id, name ="PK_User_ID") // name is optional here
}
object Cities : Table() {
val id = integer("id").autoIncrement() // Column<Int>val name = varchar("name", 50) // Column<String>overrideval primaryKey =PrimaryKey(id, name ="PK_Cities_ID")
}
funmain() {
Database.connect("jdbc:h2:mem:test", driver ="org.h2.Driver", user ="root", password ="")
transaction {
addLogger(StdOutSqlLogger)
SchemaUtils.create (Cities, Users)
val saintPetersburgId =Cities.insert {
it[name] ="St. Petersburg"
} get Cities.id
val munichId =Cities.insert {
it[name] ="Munich"
} get Cities.id
val pragueId =Cities.insert {
it.update(name, stringLiteral(" Prague ").trim().substring(1, 2))
}[Cities.id]
val pragueName =Cities.select { Cities.id eq pragueId }.single()[Cities.name]
assertEquals(pragueName, "Pr")
Users.insert {
it[id] ="andrey"
it[name] ="Andrey"
it[Users.cityId] = saintPetersburgId
}
Users.insert {
it[id] ="sergey"
it[name] ="Sergey"
it[Users.cityId] = munichId
}
Users.insert {
it[id] ="eugene"
it[name] ="Eugene"
it[Users.cityId] = munichId
}
Users.insert {
it[id] ="alex"
it[name] ="Alex"
it[Users.cityId] =null
}
Users.insert {
it[id] ="smth"
it[name] ="Something"
it[Users.cityId] =null
}
Users.update({ Users.id eq "alex"}) {
it[name] ="Alexey"
}
Users.deleteWhere{ Users.name like "%thing"}
println("All cities:")
for (city inCities.selectAll()) {
println("${city[Cities.id]}: ${city[Cities.name]}")
}
println("Manual join:")
(Users innerJoin Cities).slice(Users.name, Cities.name).
select {(Users.id.eq("andrey") orUsers.name.eq("Sergey")) andUsers.id.eq("sergey") andUsers.cityId.eq(Cities.id)}.forEach {
println("${it[Users.name]} lives in ${it[Cities.name]}")
}
println("Join with foreign key:")
(Users innerJoin Cities).slice(Users.name, Users.cityId, Cities.name).
select { Cities.name.eq("St. Petersburg") orUsers.cityId.isNull()}.forEach {
if (it[Users.cityId] !=null) {
println("${it[Users.name]} lives in ${it[Cities.name]}")
}
else {
println("${it[Users.name]} lives nowhere")
}
}
println("Functions and group by:")
((Cities innerJoin Users).slice(Cities.name, Users.id.count()).selectAll().groupBy(Cities.name)).forEach {
val cityName = it[Cities.name]
val userCount = it[Users.id.count()]
if (userCount >0) {
println("$userCount user(s) live(s) in $cityName")
} else {
println("Nobody lives in $cityName")
}
}
SchemaUtils.drop (Users, Cities)
}
}

Generated SQL:

 SQL: CREATE TABLE IF NOT EXISTS Cities (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(50) NOT NULL, CONSTRAINT PK_Cities_ID PRIMARY KEY (id))
SQL: CREATE TABLE IF NOT EXISTS Users (id VARCHAR(10) NOT NULL, name VARCHAR(50) NOT NULL, city_id INTNULL, CONSTRAINT PK_User_ID PRIMARY KEY (id))
SQL: ALTER TABLE Users ADD FOREIGN KEY (city_id) REFERENCES Cities(id)
SQL: INSERT INTO Cities (name) VALUES ('St. Petersburg')
SQL: INSERT INTO Cities (name) VALUES ('Munich')
SQL: INSERT INTO Cities (name) VALUES ('Prague')
SQL: INSERT INTO Users (id, name, city_id) VALUES ('andrey', 'Andrey', 1)
SQL: INSERT INTO Users (id, name, city_id) VALUES ('sergey', 'Sergey', 2)
SQL: INSERT INTO Users (id, name, city_id) VALUES ('eugene', 'Eugene', 2)
SQL: INSERT INTO Users (id, name, city_id) VALUES ('alex', 'Alex', NULL)
SQL: INSERT INTO Users (id, name, city_id) VALUES ('smth', 'Something', NULL)
SQL: UPDATE Users SET name='Alexey'WHEREUsers.id='alex'
SQL: DELETEFROM Users WHEREUsers.nameLIKE'%thing'
All cities:
SQL: SELECTCities.id, Cities.nameFROM Cities
1: St. Petersburg
2: Munich
3: Prague
Manual join:
SQL: SELECTUsers.name, Cities.nameFROM Users INNER JOIN Cities ONCities.id=Users.city_idWHERE ((Users.id='andrey') or (Users.name='Sergey')) andUsers.id='sergey'andUsers.city_id=Cities.id
Sergey lives in Munich
Join with foreign key:
SQL: SELECTUsers.name, Users.city_id, Cities.nameFROM Users INNER JOIN Cities ONCities.id=Users.city_idWHERE (Cities.name='St. Petersburg') or (Users.city_id IS NULL)
Andrey lives in St. Petersburg
Functions andgroup by:
SQL: SELECTCities.name, COUNT(Users.id) FROM Cities INNER JOIN Users ONCities.id=Users.city_idGROUP BYCities.name1 user(s) live(s) in St. Petersburg
2 user(s) live(s) in Munich
SQL: DROPTABLEUsers
SQL: DROPTABLECities

DAO

importorg.jetbrains.exposed.dao.*importorg.jetbrains.exposed.dao.id.EntityIDimportorg.jetbrains.exposed.dao.id.IntIdTableimportorg.jetbrains.exposed.sql.*importorg.jetbrains.exposed.sql.transactions.transactionobject Users : IntIdTable() {
val name = varchar("name", 50).index()
val city = reference("city", Cities)
val age = integer("age")
}
object Cities: IntIdTable() {
val name = varchar("name", 50)
}
classUser(id:EntityID<Int>) : IntEntity(id) {
companionobject:IntEntityClass<User>(Users)
var name by Users.name
var city by City referencedOn Users.city
var age by Users.age
}
classCity(id:EntityID<Int>) : IntEntity(id) {
companionobject:IntEntityClass<City>(Cities)
var name by Cities.name
val users by User referrersOn Users.city
}
funmain() {
Database.connect("jdbc:h2:mem:test", driver ="org.h2.Driver", user ="root", password ="")
transaction {
addLogger(StdOutSqlLogger)
SchemaUtils.create (Cities, Users)
val stPete =City.new {
name ="St. Petersburg"
}
val munich =City.new {
name ="Munich"
}
User.new {
name ="a"
city = stPete
age =5
}
User.new {
name ="b"
city = stPete
age =27
}
User.new {
name ="c"
city = munich
age =42
}
println("Cities: ${City.all().joinToString {it.name}}")
println("Users in ${stPete.name}: ${stPete.users.joinToString {it.name}}")
println("Adults: ${User.find { Users.age greaterEq 18 }.joinToString {it.name}}")
}
}

Generated SQL:

 SQL: CREATE TABLE IF NOT EXISTS Cities (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(50) NOT NULL, CONSTRAINT pk_Cities PRIMARY KEY (id))
SQL: CREATE TABLE IF NOT EXISTS Users (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(50) NOT NULL, city INTNOT NULL, age INTNOT NULL, CONSTRAINT pk_Users PRIMARY KEY (id))
SQL: CREATE INDEX Users_name ON Users (name)
SQL: ALTER TABLE Users ADD FOREIGN KEY (city) REFERENCES Cities(id)
SQL: INSERT INTO Cities (name) VALUES ('St. Petersburg'),('Munich')
SQL: SELECTCities.id, Cities.nameFROM Cities
Cities: St. Petersburg, Munich
SQL: INSERT INTO Users (name, city, age) VALUES ('a', 1, 5),('b', 1, 27),('c', 2, 42)
SQL: SELECTUsers.id, Users.name, Users.city, Users.ageFROM Users WHEREUsers.city=1
Users in St. Petersburg: a, b
SQL: SELECTUsers.id, Users.name, Users.city, Users.ageFROM Users WHEREUsers.age>=18
Adults: b, c

License

Apache License, Version 2.0, (LICENSE or https://www.apache.org/licenses/LICENSE-2.0)