### Run Exposed SQL Functions Application

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/snippets/exposed-sql-functions/README.md

Execute this command to run the Gradle application and test the SQL functions examples. This will create tables and execute all functions defined in the `/examples` folder. Modify `App.kt` to run specific examples.

```shell
./gradlew :exposed-sql-functions:run
```

--------------------------------

### Connect to SQLite Database

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Working-with-Database.md

Example of connecting to a SQLite database file.

```kotlin
Database.connect("jdbc:sqlite:/data/data.db", "org.sqlite.JDBC")
```

--------------------------------

### Run the Gradle Application

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/snippets/exposed-transactions/README.md

Command to execute the application and run the database examples.

```shell
./gradlew :exposed-transactions:run
```

--------------------------------

### Run the Exposed DAO relationships project

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/snippets/exposed-dao-relationships/README.md

Execute this command in the snippets folder to run the application and execute all examples.

```shell
./gradlew :exposed-dao-relationships:run
```

--------------------------------

### Application Execution Output

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Get-started-with-Exposed-DAO.md

Example output showing SQL logs and query results in the IDE console.

```generic
SQL: SELECT SETTING_VALUE FROM INFORMATION_SCHEMA.SETTINGS WHERE SETTING_NAME = 'MODE'
SQL: CREATE TABLE IF NOT EXISTS TASKS (ID INT AUTO_INCREMENT PRIMARY KEY, "name" VARCHAR(128) NOT NULL, DESCRIPTION VARCHAR(128) NOT NULL, COMPLETED BOOLEAN DEFAULT FALSE NOT NULL)
SQL: INSERT INTO TASKS ("name", DESCRIPTION, COMPLETED) VALUES ('Learn Exposed DAO', 'Follow the DAO tutorial', FALSE)
SQL: INSERT INTO TASKS ("name", DESCRIPTION, COMPLETED) VALUES ('Read The Hobbit', 'Read chapter one', TRUE)
Created new tasks with ids 1 and 2
SQL: SELECT TASKS.ID, TASKS."name", TASKS.DESCRIPTION, TASKS.COMPLETED FROM TASKS WHERE TASKS.COMPLETED = TRUE
Completed tasks: 1
```

--------------------------------

### Install Colima Container Runtime

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Contributing.md

Use this command to install Colima, a container runtime necessary for running Oracle XE tests on Apple Silicon.

```shell
brew install colima
```

--------------------------------

### Generated Migration Files Example

Source: https://github.com/jetbrains/exposed/blob/main/samples/exposed-gradle-plugin-sample/README.md

After running generateMigrations, SQL DDL files are created in the specified directory. The output indicates the number of migrations generated.

```text
# Exposed Migrations Generated 2 migrations:
  * V<ts>__CREATE_TABLE_USERS.sql
  * V<ts>__CREATE_TABLE_CITIES.sql
```

--------------------------------

### Connect to SQL Server Database via JDBC

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Working-with-Database.md

Example of connecting to a SQL Server database using JDBC.

```kotlin
Database.connect("jdbc:sqlserver://localhost:1433;databaseName=database", "user", "password")
```

--------------------------------

### Connect to SQLite In-Memory Database

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Working-with-Database.md

Example of connecting to an in-memory SQLite database.

```kotlin
Database.connect("jdbc:sqlite:file:test?mode=memory&cache=shared", "org.sqlite.JDBC")
```

--------------------------------

### Configure PostgreSQL Database Connection

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/snippets/exposed-data-types/README.md

Connect to a PostgreSQL database using the provided credentials. Ensure your database connection details match your setup.

```Kotlin
val postgreSQL = Database.connect(
        "jdbc:postgresql://localhost:5432/postgres",
        driver = "org.postgresql.Driver",
        user = "user",
        password = "password"
    )
```

--------------------------------

### Connect to SQL Server Database via R2DBC

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Working-with-Database.md

Example of establishing a connection to a SQL Server database using R2DBC.

```kotlin
Database.connect("r2dbc:sqlserver://localhost:1433", "user", "password")
```

--------------------------------

### Connect to PostgreSQL Database via R2DBC

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Working-with-Database.md

Example of establishing a connection to a PostgreSQL database using R2DBC.

```kotlin
Database.connect("r2dbc:postgresql://localhost:5432/database", "user", "password")
```

--------------------------------

