Getting to know SQL for applications

1. Why you need a relational DB

Honestly, the first weeks of learning programming are very cozy: we have a MutableList, we add elements to it — and life is great. But only until you close the program. Then the list... magically disappears, like motivation on Friday at 18:59. This is where a database shows up: it stores data so it lives longer than a single app session, and so that data correctness rules are enforced not “on someone’s honor”, but systematically.

A relational database (RDBMS/relational DB) is a way to store data as tables that are connected by clear rules. “Relational” — because relationships (relations) between data sets matter. In a learning context it looks a bit like Excel, but with a superpower: tables can protect data from chaos, maintain relationships, and run queries that “scan” millions of rows faster than you can say “can I just loop through it?”.

So we don’t get confused later, let’s lock in the basic terms.

Mini glossary: table, row, column, schema

Here’s an important idea: a table is not a row. A table is a collection of records of one type. A row is one record. Columns are the fields of a record. And all of this gets packaged into the concept of a “schema”, i.e., an agreement about the structure of the data. If you’ve ever argued on a team like “can the email field be empty or not?” — the schema exists precisely so the argument ends (usually with common sense winning).

2. Schema as a data contract

When an app stores data in lists, all correctness is on your code: forget a check — you get garbage. In a database, you can “move” part of these checks into the schema. This is very similar to good API design: you don’t hope that the caller of your function will always pass the correct value — you define a contract. In exactly the same way, a database must know what counts as a valid value in a table.

A schema describes: which tables exist, which columns they have, which data types, and which integrity constraints apply. In SQL, a schema is usually created with commands like CREATE TABLE ....

In this lecture we are not setting up a specific DBMS (PostgreSQL/SQLite/MySQL) and we are not starting a server. We are building a model so that later JDBC and Exposed won’t look like “magic spells”. Therefore the examples will be SQL strings in Kotlin.

Example: users table

Even if our app is about expenses, it’s very useful to start with something simple: users with id, email, name. This is a concentrated set of all key constraints: primary key, uniqueness, and a ban on NULL.

fun main() {
    val createUsers = """
        CREATE TABLE users (
            id INTEGER PRIMARY KEY,
            email TEXT UNIQUE NOT NULL,
            name TEXT NOT NULL
        )
    """.trimIndent()

    println(createUsers)
    // CREATE TABLE users ( ... )
}

Pay attention to the meaning, not the specific types (INTEGER, TEXT) — different DBMSs vary in the details. What matters is that this is a contract: email is unique and required, name is required, and id is the main identifier of a row.

3. Keys and integrity constraints

In programming, you often want everything to be flexible: “let the email be empty”, “let there be two identical records”, “I’ll clean it up later”. A database is that strict teacher who says: “first correct data — then you pass.” And while that can be annoying sometimes, in real projects it saves you from very expensive mistakes.

Integrity constraints come in different kinds, but today we need four basic ones: PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY.

Primary Key (PK): the row’s “passport”

A primary key is a column (or set of columns) that uniquely identifies a row. In application code, PK exists so you can confidently say: “update this record” or “delete this record”, without risking hitting extra rows.

If there is no PK, you start doing DELETE WHERE name = 'Coffee', and suddenly you delete all coffee in the history of humanity. And then you say, “why don’t people like programmers?”.

NOT NULL: “a value must exist”

NOT NULL forbids a missing value. That is, the column must have a value in every row. Important: NULL is not an empty string and not zero. It means “value unknown/absent”.

If an expense has no amount — that’s a weird expense. So the amount is almost always NOT NULL.

UNIQUE: “no duplicates allowed”

UNIQUE protects you from duplicates in a meaningful sense. For example, an email should usually be unique. You can try to enforce this only in code, but in a distributed world your code is not always the only entry point (migrations, admin scripts, integrations). The database is the last line of defense.

Foreign Key (FK): “a reference to another table”

A foreign key defines a relationship between tables: for example, expenses.category_id references categories.id. FK is needed so “dangling references” don’t appear: an expense pointing to a category that doesn’t exist.

