Connect and retrieve data from databases

Kotlin DataFrame library provides support for the most common SQL databases:

Explore the Kotlin DataFrame SQL Examples on GitHub.

Before you start

To follow this tutorial:

Select File | New | Kotlin Notebook.
Add the Java Database Connectivity (JDBC) driver dependency for your database in the first cell of your notebook.
For example, to connect to a MariaDB database, add:
USE { dependencies("org.mariadb.jdbc:mariadb-java-client:$version") }
Import Kotlin DataFrame:
%use dataframe
Run the code cell with the %use dataframe line before any other code cells to make sure the DataFrame library and its APIs are available in the notebook.

To follow the tutorial, you can also use DataFrame as a Gradle or Maven dependency.

Connect to a database

To connect to a database, create a connection configuration using the DbConnectionConfig() function:

Import the following functionality:
import org.jetbrains.kotlinx.dataframe.io.DbConnectionConfig import org.jetbrains.kotlinx.dataframe.schema.DataFrameSchema
Define connection parameters (URL, username, password) using the DbConnectionConfig() function:
val URL = "YOUR_URL" val USER_NAME = "YOUR_USERNAME" val PASSWORD = "YOUR_PASSWORD" val dbConfig = DbConnectionConfig(URL, USER_NAME, PASSWORD)

Inspect database schema

Before loading the data, inspect the database schemas to understand what tables you have and what columns they contain. You can use the schemas to decide which table to load into a DataFrame.

To retrieve schemas for all user-created tables in your database, use the DataFrameSchema.readAllSqlTables() function:

val dataSchemas = DataFrameSchema.readAllSqlTables(dbConfig)

dataSchemas.forEach { (tableName, schema) ->
    println("---Schema for table: $tableName---")
    println(schema)
    println()
}

Load data

After you inspect the database schemas and select the data, load the data into a DataFrame.

Kotlin DataFrame provides two ways to load data from a database:

Load data directly from a table.
Load the result of a custom SQL query.

Both approaches return a DataFrame that you can inspect, transform, and analyze.

Load data from a table

To load data from a table, use the DataFrame.readSqlTable() function.

The following example loads the first 100 rows from the movies table:

val moviesDf = DataFrame.readSqlTable(
    dbConfig = dbConfig,
    tableName = "movies",
    limit = 100
)

moviesDf

Load data with an SQL query

To execute a specific SQL query on your database, use the DataFrame.readSqlQuery() function. This approach is useful when you need to load specific columns, join tables, filter rows, or aggregate data in a database.

Let's retrieve a specific dataset related to movies directed by Quentin Tarantino. This query selects movie details and combines genres for each movie:

val TARANTINO_FILMS_SQL_QUERY = """
    SELECT name, year, rank, GROUP_CONCAT(genre) as "genres"
    FROM movies JOIN movies_directors ON movie_id = movies.id
    JOIN directors ON directors.id=director_id LEFT JOIN movies_genres ON movies.id = movies_genres.movie_id
    WHERE directors.first_name = "Quentin" AND directors.last_name = "Tarantino"
    GROUP BY name, year, rank
    ORDER BY year
    """

val tarantinoMoviesDf = DataFrame.readSqlQuery(dbConfig, TARANTINO_FILMS_SQL_QUERY)

tarantinoMoviesDf

Process data

After loading your database into a DataFrame, you can use DataFrame operations to process retrieved data.

For example, let's manipulate data from the previous section. The following code:

Replaces missing values in the year column using the .fillNA() function.
Converts the column to Int using the .convert() function.
Keeps only films released after 2000 using the .filter() function.

val filteredTarantinoMovies = tarantinoMoviesDf
    .fillNA { year }.with { 0 }
    .convert { year }.toInt()
    .filter { year > 2000 }

filteredTarantinoMovies

Analyze data

Use the DataFrame library to group, sort, and aggregate data so you can uncover and understand patterns in your data.

For example, let's read actor data from the actors table and find the 20 most common first names:

// Extract data from the actors table
val actorDf = DataFrame.readSqlTable(dbConfig, "actors", 10000)
val top20ActorNames = actorDf
   // Groups the data by the first_name column
   .groupBy { first_name }
   
   // Counts the occurrences of each unique first name
   .count()
   
    // Sorts the results in descending order of count
   .sortByDesc("count")
    
   // Selects the top 20 most frequent names for analysis.
   .take(20)

What's next

Explore data visualization using the Kandy library
Find additional information about data visualization in Data visualization with Kandy
For an extensive overview of tools and resources available for data science and analysis in Kotlin, see Kotlin and Java libraries for data analysis

29 June 2026