### Connect to Oracle Database via JDBC

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Working-with-Database.md

Example of establishing a connection to an Oracle database using JDBC.

```kotlin
Database.connect("jdbc:oracle:thin:@localhost:1521:xe", "oracle", "user", "password")
```

--------------------------------

### UUIDTable and UUIDEntity with java.util.UUID (Exposed 0.61.0)

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Migration-Guide-1-0-0.md

Example demonstrating the usage of UUIDTable and UUIDEntity with java.util.UUID in Exposed version 0.61.0. This setup is for older versions and requires specific imports.

```kotlin
import org.jetbrains.exposed.dao.id.*
import org.jetbrains.exposed.dao.*
import java.util.UUID

object TestTable : UUIDTable("tester") {
    val secondaryId = uuid("secondary_id").uniqueIndex()
}

class TestEntity(id: EntityID<UUID>) : UUIDEntity(id) {
    companion object : UUIDEntityClass<TestEntity>(TestTable)

    var secondaryId by TestTable.secondaryId
}
```

--------------------------------

### Connect to PostgreSQL Database via JDBC

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Working-with-Database.md

Example of connecting to a PostgreSQL database using the standard JDBC driver.

```kotlin
Database.connect("jdbc:postgresql://localhost:5432/database", "user", "password")
```

--------------------------------

### Start Colima Daemon in x86_64 Mode

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Contributing.md

Start the Colima daemon in x86_64 emulation mode. This is required for specific testing scenarios on Apple Silicon.

```shell
colima start --arch x86_64 --memory 4 --network-address
```

--------------------------------

### Run Exposed DSL Application

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/snippets/exposed-dsl/README.md

Run the Exposed DSL application using Gradle to execute database queries and examples. Navigate to the 'snippets' folder before running.

```shell
./gradlew :exposed-dsl:run
```

--------------------------------

### Connect to Oracle Database via R2DBC

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Working-with-Database.md

Example of establishing a connection to an Oracle database using R2DBC.

```kotlin
Database.connect("r2dbc:oracle://localhost:1521/xe", "user", "password")
```

--------------------------------

### Configure PostgreSQL Connection

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/snippets/exposed-databases-jdbc/README.md

Define a PostgreSQL database connection using Exposed. Ensure your connection credentials match your setup.

```Kotlin
    val postgreSQL = Database.connect(
        "jdbc:postgresql://localhost:5432/postgres",
        driver = "org.postgresql.Driver",
        user = "user",
        password = "password"
    )
```

--------------------------------

### UuidTable and UuidEntity with kotlin.uuid.Uuid (Exposed 1.0.0)

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Migration-Guide-1-0-0.md

Example showing the updated UuidTable and UuidEntity for kotlin.uuid.Uuid in Exposed 1.0.0. Requires experimental API opt-in.

```kotlin
import org.jetbrains.exposed.v1.core.dao.id.*
import org.jetbrains.exposed.v1.dao.*
import kotlin.uuid.ExperimentalUuidApi
import kotlin.uuid.Uuid

object TestTable : UuidTable("tester") {
    @OptIn(ExperimentalUuidApi::class)
    val secondaryId = uuid("secondary_id").uniqueIndex()
}

@OptIn(ExperimentalUuidApi::class)
class TestEntity(id: EntityID<Uuid>) : UuidEntity(id) {
    companion object : UuidEntityClass<TestEntity>(TestTable)

    var secondaryId by TestTable.secondaryId
}
```

--------------------------------

### UUIDTable and UUIDEntity with java.util.UUID (Exposed 1.0.0)

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Migration-Guide-1-0-0.md

Example demonstrating how to continue using java.util.UUID with UUIDTable and UUIDEntity in Exposed 1.0.0. This involves updated imports and using the .javaUUID() extension function.

```kotlin
import org.jetbrains.exposed.v1.core.dao.id.*
import org.jetbrains.exposed.v1.core.dao.id.java.UUIDTable
import org.jetbrains.exposed.v1.core.java.javaUUID
import org.jetbrains.exposed.v1.dao.java.*
import java.util.UUID

object TestTable : UUIDTable("tester") {
    val secondaryId = javaUUID("secondary_id").uniqueIndex()
}

class TestEntity(id: EntityID<UUID>) : UUIDEntity(id) {
    companion object : UUIDEntityClass<TestEntity>(TestTable)

    var secondaryId by TestTable.secondaryId
}
```

