Database

You have been practicing with SQLite. It is great for learning. But for real projects, you need a real database. PostgreSQL (often called “Postgres”) is the most popular open-source relational database. It is used by companies of all sizes, from startups to enterprises. In this article, you will set it up, connect to it, and learn its unique features. In the previous article, you learned about indexes and performance. Now you will put everything together with a production-ready database. ...

Your queries work. But they are slow. A simple SELECT takes seconds instead of milliseconds. On a table with millions of rows, it could take minutes. The fix is almost always the same: add an index. In the previous article, you learned about window functions. Now you will learn how to make all your queries run fast. What Is an Index? Think of a book’s index at the back. If you want to find “window functions” in a 500-page book, you have two options: ...

GROUP BY is great for summaries. But it collapses your rows. You get one row per group. What if you want the summary and the individual rows at the same time? That is what window functions do. They calculate across rows without removing any of them. In the previous article, you learned about aggregation and subqueries. Window functions are the next level. What Are Window Functions? A window function performs a calculation across a set of rows that are related to the current row. This set of rows is called a window. ...

You know how to get individual rows. But what if you need answers like “How many books did we sell?” or “What is the average book price?” That is where aggregation comes in. In the previous article, you learned how to combine tables with JOINs. Now you will learn how to summarize data across many rows into a single answer. Aggregate Functions Aggregate functions take many rows and return a single value. ...

So far, you have worked with one table at a time. But real databases have many tables, and the interesting answers come from combining them. In the previous article, you learned how to add and modify data. Now you will learn how to pull data from multiple tables at once using JOINs. Why Tables Are Related In our bookstore database, we have separate tables for books and authors. Why not put everything in one table? ...

In the previous article, you learned how to read data with SELECT. But a database is not useful if you cannot add, change, or remove data. This article covers the three commands that modify data: INSERT, UPDATE, and DELETE. You will also learn about transactions — a way to make sure your changes are safe. Our Sample Database We continue with the same online bookstore database. If you need to set it up, copy the CREATE TABLE and INSERT statements from the first article. ...

You have data. You need to get answers from it. SQL is the language that does this. Every app you use — social media, banking, shopping — stores data in a database. SQL is how developers read, filter, and organize that data. It has been around since the 1970s, and it is still the most important skill for working with data. What Is SQL? SQL stands for Structured Query Language. It is a language for talking to databases. You write a query, and the database gives you the answer. ...

In the previous tutorials, we used SchemaUtils.create() to create database tables. This works for development, but it has a big problem: it cannot handle schema changes. What happens when you need to add a column? Rename a table? Change a data type? You cannot just drop the database and recreate it — production data would be lost. This is where database migrations come in. Why Migrations Matter Without migrations, you have these problems: ...

A real application has connected data. Users own notes. Notes have tags. Orders belong to customers. These connections are called relationships. In this tutorial, you will add relationships between tables, write JOIN queries, and build advanced filtering, sorting, and pagination. Types of Relationships Type Example Implementation One-to-Many One user has many notes Foreign key on notes table Many-to-Many Notes have many tags, tags belong to many notes Join table (note_tags) One-to-One One user has one profile Foreign key with unique constraint One-to-Many: Users → Notes A user can have many notes. Each note belongs to one user (or no user). ...

Our API works, but the data lives in memory. Restart the server and everything is gone. Time to add a real database. In this tutorial, you will connect your Ktor API to a database using Exposed — JetBrains’ SQL library for Kotlin. We will use H2 for development and explain how to switch to PostgreSQL for production. What is Exposed? Exposed is a SQL library made by JetBrains. It gives you two ways to work with databases: ...