4. Tables and relationships using Expense Tracker as an example

Now we’ll take an important step: translate familiar objects from code into a tabular structure. Let’s imagine our practical project as a CLI app Expense Tracker that stores expenses with an amount, a description, and a category.

In memory we could store it like this:

• a category as a string "food" directly inside the expense,
• or a category as a separate object.

In the relational model, this usually turns into two tables: categories and expenses. The category is a separate entity, and the expense stores a reference to it.

To make the idea stick visually, here is the relationship diagram (not SQL, just a “map”):

flowchart LR
    C[categories] -->|id = PK| E[expenses]
    E -->|category_id = FK -> categories.id| C

Yes, the arrows are conceptually bidirectional: a category “has many expenses”, and an expense “belongs to one category”.

SQL description of tables: categories and expenses

Let’s show the schema as Kotlin strings.

fun main() {
    val createCategories = """
        CREATE TABLE categories (
            id INTEGER PRIMARY KEY,
            name TEXT UNIQUE NOT NULL
        )
    """.trimIndent()

    val createExpenses = """
        CREATE TABLE expenses (
            id INTEGER PRIMARY KEY,
            amount_cents INTEGER NOT NULL,
            description TEXT NOT NULL,
            category_id INTEGER NOT NULL,
            FOREIGN KEY (category_id) REFERENCES categories(id)
        )
    """.trimIndent()

    println(createCategories)
    println(createExpenses)
}

There are several practical choices here.

We use amount_cents as an INTEGER, not a DOUBLE. This is a common technique for money: store the amount in whole “smallest units” (cents) to avoid binary floating-point surprises. In future lectures on JDBC/Exposed we’ll appreciate this even more, because “money” bugs are the kind that very quickly turn a programmer into a philosopher.

5. Minimal SQL for CRUD

If you simplify a lot, most applications around us do the same thing: create records, read records, update records, and delete records. This is called CRUD (Create, Read, Update, Delete). In SQL it corresponds to four basic operations: INSERT, SELECT, UPDATE, DELETE.

Our goal now is not to learn “all SQL in the world”, but to learn to recognize these commands in code and write them in a way that is safe and predictable.

INSERT: add a new row

INSERT creates a record. Usually you list the columns and values.

In real JDBC code, we will almost always use parameters ? (more on that below), but for now let’s just lock in the pattern.

fun main() {
    val insertCategorySql = "INSERT INTO categories(name) VALUES (?)"
    val insertExpenseSql = """
        INSERT INTO expenses(amount_cents, description, category_id)
        VALUES (?, ?, ?)
    """.trimIndent()

    println(insertCategorySql) // INSERT INTO categories(name) VALUES (?)
    println(insertExpenseSql)  // INSERT INTO expenses(...) VALUES (?, ?, ?)
}

SELECT: read data

SELECT is a “query”. In the minimal version: which columns we want to get and from which table.

fun main() {
    val selectAllCategoriesSql = "SELECT id, name FROM categories"
    val selectExpenseByIdSql = "SELECT id, amount_cents, description, category_id FROM expenses WHERE id = ?"

    println(selectAllCategoriesSql) // SELECT id, name FROM categories
    println(selectExpenseByIdSql)   // SELECT ... FROM expenses WHERE id = ?
}

WHERE id = ? is the key part: we select a specific record by primary key.

UPDATE: change a record

UPDATE almost always must have a WHERE. Without WHERE you change every row in the table. Sometimes that’s needed in migrations, but in application code it’s usually a catastrophe, not a feature.

fun main() {
    val updateExpenseDescriptionSql = """
        UPDATE expenses
        SET description = ?
        WHERE id = ?
    """.trimIndent()

    println(updateExpenseDescriptionSql)
}

DELETE: remove a record

With DELETE it’s the same story: without WHERE you wipe the entire table.

fun main() {
    val deleteExpenseSql = "DELETE FROM expenses WHERE id = ?"
    println(deleteExpenseSql) // DELETE FROM expenses WHERE id = ?
}

