# Repository: JetBrains/Exposed # Stars: 9208 ## CLAUDE.md # CLAUDE.md This file provides guidance to Claude Code (claude.ai/code) when working with code in this repository. ## Project Overview Exposed is a lightweight ORM framework for Kotlin that provides two APIs: - **DSL API**: Type-safe SQL-wrapping Domain Specific Language (in `exposed-core`) - Works with both JDBC (`exposed-jdbc`) and R2DBC (`exposed-r2dbc`) - **DAO API**: Lightweight Data Access Object API (in `exposed-dao`) - **Only works with JDBC** - does not support R2DBC ## Module Architecture ### Core Modules - **exposed-core**: Foundation layer with DSL API, database abstractions, column types, and vendor dialects - **exposed-dao**: DAO API with entity classes and relationships (JDBC only, does not work with R2DBC) - **exposed-jdbc**: JDBC implementation with blocking transactions - **exposed-r2dbc**: R2DBC implementation with suspending transactions ### Extension Modules - **exposed-java-time**, **exposed-jodatime**, **exposed-kotlin-datetime**: Date/time support - **exposed-json**: JSON/JSONB column types - **exposed-crypt**: Encrypted column types - **exposed-money**: JavaMoney MonetaryAmount support - **exposed-migration-core**: Common migration functionality - **exposed-migration-jdbc**: JDBC-based schema migrations - **exposed-migration-r2dbc**: R2DBC-based schema migrations - **exposed-spring-boot-starter**: Spring Boot integration - **spring-transaction**: Spring Framework transaction manager ### Test Modules - **exposed-tests**: Main JDBC-based test suite - **exposed-r2dbc-tests**: R2DBC-specific test suite - **exposed-jdbc-r2dbc-tests**: Cross-compatibility tests ## Build & Development ### Building ```bash ./gradlew compileKotlin # Compile the projects code ./gradlew detekt # Validate code style ./gradlew apiDump # Update dokka API docs after changing public API ``` ### Running Tests Tests are organized by database and dialect. Each module has database-specific test tasks: #### Quick test with H2 (no Docker required) ```bash ./gradlew test_h2_v2 # All modules with H2 ./gradlew :exposed-tests:test_h2_v2 # JDBC Tests with H2 ./gradlew :exposed-r2dbc-tests:test_h2_v2 # R2DBC Tests with H2 ``` #### Test with Postgres ```bash ./gradlew test_postgres # All modules with Postgres ./gradlew :exposed-tests:test_postgres # JDBC Tests with Postgres ./gradlew :exposed-r2dbc-tests:test_postgres # R2DBC Tests with Postgres ``` #### Test with specific database (requires Docker) ```bash # Start database containers first ./gradlew mariadbComposeUp # Start MariaDB ./gradlew postgresComposeUp # Start PostgreSQL ./gradlew mysql8ComposeUp # Start MySQL 8 ./gradlew oracleComposeUp # Start Oracle ./gradlew sqlserverComposeUp # Start SQL Server # Run tests ./gradlew :exposed-tests:test_postgres ./gradlew :exposed-tests:test_mysql_v8 ./gradlew :exposed-tests:test_mariadb # Stop containers ./gradlew postgresComposeDownForced ``` #### Run specific test class or method with H2 ```bash ./gradlew :exposed-tests:test_h2_v2 --tests "org.jetbrains.exposed.v1.tests.shared.dml.InsertTests" ./gradlew :exposed-tests:test_h2_v2 --tests "*.InsertTests.testBatchInsert" ``` #### Available test databases - `test_h2_v2`, `test_h2_v2_mysql`, `test_h2_v2_psql`, etc. (H2 with different dialect emulations) - `test_sqlite` - `test_mysql_v5`, `test_mysql_v8` - `test_mariadb` - `test_postgres`, `test_postgresng` - `test_oracle` - `test_sqlserver` ## Testing Infrastructure ### Test Base Classes and Utilities Tests inherit from different base classes depending on the driver: **JDBC Tests** - inherit from `DatabaseTestsBase` (in `exposed-tests/src/main/kotlin/org/jetbrains/exposed/v1/tests/`): - Tests are parameterized by database dialect using `@ParameterizedClass` and `@MethodSource("data")` - Each test automatically runs against all enabled dialects - Available dialects are determined by system properties set by Gradle test tasks **R2DBC Tests** - inherit from `R2dbcDatabaseTestsBase` (in `exposed-r2dbc-tests/src/main/kotlin/`): - Similar parameterized testing pattern as JDBC - Uses suspending functions and coroutine context - Test methods use `= runTest { }` for coroutine support, or utils methods like `withDb`, `withTables`, ### TestDB Enums There are separate `TestDB` enums for JDBC and R2DBC tests: **JDBC TestDB** (`exposed-tests/src/main/kotlin/org/jetbrains/exposed/v1/tests/TestDB.kt`): - Connection strings using JDBC URLs (e.g., `jdbc:h2:mem:...`, `jdbc:postgresql://...`) - JDBC driver class names - Before/after connection hooks - Database-specific configuration (e.g., H2 dialect emulation modes) Available JDBC TestDB values: - `H2_V2`, `H2_V2_MYSQL`, `H2_V2_PSQL`, `H2_V2_MARIADB`, `H2_V2_ORACLE`, `H2_V2_SQLSERVER` - `SQLITE`, `MYSQL_V5`, `MYSQL_V8`, `MARIADB`, `POSTGRESQL`, `POSTGRESQLNG`, `ORACLE`, `SQLSERVER` **R2DBC TestDB** (`exposed-r2dbc-tests/src/main/kotlin/org/jetbrains/exposed/v1/r2dbc/tests/TestDB.kt`): - Connection strings using R2DBC URLs (e.g., `r2dbc:h2:mem:...`, `r2dbc:postgresql://...`) - R2DBC isolation levels - Suspend-aware before/after connection hooks Available R2DBC TestDB values: - `H2_V2`, `H2_V2_MYSQL`, `H2_V2_PSQL`, `H2_V2_MARIADB`, `H2_V2_ORACLE`, `H2_V2_SQLSERVER` - `MYSQL_V5`, `MYSQL_V8`, `MARIADB`, `POSTGRESQL`, `ORACLE`, `SQLSERVER` - Note: R2DBC does **not** support `SQLITE` or `POSTGRESQLNG` ### Writing Tests Tests extend `DatabaseTestsBase` and use these helper functions: #### JDBC Tests with `withDb` ```kotlin class MyTests : DatabaseTestsBase() { @Test fun testSomething() { withDb { testDb -> // Runs against current dialect // Create tables SchemaUtils.create(MyTable) // Insert/query data MyTable.insert { it[name] = "test" } // Clean up SchemaUtils.drop(MyTable) } } } ``` #### Using `withTables` for automatic table management ```kotlin @Test fun testWithTables() { withTables(MyTable, AnotherTable) { // Tables are created before block and dropped after MyTable.insert { it[name] = "test" } } } ``` #### Conditional tests ```kotlin @Test fun testPostgresOnly() { withDb(TestDB.POSTGRESQL) { // Only runs for PostgreSQL // Postgres-specific test } } ``` #### Skip databases that don't support a feature ```kotlin @Test fun testJsonSupport() { withTables(JsonTable, excludeSettings = listOf(TestDB.SQLITE, TestDB.MYSQL_V5)) { // Test JSON columns } } ``` ## Important Patterns ### Transaction Context - JDBC: `transaction { }` - blocking transaction execution - JDBC: `suspendTransaction { }` - suspending, with actually blocking database connections - R2DBC: `suspendTransaction { }` - suspending, uses coroutine context - Never mix JDBC and R2DBC transaction functions ### Database Vendor Support Database-specific behavior is in `exposed-core/src/main/kotlin/org/jetbrains/exposed/v1/core/vendors/`: - `H2.kt`, `MysqlDialect.kt`, `PostgreSQL.kt`, `OracleDialect.kt`, `SQLServerDialect.kt`, `SQLiteDialect.kt`, `MariaDBDialect.kt` - Extend `DatabaseDialect` and implement `VendorDialect` - Override `DataTypeProvider` and `FunctionProvider` for dialect-specific SQL ## Common Development Tasks ### Adding a new column type 1. Create column type class in `exposed-core` (extends `ColumnType`) 2. Add factory method to `Table` class 3. Add dialect-specific SQL type mapping in `DataTypeProvider` implementations 4. Add tests in `exposed-tests` covering multiple databases 5. Add tests in `exposed-r2dbc-tests` covering multiple databases ### Working with migrations - Migration modules use serialization to track schema state - JDBC migrations: `exposed-migration-jdbc` with `MigrationUtils` - R2DBC migrations: `exposed-migration-r2dbc` with suspend support - Both share common code from `exposed-migration-core` ## Best Practices and Gotchas ### Multi-Database Compatibility - Always test features against multiple databases, especially H2, PostgreSQL, and MySQL - Use dialect checks when implementing database-specific features: ```kotlin if (currentDialectTest is PostgreSQLDialect) { // PostgreSQL-specific code } ``` - H2 dialect emulation modes (`H2_V2_MYSQL`, `H2_V2_PSQL`, etc.) help catch compatibility issues early ### Testing Best Practices - Extend `DatabaseTestsBase` or `R2dbcDatabaseTestsBase` for parameterized multi-database testing - Use `Assumptions.assumeTrue()` or `excludeSettings` argument in `withTables` to skip tests for unsupported databases - Prefer `withTables` over manual `SchemaUtils.create/drop` for cleaner tests - Test both JDBC and R2DBC implementations when adding core features - Use `currentDialectTest` to access current dialect in assertions ### API Compatibility - Run `./gradlew apiCheck` before committing public API changes - Binary compatibility is critical - breaking changes require major version bump - Use `@InternalApi` annotation for internal implementation details - Document breaking changes in BREAKING_CHANGES.md under "Breaking changes" section ## Code Style and Conventions ### Style Configuration - **EditorConfig**: `.editorconfig` defines code formatting rules - Indent: 4 spaces - Max line length: 166 characters - Charset: UTF-8 - End of line: LF - Kotlin code style: KOTLIN_OFFICIAL - **Detekt**: Static analysis with `detekt/detekt-config.yml` - Max issues: 0 (all issues must be fixed) - Wildcard imports are allowed - Magic numbers allowed in named arguments and ranges - Run with: `./gradlew detekt` ### Naming Conventions - Package structure uses `org.jetbrains.exposed.v1.*` namespace - Table objects: PascalCase (e.g., `Users`, `Cities`) - Column names: camelCase in code, snake_case in SQL - Test classes: Suffix with `Tests` or `Test` - Test methods: Descriptive names starting with `test` ### Common Utilities Located in `exposed-tests/src/main/kotlin/org/jetbrains/exposed/v1/tests/`: - `TestUtils.kt`: `currentDialectTest`, `currentDialectMetadataTest`, helper functions - `DatabaseTestsBase.kt`: Base class for all JDBC tests - `R2DBCDatabaseTestsBase.kt`: Base class for all R2DBC tests - `TestDB.kt`: Database connection configurations - `shared/Assert.kt`: Custom assertion functions - `shared/MiscTable.kt`, `shared/ForeignKeyTables.kt`: Reusable test tables ## Sample Projects The `samples/` directory contains reference implementations: - **exposed-ktor**: Ktor application with JDBC - **exposed-ktor-r2dbc**: Ktor application with R2DBC - **exposed-migration**: Migration examples - **exposed-spring**: Spring Boot integration examples These demonstrate best practices for using Exposed in real applications. ## Key Files - `buildSrc/`: Custom Gradle plugins and build configuration - `build.gradle.kts`: Root build configuration with testDb DSL usage - `settings.gradle.kts`: Module definitions - `buildScripts/docker/`: Database container configurations - `gradle.properties`: Version and build settings - `.editorconfig`: Code formatting rules - `detekt/detekt-config.yml`: Static analysis configuration ## README.md
Exposed logo

