Lectures

We break down the basic “mental model” of relational databases: tables, rows, columns, and the schema as a data contract. We learn to see the foundation of CRUD operations in SQL ( INSERT, SELECT, UPDATE, DELETE), to understand integrity constraints ( PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY), and we also build a correct habit: fixed query + parameters ( ?) instead of string concatenation. All examples are shown as Kotlin strings with trimIndent() for future work with JDBC and Exposed.

We break down JDBC’s “three nested dolls”: Connection (a channel to the DB), PreparedStatement (a prepared SQL with parameters ?), and ResultSet (a cursor over rows). Using Kotlin examples, we learn how to safely run SELECT/ INSERT/ UPDATE/ DELETE, map results into a data class, and avoid resource leaks via use().

We break down why JDBC needs transactions and where their boundaries lie: how autoCommit affects persisting changes, why multiple SQL operations must live in a single transaction, and how to correctly build a try/ catch/ finally template for guaranteed commit() and rollback(). In practice, we build a convenient inTx wrapper, tie expense insertion to auditing, discuss money transfers as a “double UPDATE,” and common mistakes (early commit, forgotten rollback, overly broad transactions).

We break down the Exposed library from the SQL DSL perspective: how to add the dependencies and call Database.connect, describe a table via LongIdTable, work within transaction { }, and implement CRUD operations via insert, select/ selectAll, update, deleteWhere. At the end — how to “hide” Exposed behind a repository interface and which mistakes beginners make most often.

We break down why JDBC remains blocking even inside coroutines, how dispatchers differ, and why you should move queries into withContext( Dispatchers . IO). We lock in the architectural rule “IO lives in the storage layer”, handle transactions carefully (don’t break the context), build a minimal Expense Tracker on JDBC, and discuss Exposed under coroutines. In the end — an honest error model and a list of common pitfalls.