--------------------------------

### Generated SQL and Output

Source: https://github.com/jetbrains/exposed/blob/main/README.md

Displays the SQL statements executed and the corresponding output generated by the Kotlin DAO example. This includes table creation, data insertion, queries, and table drops.

```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
```

--------------------------------

### Run the Exposed Tutorial Project with Gradle

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/snippets/get-started-with-exposed/README.md

Execute this command in the repository's root directory to run the application.

```bash
./gradlew :get-started-with-exposed:run
```

--------------------------------

### Build the Exposed Tutorial Project with Gradle

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/snippets/get-started-with-exposed/README.md

Use this command to build the application by following the steps in the tutorial.

```shell
./gradlew :get-started-with-exposed:build
```

--------------------------------

### Using Window Functions with OVER, PARTITION BY, and ORDER BY

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/SQL-Functions.md

Demonstrates how to apply window functions like `sum()` with `OVER`, `PARTITION BY`, and `ORDER BY` clauses. Ensure the `OVER` clause is chained after the function call.

```kotlin
val avgSalary = avg(salary).over(partition = { department }, orderBy = { name DESC })

```

--------------------------------

### Build Exposed Maven Project

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/snippets/exposed-modules-maven/README.md

Navigate to the `snippets` folder in your terminal and run this command to build the application.

```shell
./gradlew :exposed-modules-maven:build
```

--------------------------------

### Extract Substring

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/SQL-Functions.md

Demonstrates the .substring() function to extract a portion of a string based on start position and length.

```kotlin
val substringFilm = FilmBoxOfficeTable.film.substring(1, 3)
    FilmBoxOfficeTable.select { substringFilm.isNotNull() }
```

--------------------------------

### Run Exposed DAO Application

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/snippets/exposed-dao/README.md

Execute this command to run the Exposed DAO application. This will create tables and execute functions defined in the `/examples` folder. Navigate to the `snippets` folder in your terminal first.

```shell
./gradlew :exposed-dao:run
```

--------------------------------

### Programmatically initialize database schema

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Spring-Boot-integration.md

Use an ApplicationRunner to create the schema at startup, as dynamic DDL generation is restricted in native images.

```kotlin
@Component
@Transactional
class SchemaInitialize : ApplicationRunner {
    override fun run(args: ApplicationArguments) {
        SchemaUtils.create(MessageEntity)
    }
}
```

--------------------------------

### Run the Ktor application

Source: https://github.com/jetbrains/exposed/blob/main/samples/exposed-ktor/README.md

Execute the application from the repository root directory using the Gradle wrapper.

```bash
./gradlew run
```

--------------------------------

### Build Gradle Application

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/snippets/exposed-data-types/README.md

Build the application using the Gradle wrapper. This command should be run from the `snippets` folder.

```shell
./gradlew :exposed-data-types:build
```

--------------------------------

### Running the Generate Migrations Task

Source: https://github.com/jetbrains/exposed/blob/main/samples/exposed-gradle-plugin-sample/README.md

Navigate to the sample directory and execute the Gradle task to generate database migrations based on your Exposed table definitions.

```bash
cd samples/exposed-gradle-plugin-sample
./gradlew generateMigrations
```

--------------------------------

### Define Table with Default Values

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Breaking-Changes.md

Example of a table definition using default values and default expressions.

```kotlin
object TestTable : IntIdTable("test") { 
  val number = integer("number").default(100)
  val expression = integer("exp")
      .defaultExpression(intLiteral(100) + intLiteral(200))
}

TestTable.insert { }
```

--------------------------------

### Connect to Database using Configuration Block

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Working-with-ConnectionFactory.md

Establishes a database connection using a configuration block to set default attempts and isolation levels.

```kotlin
import io.r2dbc.spi.IsolationLevel
import org.jetbrains.exposed.v1.r2dbc.R2dbcDatabase

val database = R2dbcDatabase.connect {
    defaultMaxAttempts = 1
    defaultR2dbcIsolationLevel = IsolationLevel.READ_COMMITTED

    setUrl("r2dbc:h2:mem:///test;DB_CLOSE_DELAY=-1;")
}
```

--------------------------------

### Build Exposed Migrations Application

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/snippets/exposed-migrations/README.md