6. SELECT and query safety

How to read SELECT queries

When you start writing queries, you often hear in your head: “I just want a list of today’s expenses.” And then it begins: SELECT ... WHERE ... ORDER BY ... LIMIT ... — and it feels like a spell. In reality, it’s a very logical chain of steps, just written down as text.

SELECT has a few very practical elements that you’ll see constantly.

WHERE: a filter “keep only what fits”

WHERE sets the selection condition. It’s like filter for a collection, but it runs inside the database (and is usually more efficient). It helps to remember your “collection mindset”: we’ve already gone through transformations and filtering of collections in Kotlin.
SQL does something similar, just on the storage side.

ORDER BY: sorting

Sorting is especially common in lists: “show the latest expenses.” In SQL that’s ORDER BY created_at DESC (if you have date/time), or ORDER BY id DESC (a very rough, but sometimes acceptable approximation).

LIMIT: “give me the first N”

LIMIT limits the number of rows in the response. This is useful both for UX and for performance.

Let’s put it together into one example: a query for “the latest 10 expenses” (without dates, so we don’t get ahead of ourselves):

fun main() {
    val recentExpensesSql = """
        SELECT id, amount_cents, description, category_id
        FROM expenses
        ORDER BY id DESC
        LIMIT 10
    """.trimIndent()

    println(recentExpensesSql)
}

Parameterized queries (?)

Now comes a principle that sounds boring but saves projects: an SQL query must be fixed text, and the data must be passed separately as parameters.

In SQL, a parameter is denoted by ? (in the JDBC world). We won’t write JDBC code today — that’s the next lecture. But we must already build the habit: don’t “paste” values into the query string.

Why this matters.

• First, it reduces the risk of errors with quotes and escaping. Strings with apostrophes, spaces, weird characters — the database can handle all of that correctly if you pass values as parameters.
• Second, it protects against SQL injection: situations where user input turns into part of SQL code. Even if your app is “not about security”, it’s better to write it the right way immediately so you don’t have to unlearn later.

Let’s compare two approaches at the Kotlin-string level.

fun main() {
    val email = "bob@example.com"

    val unsafe = "SELECT id, email, name FROM users WHERE email = '$email'"
    val safe = "SELECT id, email, name FROM users WHERE email = ?"

    println(unsafe) // SELECT ... WHERE email = 'bob@example.com'
    println(safe)   // SELECT ... WHERE email = ?
}

The second option looks “incomplete” until you know JDBC. But that is exactly the correct style: the query is a template, and the value is a separate entity.

What to store as a string vs. as a reference

When you design tables, a typical question comes up: “Can I just store the category as a string in expenses.category? Why do I need a separate table?”

Sometimes you can. But more often a separate table is better, because: if the category is separate, you avoid typos (Food vs food vs fod), you can rename a category in one place, you can store additional fields (icon, order, color), and you get FK enforcement.

To keep this from being abstract, let’s imagine two designs.

Option A: category as a string in expenses

fun main() {
    val createExpensesFlat = """
        CREATE TABLE expenses (
            id INTEGER PRIMARY KEY,
            amount_cents INTEGER NOT NULL,
            description TEXT NOT NULL,
            category TEXT NOT NULL
        )
    """.trimIndent()

    println(createExpensesFlat)
}

This is simpler, faster to start with, fewer tables. But over the long run, data quality problems almost always surface.

Option B: separate table + FK

We already showed this above, but now the meaning is clearer: category is a separate entity.

fun main() {
    val createCategories = """
        CREATE TABLE categories (
            id INTEGER PRIMARY KEY,
            name TEXT UNIQUE NOT NULL
        )
    """.trimIndent()

    val createExpenses = """
        CREATE TABLE expenses (
            id INTEGER PRIMARY KEY,
            amount_cents INTEGER NOT NULL,
            description TEXT NOT NULL,
            category_id INTEGER NOT NULL,
            FOREIGN KEY (category_id) REFERENCES categories(id)
        )
    """.trimIndent()

    println(createCategories)
    println(createExpenses)
}

