Database Connection in Scala

In this lesson, you will learn how Scala applications connect to relational databases. Database connectivity is essential for building real-world applications that store, retrieve, and manage persistent data.

Scala uses JDBC (Java Database Connectivity) to interact with databases, which means any database supported by Java can also be used with Scala.

Why Database Connectivity Matters

Most applications rely on databases to:

• Store user information
• Manage transactions
• Persist application state
• Analyze and retrieve large datasets

Scala is commonly used with databases in backend systems, data pipelines, and enterprise applications.

What Is JDBC?

JDBC is a standard Java API that allows applications to communicate with relational databases such as:

• MySQL
• PostgreSQL
• Oracle
• SQLite

Scala runs on the JVM, so it can directly use JDBC without any extra setup.

JDBC Architecture Overview

A typical JDBC workflow involves:

• Loading the database driver
• Creating a connection
• Executing SQL statements
• Processing results
• Closing the connection

Adding a JDBC Driver

To connect to a database, you must include the appropriate JDBC driver. For example, MySQL uses the MySQL Connector/J driver.

In SBT, you would add:

libraryDependencies += "mysql" % "mysql-connector-java" % "8.0.33"

Establishing a Database Connection

Below is a basic example of connecting to a MySQL database using JDBC.

import java.sql.DriverManager

val url = "jdbc:mysql://localhost:3306/testdb"
val username = "root"
val password = "password"

val connection = DriverManager.getConnection(url, username, password)

println("Connected successfully!")

The connection object is the gateway for executing SQL queries.

Executing SQL Queries

Once connected, you can execute SQL queries using a statement.

val statement = connection.createStatement()
val resultSet = statement.executeQuery("SELECT * FROM users")

while (resultSet.next()) {
  println(resultSet.getString("name"))
}

Each row is accessed using the next() method.

Using Prepared Statements

Prepared statements help prevent SQL injection and improve performance.

val query = "SELECT * FROM users WHERE id = ?"
val preparedStmt = connection.prepareStatement(query)

preparedStmt.setInt(1, 1)

val result = preparedStmt.executeQuery()
while (result.next()) {
  println(result.getString("name"))
}

Inserting Data into a Database

You can insert records using SQL INSERT statements.

val insertSQL = "INSERT INTO users (name, age) VALUES (?, ?)"
val stmt = connection.prepareStatement(insertSQL)

stmt.setString(1, "Alice")
stmt.setInt(2, 25)

stmt.executeUpdate()

Updating and Deleting Records

Scala can also update or delete data using SQL commands.

// Update
val updateSQL = "UPDATE users SET age = ? WHERE name = ?"
val updateStmt = connection.prepareStatement(updateSQL)
updateStmt.setInt(1, 30)
updateStmt.setString(2, "Alice")
updateStmt.executeUpdate()

// Delete
val deleteSQL = "DELETE FROM users WHERE name = ?"
val deleteStmt = connection.prepareStatement(deleteSQL)
deleteStmt.setString(1, "Alice")
deleteStmt.executeUpdate()

Closing Database Resources

Always close database resources to avoid memory leaks.

resultSet.close()
statement.close()
connection.close()

Using Try for Safe Database Operations

Use Try to safely handle connection failures.

import scala.util.Try
import java.sql.DriverManager

val dbConnection = Try {
  DriverManager.getConnection(url, username, password)
}

dbConnection match {
  case scala.util.Success(conn) =>
    println("Connected")
    conn.close()
  case scala.util.Failure(err) =>
    println(err.getMessage)
}

Popular Scala Database Libraries

• Slick
• Doobie
• Anorm
• Quill

These libraries provide safer and more Scala-friendly database access.

📝 Practice Exercises

Exercise 1

Connect to a database and print all records from a table.

Exercise 2

Insert a new user record into a table.

Exercise 3

Update an existing database record.

✅ Practice Answers

Answer 1

connection.createStatement()
  .executeQuery("SELECT * FROM users")

Answer 2

stmt.setString(1, "Bob")
stmt.setInt(2, 28)
stmt.executeUpdate()

Answer 3

updateStmt.executeUpdate()

What’s Next?

In the next lesson, you will learn about Web Frameworks in Scala and how to build web applications and APIs.