Build the application using Gradle. Navigate to the 'snippets' folder in your terminal before running.

```shell
./gradlew :exposed-migrations:build
```

--------------------------------

### Locate Substring

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/SQL-Functions.md

Employs the .locate() function to find the starting index of a substring within a string. Returns 0 if not found.

```kotlin
val locateFilm = FilmBoxOfficeTable.film.locate("The")
    FilmBoxOfficeTable.select { locateFilm.greater(0) }
```

--------------------------------

### Build Exposed DAO Application

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/snippets/exposed-dao/README.md

Use this command to build the Exposed DAO application. Navigate to the `snippets` folder in your terminal before running.

```shell
./gradlew :exposed-dao:build
```

--------------------------------

### Build Exposed SQL Functions Application

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/snippets/exposed-sql-functions/README.md

Use this command to build the Gradle application that demonstrates Exposed SQL functions. Navigate to the `snippets` folder in your terminal before running.

```shell
./gradlew :exposed-sql-functions:build
```

--------------------------------

### Accessing Returned Values from Transaction

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Transactions.md

Transactions can return values. This example retrieves a list of `ResultRow` objects containing `UsersTable` data.

```kotlin
val idsAndContent = transaction {
   Documents.selectAll().limit(10).map { it[Documents.id] to it[Documents.content] }
}
```

```kotlin
val documentsWithContent = transaction {
   Documents.selectAll().limit(10)
}
```

--------------------------------

### Configure Database Connection for Migrations

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Exposed-gradle-plugin.md

Set up direct database connection details for schema comparison and migration generation.

```kotlin
exposed {
    migrations {
        tablesPackage.set("com.example.db.tables")
        databaseUrl.set("jdbc:postgresql://localhost:5432/mydb")
        databaseUser.set("postgres")
        databasePassword.set("password")
    }
}
```

--------------------------------

### Get Character Length

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/SQL-Functions.md

Utilizes the .charLength() function to determine the number of characters in a string expression. Returns null for null strings.

```kotlin
val charLengthFilm = FilmBoxOfficeTable.film.charLength()
    FilmBoxOfficeTable.select { charLengthFilm.eq(5) }
```

--------------------------------

### Build the Exposed DAO relationships project

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/snippets/exposed-dao-relationships/README.md

Execute this command in the snippets folder to compile the project.

```shell
./gradlew :exposed-dao-relationships:build
```

--------------------------------

### Construct ConnectionFactoryOptions from Scratch

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Working-with-ConnectionFactory.md

Builds the connection configuration entirely from scratch using the connectionFactoryOptions builder.

```kotlin
import io.r2dbc.spi.ConnectionFactoryOptions
import io.r2dbc.spi.IsolationLevel
import io.r2dbc.spi.Option
import org.jetbrains.exposed.v1.r2dbc.R2dbcDatabase

val database = R2dbcDatabase.connect {
    defaultMaxAttempts = 1
    defaultR2dbcIsolationLevel = IsolationLevel.READ_COMMITTED

    connectionFactoryOptions {
        option(ConnectionFactoryOptions.DRIVER, "h2")
        option(ConnectionFactoryOptions.PROTOCOL, "mem")
        option(ConnectionFactoryOptions.DATABASE, "test")
        option(Option.valueOf("DB_CLOSE_DELAY"), "-1")
    }
}
```

--------------------------------

### Define and Use SQL Tables with Exposed

Source: https://github.com/jetbrains/exposed/blob/main/README.md

Demonstrates defining table schemas (Cities, Users), connecting to a database, and performing CRUD operations within a transaction. Includes schema creation and dropping.

```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 {
                println("${it[Cities.id]}: ${it[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 {
                println("${it[Users.name]} lives in ${it[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 {
                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)
            .select(Cities.name, Users.id.count())
            .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)
    }
}
```

```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)
```

--------------------------------

### Build Exposed Kotlin Gradle Project

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/snippets/exposed-modules-kotlin-gradle/README.md

Navigate to the 'snippets' folder and execute this command in your terminal to build the project. This command compiles and tests the exposed-modules-kotlin-gradle module.

```shell
./gradlew :exposed-modules-kotlin-gradle:build
```

--------------------------------

### Access Raw JDBC Connection in Exposed

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Frequently-Asked-Questions.md

Use the `connection.connection` property within a `transaction` block to get the underlying `java.sql.Connection`. This allows direct execution of JDBC statements.