[![Official JetBrains project](https://jb.gg/badges/official.svg)](https://confluence.jetbrains.com/display/ALL/JetBrains+on+GitHub) [![Kotlin](https://img.shields.io/badge/dynamic/toml?url=https://raw.githubusercontent.com/JetBrains/Exposed/refs/heads/main/gradle/libs.versions.toml&query=%24.versions.kotlin&logo=kotlin&label=kotlin&color=blue)](http://kotlinlang.org) [![Slack Channel](https://img.shields.io/badge/chat-exposed-yellow.svg?logo=slack)](https://kotlinlang.slack.com/messages/exposed/) [![TC Build status](https://exposed.teamcity.com/app/rest/builds/buildType:id:Exposed_Build/statusIcon.svg)](https://exposed.teamcity.com/viewType.html?buildTypeId=Exposed_Build&guest=1) [![Maven Central](https://img.shields.io/maven-central/v/org.jetbrains.exposed/exposed-core?label=maven+central)](https://central.sonatype.com/search?namespace=org.jetbrains.exposed) [![GitHub License](https://img.shields.io/badge/license-Apache%20License%202.0-blue.svg?style=flat)](https://www.apache.org/licenses/LICENSE-2.0)
## Welcome to **Exposed**, an ORM framework for [Kotlin](https://github.com/JetBrains/kotlin). [Exposed](https://www.jetbrains.com/exposed/) is a lightweight SQL library on top of a database connectivity driver for the Kotlin programming language, with support for both JDBC and R2DBC (since version 1.0.0-*) drivers. It offers two approaches for database access: a typesafe SQL-wrapping Domain-Specific Language (DSL) and a lightweight Data Access Object (DAO) API. Our official mascot is the cuttlefish, which is well-known for its outstanding mimicry ability that enables it to blend seamlessly into any environment. Similar to our mascot, Exposed can be used to mimic a variety of database engines, which helps you to build applications without dependencies on any specific database engine and to switch between them with very little or no changes. ## Supported Databases - H2 (versions 2.x) - [![MariaDB](https://img.shields.io/badge/MariaDB-003545?style=for-the-badge&logo=mariadb&logoColor=white)](https://github.com/mariadb-corporation/mariadb-connector-j) - [![MySQL](https://img.shields.io/badge/mysql-4479A1.svg?style=for-the-badge&logo=mysql&logoColor=white)](https://github.com/mysql/mysql-connector-j) - [![Oracle](https://img.shields.io/badge/Oracle-F80000?style=for-the-badge&logo=oracle&logoColor=white)](https://www.oracle.com/ca-en/database/technologies/appdev/jdbc-downloads.html) - [![Postgres](https://img.shields.io/badge/postgres-%23316192.svg?style=for-the-badge&logo=postgresql&logoColor=white)](https://jdbc.postgresql.org/) (Also, PostgreSQL using the [pgjdbc-ng](https://impossibl.github.io/pgjdbc-ng/) JDBC driver) - [![MicrosoftSQLServer](https://img.shields.io/badge/Microsoft%20SQL%20Server-CC2927?style=for-the-badge&logo=microsoft%20sql%20server&logoColor=white)](https://github.com/microsoft/mssql-jdbc) - [![SQLite](https://img.shields.io/badge/sqlite-%2307405e.svg?style=for-the-badge&logo=sqlite&logoColor=white)](https://github.com/xerial/sqlite-jdbc) ## Dependencies Releases of Exposed are available in the [Maven Central repository](https://search.maven.org/search?q=org.jetbrains.exposed). For details on how to configure this repository and how to add Exposed dependencies to an existing Gradle/Maven project, see the full [guide on modules](https://www.jetbrains.com/help/exposed/exposed-modules.html). ### Exposed modules `Exposed` consists of the following core modules: | Module | Function | |-----------------|------------------------------------------------------------------------------------------------------------------------------------------------------------------| | `exposed-core` | Provides the foundational components and abstractions needed to work with databases in a type-safe manner and includes the Domain-Specific Language (DSL) API | | `exposed-dao` | (Optional) Allows you to work with the Data Access Object (DAO) API.
It is only compatible with `exposed-jdbc` and does not work with `exposed-r2dbc`.
| | `exposed-jdbc` | Provides support for Java Database Connectivity (JDBC) with a transport-level implementation based on the Java JDBC API | | `exposed-r2dbc` | Provides support for Reactive Relational Database Connectivity (R2DBC) | As well as the following extension modules: | Module | Function | |--------------------------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | `exposed-crypt` | Provides additional column types to store encrypted data in the database and encode/decode it on the client-side | | `exposed-java-time` | Date-time extensions based on the [Java 8 Time API](https://docs.oracle.com/javase/8/docs/api/java/time/package-summary.html) | | `exposed-jodatime` | Date-time extensions based on the [Joda-Time](https://www.joda.org/joda-time/) library | | `exposed-json` | JSON and JSONB data type extensions | | `exposed-kotlin-datetime` | Date-time extensions based on the [`kotlinx-datetime`](https://kotlinlang.org/api/kotlinx-datetime/) library | | `exposed-migration-core` | Provides core common functionality for database schema migrations | | `exposed-migration-jdbc` | Provides utilities to support database schema migrations, with a reliance on a JDBC driver | | `exposed-migration-r2dbc` | Provides utilities to support database schema migrations, with a reliance on a R2DBC driver | | `exposed-money` | Extensions to support [`MonetaryAmount`](https://javamoney.github.io/apidocs/java.money/javax/money/MonetaryAmount.html) from the [JavaMoney API](https://javamoney.github.io/) | | `exposed-spring-boot-starter` | A starter for [Spring Boot 3](https://spring.io/projects/spring-boot) to utilize Exposed as the ORM | | `exposed-spring-boot4-starter` | A starter for [Spring Boot 4](https://spring.io/projects/spring-boot) to utilize Exposed as the ORM | | `spring-transaction` | Transaction manager that builds on top of the standard transaction workflow from Spring Framework 6 | | `spring7-transaction` | Transaction manager that builds on top of the standard transaction workflow from Spring Framework 7 | ### Requirements Kotlin version 2.1.+ The following module(s) require JDK 17 or newer: * `spring-transaction` - depends on Spring Framework 6 * `spring7-transaction` - depends on Spring Framework 7 * `exposed-spring-boot-starter` - depends on Spring Boot 3 * `exposed-spring-boot4-starter` - depends on Spring Boot 4 * `exposed-crypt` - depends on Spring Security 7 The following module(s) require JDK 11 or newer: * `exposed-r2dbc` * `exposed-migration-r2dbc` All other modules have a minimum requirement of JDK 8. ## Samples using Exposed Follow the [Getting Started with DSL tutorial](https://www.jetbrains.com/help/exposed/getting-started-with-exposed.html) for a quick start or check out the [samples](samples/README.md) for more in-depth projects. ## Documentation For complete documentation, samples, and tutorials, see the following links: - [Documentation](https://www.jetbrains.com/help/exposed/home.html) - [Migration Guide](https://www.jetbrains.com/help/exposed/migration-guide-1-0-0.html) - [Breaking changes](https://www.jetbrains.com/help/exposed/breaking-changes.html) ## Contributing ### Reporting issues We encourage your feedback in any form, such as feature requests, bug reports, documentation updates, and questions. Please use [our issue tracker](https://youtrack.jetbrains.com/issues/EXPOSED) to report any issues or to log new requests. While issues are visible publicly, either creating a new issue or commenting on an existing one does require logging in to YouTrack. ### Submitting pull requests We actively welcome your pull requests and encourage you to link your work to an [existing issue](https://youtrack.jetbrains.com/issues/EXPOSED). See the full [contribution guide](https://www.jetbrains.com/help/exposed/contributing.html#pull-requests) for more details. By contributing to the Exposed project, you agree that your contributions will be licensed under [Apache License, Version 2.0](https://www.apache.org/licenses/LICENSE-2.0).

## Support Have questions or want to contribute to the discussion? Join us in the [#exposed](https://kotlinlang.slack.com/messages/exposed/) channel on the [Kotlin Slack](https://kotlinlang.slack.com/). If you're not a member yet, you can [request an invitation](https://surveys.jetbrains.com/s3/kotlin-slack-sign-up). ## Examples ### SQL DSL ```kotlin import org.jetbrains.exposed.v1.core.* import org.jetbrains.exposed.v1.core.SqlExpressionBuilder.like import org.jetbrains.exposed.v1.jdbc.* import org.jetbrains.exposed.v1.jdbc.transactions.transaction object Cities : Table() { val id = integer("id").autoIncrement() val name = varchar("name", 50) override val primaryKey = PrimaryKey(id) } object Users : Table() { val id = varchar("id", 10) val name = varchar("name", length = 50) val cityId = integer("city_id").references(Cities.id).nullable() override val primaryKey = PrimaryKey(id, name = "PK_User_ID") } fun main() { 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 .selectAll() .where { Cities.id eq pragueId } .single()[Cities.name] println("pragueName = $pragueName") Users.insert { it[id] = "andrey" it[name] = "Andrey" it[cityId] = saintPetersburgId } Users.insert { it[id] = "sergey" it[name] = "Sergey" it[cityId] = munichId } Users.insert { it[id] = "eugene" it[name] = "Eugene" it[cityId] = munichId } Users.insert { it[id] = "alex" it[name] = "Alex" it[cityId] = null } Users.insert { it[id] = "smth" it[name] = "Something" it[cityId] = null } Users.update(where = { Users.id eq "alex" }) { it[name] = "Alexey" } Users.deleteWhere { Users.name like "%thing" } println("All cities:") Cities .selectAll() .forEach { result -> println("${result[Cities.id]}: ${result[Cities.name]}") } println("Manual join:") (Users innerJoin Cities) .select(Users.name, Cities.name) .where { (Users.id.eq("andrey") or Users.name.eq("Sergey")) and Users.id.eq("sergey") and Users.cityId.eq(Cities.id) }.forEach { result -> println("${result[Users.name]} lives in ${result[Cities.name]}") } println("Join with foreign key:") (Users innerJoin Cities) .select(Users.name, Users.cityId, Cities.name) .where { Cities.name.eq("St. Petersburg") or Users.cityId.isNull() } .forEach { result -> if (result[Users.cityId] != null) { println("${result[Users.name]} lives in ${result[Cities.name]}") } else { println("${result[Users.name]} lives nowhere") } } println("Functions and group by:") (Cities innerJoin Users) .select(Cities.name, Users.id.count()) .groupBy(Cities.name) .forEach { result -> val cityName = result[Cities.name] val userCount = result[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 SQL: CREATE TABLE IF NOT EXISTS CITIES (ID INT AUTO_INCREMENT PRIMARY KEY, "name" VARCHAR(50) NOT NULL) SQL: CREATE TABLE IF NOT EXISTS USERS (ID VARCHAR(10), "name" VARCHAR(50) NOT NULL, CITY_ID INT NULL, CONSTRAINT PK_User_ID PRIMARY KEY (ID), CONSTRAINT FK_USERS_CITY_ID__ID FOREIGN KEY (CITY_ID) REFERENCES CITIES(ID) ON DELETE RESTRICT ON UPDATE RESTRICT) SQL: INSERT INTO CITIES ("name") VALUES ('St. Petersburg') SQL: INSERT INTO CITIES ("name") VALUES ('Munich') SQL: INSERT INTO CITIES ("name") VALUES (SUBSTRING(TRIM(' Prague '), 1, 2)) SQL: SELECT CITIES.ID, CITIES."name" FROM CITIES WHERE CITIES.ID = 3 pragueName = Pr 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' WHERE USERS.ID = 'alex' SQL: DELETE FROM USERS WHERE USERS."name" LIKE '%thing' All cities: SQL: SELECT CITIES.ID, CITIES."name" FROM CITIES 1: St. Petersburg 2: Munich 3: Pr Manual join: SQL: SELECT USERS."name", CITIES."name" FROM USERS INNER JOIN CITIES ON CITIES.ID = USERS.CITY_ID WHERE ((USERS.ID = 'andrey') OR (USERS."name" = 'Sergey')) AND (USERS.ID = 'sergey') AND (USERS.CITY_ID = CITIES.ID) Sergey lives in Munich Join with foreign key: SQL: SELECT USERS."name", USERS.CITY_ID, CITIES."name" FROM USERS INNER JOIN CITIES ON CITIES.ID = USERS.CITY_ID WHERE (CITIES."name" = 'St. Petersburg') OR (USERS.CITY_ID IS NULL) Andrey lives in St. Petersburg Functions and group by: SQL: SELECT CITIES."name", COUNT(USERS.ID) FROM CITIES INNER JOIN USERS ON CITIES.ID = USERS.CITY_ID GROUP BY CITIES."name" 2 user(s) live(s) in Munich 1 user(s) live(s) in St. Petersburg SQL: DROP TABLE IF EXISTS USERS SQL: DROP TABLE IF EXISTS CITIES ``` ### DAO ```kotlin import org.jetbrains.exposed.v1.core.StdOutSqlLogger import org.jetbrains.exposed.v1.core.dao.id.* import org.jetbrains.exposed.v1.dao.* import org.jetbrains.exposed.v1.jdbc.* import org.jetbrains.exposed.v1.jdbc.transactions.transaction object Cities: IntIdTable() { val name = varchar("name", 50) } object Users : IntIdTable() { val name = varchar("name", length = 50).index() val city = reference("city", Cities) val age = integer("age") } class City(id: EntityID) : IntEntity(id) { companion object : IntEntityClass(Cities) var name by Cities.name val users by User referrersOn Users.city } class User(id: EntityID) : IntEntity(id) { companion object : IntEntityClass(Users) var name by Users.name var city by City referencedOn Users.city var age by Users.age } fun main() { Database.connect("jdbc:h2:mem:test", driver = "org.h2.Driver", user = "root", password = "") transaction { addLogger(StdOutSqlLogger) val saintPetersburg = City.new { name = "St. Petersburg" } val munich = City.new { name = "Munich" } User.new { name = "Andrey" city = saintPetersburg age = 5 } User.new { name = "Sergey" city = saintPetersburg age = 27 } User.new { name = "Eugene" city = munich age = 42 } val alex = User.new { name = "alex" city = munich age = 11 } alex.name = "Alexey" println("Cities: ${City.all().joinToString { it.name }}") println("Users in ${saintPetersburg.name}: ${saintPetersburg.users.joinToString { it.name }}") println("Adults: ${User.find { Users.age greaterEq 18 }.joinToString { it.name }}") SchemaUtils.drop(Users, Cities) } } ``` Generated SQL: ```sql SQL: CREATE TABLE IF NOT EXISTS CITIES (ID INT AUTO_INCREMENT PRIMARY KEY, "name" VARCHAR(50) NOT NULL) SQL: CREATE TABLE IF NOT EXISTS USERS (ID INT AUTO_INCREMENT PRIMARY KEY, "name" VARCHAR(50) NOT NULL, CITY INT NOT NULL, AGE INT NOT NULL, CONSTRAINT FK_USERS_CITY__ID FOREIGN KEY (CITY) REFERENCES CITIES(ID) ON DELETE RESTRICT ON UPDATE RESTRICT) SQL: CREATE INDEX USERS_NAME ON USERS ("name") SQL: INSERT INTO CITIES ("name") VALUES ('St. Petersburg') SQL: INSERT INTO CITIES ("name") VALUES ('Munich') SQL: SELECT CITIES.ID, CITIES."name" FROM CITIES Cities: St. Petersburg, Munich SQL: INSERT INTO USERS ("name", CITY, AGE) VALUES ('Andrey', 1, 5) SQL: INSERT INTO USERS ("name", CITY, AGE) VALUES ('Sergey', 1, 27) SQL: INSERT INTO USERS ("name", CITY, AGE) VALUES ('Eugene', 2, 42) SQL: INSERT INTO USERS ("name", CITY, AGE) VALUES ('Alexey', 2, 11) SQL: SELECT USERS.ID, USERS."name", USERS.CITY, USERS.AGE FROM USERS WHERE USERS.CITY = 1 Users in St. Petersburg: Andrey, Sergey SQL: SELECT USERS.ID, USERS."name", USERS.CITY, USERS.AGE FROM USERS WHERE USERS.AGE >= 18 Adults: Sergey, Eugene SQL: DROP TABLE IF EXISTS USERS SQL: DROP TABLE IF EXISTS CITIES ```