Here the app is a bit more complex: to add an expense, you need to know category_id. But later it becomes more convenient: we can store categories in a reference table and link to them correctly.

A layer of SQL templates

Now we’ll do a small but important piece of “practical” design: put our SQL queries in one place, so later (in JDBC and Exposed) we don’t smear strings all over the project. This is not yet DAO or Repository in the full sense, but it’s already discipline: queries are together, names are consistent, formatting is unified.

Let’s make an ExpenseSql object that contains only strings. No magic.

object ExpenseSql {
    val createCategories = """
        CREATE TABLE categories (
            id INTEGER PRIMARY KEY,
            name TEXT UNIQUE NOT NULL
        )
    """.trimIndent()

    val createExpenses = """
        CREATE TABLE expenses (
            id INTEGER PRIMARY KEY,
            amount_cents INTEGER NOT NULL,
            description TEXT NOT NULL,
            category_id INTEGER NOT NULL,
            FOREIGN KEY (category_id) REFERENCES categories(id)
        )
    """.trimIndent()
}

And let’s add CRUD templates. Notice: everywhere data comes from outside, we use ?.

object ExpenseSql {
    val insertCategory = "INSERT INTO categories(name) VALUES (?)"
    val findCategoryByName = "SELECT id, name FROM categories WHERE name = ?"

    val insertExpense = """
        INSERT INTO expenses(amount_cents, description, category_id)
        VALUES (?, ?, ?)
    """.trimIndent()

    val listExpenses = """
        SELECT id, amount_cents, description, category_id
        FROM expenses
        ORDER BY id DESC
        LIMIT ?
    """.trimIndent()

    val deleteExpenseById = "DELETE FROM expenses WHERE id = ?"
}

For now this is “just strings”. But in the next lecture these strings will become arguments to PreparedStatement, and you’ll see how “fixed SQL + parameters” turns into real working code without concatenation and without surprises.

7. Common mistakes

Mistake #1: confusing a table and a row, designing the schema “by eye”.
A beginner often says “I have an expense table”, meaning one record, and starts adding fields as if the table were an object. A useful habit: every time, say out loud “a table is many rows” and “a row is one expense/one user”. Then designing becomes much calmer.

Mistake #2: doing UPDATE or DELETE without WHERE.
This is a classic that happens even to experienced developers — simply because the query looks “logical” until you realize the scale. Without WHERE, you change or delete all rows. In application code the rule is simple: if this is not a deliberate bulk operation (and you are not ready for the consequences), then WHERE is mandatory, usually by id.

Mistake #3: keeping important constraints only in code, ignoring NOT NULL and UNIQUE.
At first it seems like: “but I validate in Kotlin, why in the DB?” Then data import appears, migration, a manual admin script, or a concurrent run — and suddenly the database contains garbage. DB constraints are insurance against the real world, where not all access goes through your ideal code.

Mistake #4: “pasting” values into SQL via concatenation.
Even if you are not thinking about security yet, concatenation breaks on quotes, spaces, and special characters. And if you do think about security, it opens the door to SQL injection. The right habit forms early: query is fixed text, values are parameters (?). In the next lecture we’ll see how this is done technically via JDBC.

Mistake #5: trying to “get by without keys” because “we’ll find it by fields anyway”.
Sometimes you don’t want to introduce an id and you update by “natural” fields (name, description). This quickly leads to ambiguity and weird bugs: a field changed — and you can no longer find the record, or you found the wrong one. A primary key is not bureaucracy; it’s the foundation of addressing rows.

Mistake #6: making relationships “by meaning”, but not enforcing them with FOREIGN KEY.
You can store categoryId as a number and “believe” it exists in categories. But then you’ll get expenses with category_id = 9999, and you’ll spend a long time debugging who did it and when. FK is a way to tell the database: “don’t allow such states,” and let it be your strict guard at the data entry point.