```Kotlin
transaction {
    val lowLevelCx = connection.connection as java.sql.Connection

    val stmt = lowLevelCx.prepareStatement("INSERT INTO TEST_TABLE (AMOUNT) VALUES (?)")
    stmt.setInt(1, 99)
    stmt.addBatch()
    stmt.setInt(1, 100)
    stmt.addBatch()
    stmt.executeBatch()

    val query = lowLevelCx.createStatement()
    val result = query.executeQuery("SELECT COUNT(*) FROM TEST_TABLE")
    result.next()
    val count = result.getInt(1)
    println(count) // 2
}
```

--------------------------------

### Exposed DAO Imports Update

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Breaking-Changes.md

Starting from version 1.0.0-beta-1, DAO-related classes have been reorganized. Entity ID classes are now in `org.jetbrains.exposed.v1.core.dao.id`, while other DAO types include the `v1` namespace in their imports.

--------------------------------

### Build Exposed Gradle Plugin Application

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/snippets/exposed-gradle-plugin/README.md

Use this command to build the application with the Exposed Gradle plugin. Navigate to the `snippets` folder in your terminal before running.

```shell
./gradlew :exposed-gradle-plugin:build
```

--------------------------------

### Build the Gradle Application

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/snippets/exposed-transactions/README.md

Command to compile the project using the Gradle wrapper.

```shell
./gradlew :exposed-transactions:build
```

--------------------------------

### Configuring H2 Data Source in IntelliJ

Source: https://github.com/jetbrains/exposed/blob/main/samples/exposed-gradle-plugin-sample/README.md

Instructions for setting up an H2 database connection in IntelliJ IDEA's Database tool window to apply and verify migrations.

```text
1. **Database tool window** → **+** → **Data Source** → **H2**.
2. URL: jdbc:h2:file:<absolute-path-to-sample>/data/mydb, user sa, empty password.
3. When a `generateMigrations` run produces `src/main/resources/db/migration/V<...>.sql`, open that file in the editor, right-click → **Execute → <your H2 data source>**.
4. Refresh the data source to verify the schema matches the table object(s).
5. Re-run `generateMigrations` — it should now generate **0 migrations** (DB is in sync with code).
```

--------------------------------

### Run the Exposed DAO project

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/snippets/get-started-with-exposed-dao/README.md

Executes the Gradle run task for the project from the snippets directory.

```bash
./gradlew :get-started-with-exposed-dao:run
```

--------------------------------

### Build Exposed DSL Application

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/snippets/exposed-dsl/README.md

Build the Exposed DSL application using Gradle. Navigate to the 'snippets' folder before running.

```shell
./gradlew :exposed-dsl:build
```

--------------------------------

### Run Gradle Application

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/snippets/exposed-data-types/README.md

Run the application using the Gradle wrapper. This command should be run from the `snippets` folder.

```shell
./gradlew :exposed-data-types:run
```

--------------------------------

### Run Exposed Migrations Application

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/snippets/exposed-migrations/README.md

Execute the application to apply migrations using Exposed and Flyway. Run this command from the 'snippets' folder.

```shell
./gradlew :exposed-migrations:run
```

--------------------------------

### Build the Exposed Groovy Gradle project

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/snippets/exposed-modules-groovy-gradle/README.md

Execute this command in the terminal from the snippets folder to build the project.

```shell
./gradlew :exposed-modules-groovy-gradle:build
```

--------------------------------

### Initialize with Pre-constructed ConnectionFactoryOptions

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Working-with-ConnectionFactory.md

Uses a pre-built ConnectionFactoryOptions object to initialize R2dbcDatabaseConfig for later connection.

```kotlin
import io.r2dbc.spi.ConnectionFactoryOptions
import io.r2dbc.spi.IsolationLevel
import io.r2dbc.spi.Option
import org.jetbrains.exposed.v1.r2dbc.R2dbcDatabase
import org.jetbrains.exposed.v1.r2dbc.R2dbcDatabaseConfig

val options = ConnectionFactoryOptions.builder()
    .option(ConnectionFactoryOptions.DRIVER, "h2")
    .option(ConnectionFactoryOptions.PROTOCOL, "mem")
    .option(ConnectionFactoryOptions.DATABASE, "test")
    .option(Option.valueOf("DB_CLOSE_DELAY"), "-1")
    .build()

val databaseConfig = R2dbcDatabaseConfig {
    defaultMaxAttempts = 1
    defaultR2dbcIsolationLevel = IsolationLevel.READ_COMMITTED
    connectionFactoryOptions = options
}

val database = R2dbcDatabase.connect(databaseConfig = databaseConfig)
```

