Add simple, self-contained SQL/DB examples for Kotlin DataFrame
- Dominant language
- Kotlin
- Stars
- 1.1k
- Forks
- 83
- Avg merge
- 4d 12h
- Merged PRs (30d)
- 30
Description
## Motivation
Today the **Database + Kotlin DataFrame (+ Kandy)** integration is only demonstrated by the external
[KotlinDataFrame-SQL-Examples](https://github.com/zaleslaw/KotlinDataFrame-SQL-Examples) project. Problems:
- its notebooks are mostly deprecated;
- the flagship example (IMDB) needs a **>100 MB MariaDB dump downloaded from Google Drive** — the barrier to
entry is so high that basically nobody actually runs it.
We want a set of **small, self-contained examples inside the `dataframe` repository**: no external dumps, no
mandatory Docker where avoidable, so that "clone → run → see a DataFrame from a database" just works.
## What already exists (foundation to reuse)
- The **`dataframe-jdbc`** module supports H2, SQLite, PostgreSQL, MySQL, MariaDB, MS SQL, DuckDB.
Entry points in `dataframe-jdbc/src/main/kotlin/.../io/readJdbc.kt`:
`readSqlTable`, `readSqlQuery`, `readResultSet`, `readAllSqlTables`,
`Connection.readDataFrame(...)`, `DbConnectionConfig.readDataFrame(...)`, `DataSource.readDataFrame(...)`.
- **"Postgres inside H2" is built in**: `H2(mode = Mode.PostgreSql)` (`.../io/db/H2.kt`, `MODE=PostgreSQL`) reads
with PostgreSQL logic. Reference tests: `dataframe-jdbc/src/test/kotlin/.../io/h2/`.
- **Testcontainers** will be wired up soon: `testcontainersTest`/`localDbTest` tasks in `dataframe-jdbc/build.gradle.kts`,
image versions in `gradle/libs.versions.toml` (postgres `18-alpine`, mysql `9.7`, mariadb `12.3.2`).
- **Examples** are standalone projects under `examples/projects//`; all are automatically compiled during
`:test` (debug mode) via `build-logic/.../dfbuild.buildExampleProjects.gradle.kts`. Related existing ones:
`exposed`, `hibernate` (ORM↔DataFrame); CSV+Kandy in `kotlin-dataframe-plugin-gradle-example`.
- Backend guide: `docs/StardustDocs/topics/guides/Guide-for-backend-SQL-developers.md`.
## Scope — new examples
Each example is a new folder under `examples/projects/`: a standalone Gradle project with its own
`settings.gradle.kts`, a tiny embedded/"fun" dataset, a `README.md`, and a runnable `main()`. DB drivers are
runtime dependencies. A companion Jupyter notebook may be added per example where it works cleanly, but the
Gradle project is the primary, CI-tested artifact.
- [ ] **`sql-csv-join`** — join a CSV file with a database table.
`readCsv()` + `readSqlTable(...)` from H2/SQLite → `join` + `groupBy`/aggregation.
Shows DataFrame as a bridge between data sources. **Includes a Kandy chart of the result.**
- [ ] **`sql-sqlite`** — read from a bundled `.sqlite` file in resources. Zero setup.
`Connection.readDataFrame("SELECT ...")` / `readSqlTable`. Small themed dataset.
- [ ] **`sql-postgres`** — one database, two ways to run it:
- zero-Docker: `H2(Mode.PostgreSql)` with `MODE=PostgreSQL`, schema + data, `readSqlQuery`/`readResultSet`;
- optional "real PostgreSQL" via **Testcontainers** (image already in the project) as a separate `main`/README section.
**Includes a Kandy chart.**
- [ ] **`sql-ktor`** — a minimal [Ktor](https://ktor.io/) server that reads a database into a `DataFrame` on
request and serves it over HTTP (JSON via `dataframe-json` `toJson`, and/or a small HTML page). Uses embedded H2
so it runs with no external setup.
**Out of scope:** a Spring Boot + full schema-migration example (too heavy — pulls in Spring, which the repo has
zero of today, plus a migration tool). Track it separately if wanted.
## Datasets
Two viable sources: **reuse datasets already bundled in the repo**, or **generate a tiny dataset in code**.
Rule of thumb:
- **Read-only "read from a real DB" demos → reuse a ready-made, recognizable dataset.** The repo already ships
the **Chinook** SQLite DB at `examples/projects/exposed/src/main/resources/chinook.db` (a digital-music store:
`artists`, `albums`, `tracks`, `genres`, `invoices`, `customers` — multiple joinable tables, well known,
~1 MB, no download). Best default.
- **Demos that must own their schema/seeding (Postgres types, Testcontainers) → generate a small themed dataset
in code** via inline `CREATE TABLE` + `INSERT` (idempotent, identical for H2-in-postgres-mode and a real
container, no binary blob to check in). Keep it small (~20–50 rows) and "fun". Pattern to copy:
`createPostgresTestData` in `dataframe-jdbc/src/test/kotlin/.../io/postgresTestBase.kt`.
Per example:
- **`sql-sqlite`** → **reuse `chinook.db`** as-is. Zero generation; run a query (e.g. top-selling genres, tracks
per album) via `Connection.readDataFrame("SELECT ...")`. Strongest ready-made fit.
- **`sql-csv-join`** → combine a **real DB table (Chinook `artists`/`albums`)** with a **small hand-authored CSV**
that joins on a key (e.g. `artist,country,formedYear`). Generate the tiny CSV side by hand so the join key and
result are obvious. Alternative fully-ready combo: `movies.csv` (already in the `movies` example) + a generated
in-memory ratings table.
- **`sql-postgres`** → **generate** a small themed schema+rows in code (inline SQL). The same seeding runs against
`H2(Mode.PostgreSql)` and against the Testcontainers Postgres — no file dependency — and lets us show a couple
of Postgres-flavored types/queries deliberately.
- **`sql-ktor`** → **generate** and seed an embedded H2 at startup (so the app is fully self-contained), or reuse
`chinook.db` read-only. Serve the resulting `DataFrame` as JSON via `dataframe-json` `toJson`.
Notes: Chinook is a public, redistributable sample DB and is already committed, so no licensing/size concern.
Avoid the test-only `safe_moz_places_sample.sqlite` (Firefox history — not illustrative) except for smoke tests.
## Implementation
1. For each example create `examples/projects//` following existing ones
(`examples/projects/exposed/`, `.../kotlin-dataframe-plugin-gradle-example/`): own `settings.gradle.kts`
(`rootProject.name`), `build.gradle.kts`, `gradle/libs.versions.toml`, `src/main/kotlin`, resources, `README.md`.
2. Mirror each example into `examples/projects/dev//` (dev mirror targeting the master library version).
3. In `build-logic/.../dfbuild.buildExampleProjects.gradle.kts` add the new libraries (ktor, postgres/sqlite
drivers, testcontainers; kandy already present) to `versionsToSync` and `exampleDependencyUpdates` so version
sync and `dependencyUpdates` are aware of them. Add new versions to the root `gradle/libs.versions.toml`.
4. Update `examples/README.md`: a section for the SQL/DB examples with links; mark the old
`KotlinDataFrame-SQL-Examples` as "advanced / large dataset".
5. (Optional) add companion notebooks under `examples/notebooks//` where they run cleanly, and cross-link
from `docs/.../Guide-for-backend-SQL-developers.md`.
## Acceptance criteria
- `./gradlew runBuildAllExampleFolders -Pkotlin.dataframe.debug=true` compiles all new examples (release + dev).
- Each example runs locally with no external data download; the Testcontainers variant runs only when Docker is present.
- Every example has a `README.md` explaining what it shows and how to run it.
- `examples/README.md` is updated.
Contributor guide
Assessment
This issue has not been assessed yet.