--------------------------------

### Run Spring Boot Application

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/snippets/exposed-spring/README.md

Execute the application using the Gradle wrapper.

```bash
./gradlew bootRun
```

--------------------------------

### JDBC Transaction Manager in Exposed 0.61.0

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Migration-Guide-1-0-0.md

Demonstrates how to connect to a database and obtain its transaction manager in Exposed 0.61.0. Shows manual implementation of a TransactionManager and how to register it.

```kotlin
import org.jetbrains.exposed.sql.Database
import org.jetbrains.exposed.sql.transactions.TransactionManager

// JDBC
val manager: TransactionManager = 
    Database.connect("jdbc:h2:mem:test", "org.h2.Driver")
        .transactionManager

Database.connect(
    url = "jdbc:h2:mem:test",
    driver = "org.h2.Driver",
    manager = { db: Database ->
        object : TransactionManager {
            // defaultIsolationLevel, defaultReadOnly, ...
            
            override fun newTransaction(
                isolation: Int,
                readOnly: Boolean,
                outerTransaction: Transaction?
            ): Transaction { /* ... */ }
    
            override fun currentOrNull(): Transaction? { /* ... */ }
    
            override fun bindTransactionToThread(transaction: Transaction?) { /* ... */ }
        }
    }
)

TransactionManager.registerManager(database, manager)

// Getting manager
val tm: TransactionManager? =
    TransactionManager.managerFor(database)
```

--------------------------------

### Connect to H2 Database from File (JDBC)

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Working-with-Database.md

Connect to an H2 database file using JDBC. Ensure the H2 driver is added as a dependency.

```kotlin
Database.connect("jdbc:h2:file:~/test", driver = "org.h2.Driver")
```

--------------------------------

### Build the Exposed DAO project

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/snippets/get-started-with-exposed-dao/README.md

Executes the Gradle build task for the project from the snippets directory.

```shell
./gradlew :get-started-with-exposed-dao:build
```

--------------------------------

### Connect to MySQL Database (JDBC)

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Working-with-Database.md

Connect to a MySQL database using JDBC. Ensure the MySQL JDBC driver is added as a dependency.

```kotlin
Database.connect("jdbc:mysql://localhost:3306/test", driver = "com.mysql.cj.jdbc.Driver", user = "user", password = "password")
```

--------------------------------

### Connect to MariaDB Database (JDBC)

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Working-with-Database.md

Connect to a MariaDB database using JDBC. Ensure the MariaDB JDBC driver is added as a dependency.

```kotlin
Database.connect("jdbc:mariadb://localhost:3306/test", driver = "org.mariadb.jdbc.Driver", user = "user", password = "password")
```

--------------------------------

### Connect to MariaDB Database (R2DBC)

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Working-with-Database.md

Connect to a MariaDB database using R2DBC. Ensure the MariaDB R2DBC driver is added as a dependency.

```kotlin
val database = Database.connect("r2dbc:mariadb://localhost:3306/test", MariaDBDialect, user = "user", password = "password", configuration = { it.isAutoCommit = false })
```

--------------------------------

### Prepare SQL for a Query (Full)

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/DSL-Statement-Builder.md

Generate a SQL string without parameter placeholders for a query by setting `prepared` to `false`. This requires a connection and transaction context.

```kotlin
val fullSql = filmQuery.prepareSQL(prepared = false)
```

--------------------------------

### Configure ConnectionFactoryOptions with URL

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Working-with-ConnectionFactory.md

Configures database connection using a URL combined with manual ConnectionFactoryOptions builder.

```kotlin
import io.r2dbc.spi.ConnectionFactoryOptions
import io.r2dbc.spi.IsolationLevel
import io.r2dbc.spi.Option
import org.jetbrains.exposed.v1.r2dbc.R2dbcDatabase
import org.jetbrains.exposed.v1.r2dbc.R2dbcDatabaseConfig

val database = R2dbcDatabase.connect(
    url = "r2dbc:h2:mem:///test;",
    databaseConfig = R2dbcDatabaseConfig {
        defaultMaxAttempts = 1
        defaultR2dbcIsolationLevel = IsolationLevel.READ_COMMITTED

        connectionFactoryOptions {
            option(Option.valueOf("DB_CLOSE_DELAY"), "-1")
        }
    }
)
```

--------------------------------

### Call custom SQL extension

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Working-with-SQL-Strings.md

Demonstrates usage of the custom execAndMap extension function.

```kotlin
transaction {
    val names = execAndMap("SELECT name FROM users") { rs ->
        generateSequence { if (rs.next()) rs.getString("name") else null }.toList()
    }
}
```

--------------------------------

### Run Exposed Gradle Plugin Application

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/snippets/exposed-gradle-plugin/README.md

This command runs the application that utilizes the Exposed Gradle plugin. Make sure to execute it from the `snippets` folder.

```shell
./gradlew :exposed-gradle-plugin:run
```

--------------------------------

### Connect to H2 Database from File (R2DBC)

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Working-with-Database.md

Connect to an H2 database file using R2DBC. Ensure the H2 R2DBC driver is added as a dependency.

```kotlin
val database = Database.connect("r2dbc:h2:file:~/test", R2DBCDialect, configuration = { it.isAutoCommit = false })
```

--------------------------------

### Functions and Group By

Source: https://github.com/jetbrains/exposed/blob/main/README.md

Demonstrates using aggregate functions (COUNT) with GROUP BY to count users per city. This is useful for data aggregation.

```SQL
SELECT CITIES."name", COUNT(USERS.ID) FROM CITIES INNER JOIN USERS ON CITIES.ID = USERS.CITY_ID GROUP BY CITIES."name"
```

--------------------------------

### Connect to MySQL Database (R2DBC)

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Working-with-Database.md

Connect to a MySQL database using R2DBC. Ensure the MySQL R2DBC driver is added as a dependency.

```kotlin
val database = Database.connect("r2dbc:mysql://localhost:3306/test", MySqlDialect, user = "user", password = "password", configuration = { it.isAutoCommit = false })
```

--------------------------------

### Build Exposed JDBC Application

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/snippets/exposed-databases-jdbc/README.md

Build the Exposed JDBC application using Gradle. Navigate to the 'snippets' folder before running.

```shell
./gradlew :exposed-databases-jdbc:build
```

--------------------------------

### Prepare SQL for a Query (Parameterized)

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/DSL-Statement-Builder.md

Generate a parameterized SQL string representation of a query using `.prepareSQL()`. This requires a connection and transaction context.

```kotlin
val sql = filmQuery.prepareSQL()
```

--------------------------------

### Run Exposed JDBC Application

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/snippets/exposed-databases-jdbc/README.md

Run the Exposed JDBC application using Gradle. Navigate to the 'snippets' folder before running.

```shell
./gradlew :exposed-databases-jdbc:run
```

--------------------------------

### Configure MySQL Database Connection

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/snippets/exposed-dsl/README.md

Connect to a MySQL database using JDBC. Ensure your MySQL server is running and the database exists.

```kotlin
val mysqlDb = Database.connect(
        "jdbc:mysql://localhost:3306/test",
        driver = "com.mysql.cj.jdbc.Driver",
        user = "root",
        password = "password"
    )
```

--------------------------------

### Verify Default Docker Context

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Contributing.md

List available Docker contexts to ensure that the default context is correctly set.

```shell
docker context list
```

--------------------------------

### Configure HikariCP for MySQL

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Working-with-DataSource.md

Set up HikariConfig and include necessary dependencies for MySQL connection pooling.

```kotlin
val config = HikariConfig().apply {
    jdbcUrl = "jdbc:mysql://localhost/dbname"
    driverClassName = "com.mysql.cj.jdbc.Driver"
    username = "username"
    password = "password"
    maximumPoolSize = 6
    // as of version 0.46.0, if these options are set here, they do not need to be duplicated in DatabaseConfig
    isReadOnly = false
    transactionIsolation = "TRANSACTION_SERIALIZABLE"
}

// Gradle
implementation "mysql:mysql-connector-java:8.0.33"
implementation "com.zaxxer:HikariCP:4.0.3"
```

--------------------------------

### Prepare SQL for an Insert Statement (Full)

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/DSL-Statement-Builder.md

Generate a SQL string without parameter placeholders for an insert statement by setting `prepared` to `false`. This requires a connection and transaction context.

```kotlin
val fullSql = insertFilm.prepareSQL(prepared = false)
```

--------------------------------

### Generate Migration Scripts

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Exposed-gradle-plugin.md

Execute the `generateMigrations` task to create SQL migration scripts based on schema differences.

```bash
./gradlew generateMigrations
```

--------------------------------

### Connect to Database using DataSource

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Working-with-DataSource.md

Pass a javax.sql.DataSource instance to Database.connect() to enable connection pooling and custom configuration.

```kotlin
val db = Database.connect(dataSource)
```

--------------------------------

### Chain and Combine SQL Functions

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/SQL-Functions.md

Illustrates chaining and combining SQL functions, such as concatenating column values and then applying TRIM and LOWER.

```kotlin
val concatenated = concat(FilmBoxOfficeTable.film, " (", FilmBoxOfficeTable.year, ")")
    FilmBoxOfficeTable.select {
        lowerCase(trim(concatenated))
    }
```

--------------------------------

### Generate Migration Script with Gradle

Source: https://github.com/jetbrains/exposed/blob/main/samples/exposed-migration/README.md

Execute this Gradle task from your repository's root directory to generate a SQL migration script. The script will be placed in the 'migrations' directory.

```bash
./gradlew generateMigrationScript
```

--------------------------------

### Configure Maven Central Repository (Gradle)

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/Adding-dependencies.md

Add this to your build.gradle file to ensure Maven Central is available for dependency resolution.

```kotlin
repositories {
    mavenCentral()
}
```

--------------------------------

### Update and Delete SQL Statements

Source: https://github.com/jetbrains/exposed/blob/main/README.md

Demonstrates basic UPDATE and DELETE operations on the USERS table. Ensure the table exists before executing.

```SQL
UPDATE USERS SET "name"='Alexey' WHERE USERS.ID = 'alex'
```

```SQL
DELETE FROM USERS WHERE USERS."name" LIKE '%thing'
```

--------------------------------

### Project Structure Overview

Source: https://github.com/jetbrains/exposed/blob/main/samples/exposed-ktor-r2dbc/README.md

This is a file tree representation of the Exposed-Ktor-R2DBC project. It shows the organization of Kotlin code across different domains (User, Project, Issue, Comment) and Ktor plugin configurations.

```plaintext
src/
├── main/
│   └── kotlin/
│       ├── domain/
│       │   ├── comment/
│       │   └── issue/
│       │       ├── Issue.kt            # Issue data class
│       │       ├── IssueRepository.kt  # Exposed database operations
│       │       ├── IssueRoutes.kt      # Ktor route handler
│       │       ├── IssueService.kt     # Service level
│       │       └── IssuesTable.kt      # Exposed table object, convertors
│       │   ├── project/
│       │   ├── user/
│       │   └── BaseRepository.kt
│       ├── plugins/                    # Ktor plugin configuration
│       │   ├── Database.kt             # Exposed database connection, schema setup
│       │   ├── Monitoring.kt
│       │   ├── Routing.kt
│       │   └── Serialization.kt
└──     └── Application.kt              # Application entry point
```

--------------------------------

### Generate Exposed Migration Script

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/snippets/exposed-gradle-plugin/README.md

Execute this command to generate database migration scripts using the Exposed Gradle plugin. Ensure you are in the `snippets` directory.

```shell
./gradlew :exposed-gradle-plugin:generateMigrations
```

--------------------------------

### Prepare SQL for an Insert Statement (Parameterized)

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/DSL-Statement-Builder.md

Generate a parameterized SQL string for an insert statement using `.prepareSQL()`. This requires a connection and transaction context.

```kotlin
val preparedSql = insertFilm.prepareSQL()
```

--------------------------------

### Aggregate Sum and Count

Source: https://github.com/jetbrains/exposed/blob/main/documentation-website/Writerside/topics/SQL-Functions.md

Demonstrates the direct use of SUM() and COUNT() SQL functions with column expressions for aggregation.

```kotlin
val sumIncome = sum(FilmBoxOfficeTable.income)
    val countFilms = count(FilmBoxOfficeTable.film)

    FilmBoxOfficeTable.slice(sumIncome, countFilms).selectAll().firstOrNull()
```