Running Queries
Read rows into case classes.
Running Queries is a free Scala for Backend Engineering & Functional Programming lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Scala for Backend Engineering & Functional Programming learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
The sql Interpolator
Doobie's sql string interpolator is the core of querying. It parses your SQL and turns embedded Scala values into safe, bound parameters.
Interpolated values become JDBC ? placeholders, so there is no SQL injection and no manual PreparedStatement wiring.
import doobie.implicits._
val minAge = 18
val frag = sql"select name from users where age >= $minAge"From Fragment to Query
An sql"..." expression is a Fragment. To run it as a read you call .query[A], choosing the row type A.
Doobie uses a Read[A] typeclass to map result columns onto A, supporting primitives, tuples, and case classes.
case class User(name: String, age: Int)
val q: Query0[User] =
sql"select name, age from users".query[User]unique, option, and to
A Query0[A] offers different accumulators. .unique expects exactly one row, .option expects zero or one, and .to[List] collects all rows.
Each returns a ConnectionIO that you later .transact.
val one: ConnectionIO[User] = q.unique
val maybe: ConnectionIO[Option[User]] = q.option
val all: ConnectionIO[List[User]] = q.to[List]Mapping Rows to Case Classes
Doobie derives Read instances for case classes automatically when each field has a column mapping. Column order must match the select list.
This lets you select straight into rich domain types without boilerplate row parsing.
case class Account(id: Long, email: String, active: Boolean)
val accounts =
sql"select id, email, active from accounts"
.query[Account].to[Vector]Parameterized Queries
Every interpolated Scala value is bound as a parameter using its Put instance. You can interpolate as many as you like, including in different positions.
Because they are bound, not concatenated, types are checked and injection is impossible.
def findByEmail(email: String): ConnectionIO[Option[Account]] =
sql"select id, email, active from accounts where email = $email"
.query[Account].optionStreaming Large Results
For big result sets, use .stream to get an fs2 Stream[ConnectionIO, A]. Rows are pulled lazily with a server-side cursor, keeping memory bounded.
Combine it with fs2 combinators to process millions of rows incrementally.
import fs2.Stream
val s: Stream[ConnectionIO, Account] =
sql"select id, email, active from accounts"
.query[Account].streamControlling Fetch Size
The default cursor fetch size may load everything at once on some drivers. .streamWithChunkSize(n) sets the JDBC fetch size so the cursor fetches n rows per round trip.
Tuning this balances round trips against memory.
val s =
sql"select id, email, active from accounts"
.query[Account]
.streamWithChunkSize(512)Optional Columns and Nullability
Nullable columns map to Option[A] in your row type. If a column can be NULL you must read it as an Option, or Doobie throws on a null value.
This makes nullability explicit and type-safe at the boundary.
case class Person(id: Long, nickname: Option[String])
val ps =
sql"select id, nickname from people".query[Person].to[List]Building SQL with Fragments
Fragment values compose with ++, letting you assemble queries from pieces while keeping parameters bound.
The fr interpolator builds a fragment with a trailing space, which is handy for stitching clauses together.
val base = fr"select id, email, active from accounts"
val where = fr"where active = $${true}"
val q = (base ++ where).query[Account]Dynamic WHERE Clauses
Doobie's Fragments helpers build conditional clauses. Fragments.whereAndOpt combines optional filters, emitting only the ones that are present.
This is the idiomatic way to build safe dynamic search queries.
import doobie.util.fragments._
val emailOpt: Option[String] = Some("a@b.com")
val filter = whereAndOpt(emailOpt.map(e => fr"email = $e"))
val q = (fr"select id, email, active from accounts" ++ filter)Checking Queries at Build Time
Doobie's check analysis runs a query against the database schema to verify column types and counts match your Scala types.
Used in tests, it catches schema drift before it reaches production.
import doobie.scalatest._
// inside a spec mixing in IOChecker
check(sql"select id, email, active from accounts".query[Account])Quick Check
Choose the right accumulator.
Recap
The sql interpolator builds parameterized Fragments; .query[A] turns them into a Query0[A] mapped via Read[A].
Pick .unique, .option, .to[List], or .stream to accumulate rows. Use Fragments for dynamic clauses and check to validate against the schema.
Frequently asked questions
Is the “Running Queries” lesson free?
Yes — the full text of “Running Queries” is free to read here on the web, and the Scala for Backend Engineering & Functional Programming course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Scala for Backend Engineering & Functional Programming course, upgrade to CoddyKit PRO.
What will I learn in “Running Queries”?
Read rows into case classes. You practise Scala for Backend Engineering & Functional Programming with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start Scala for Backend Engineering & Functional Programming?
No prior experience is required. Scala for Backend Engineering & Functional Programming on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Running Queries” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this Scala for Backend Engineering & Functional Programming lesson?
Yes. Every Scala for Backend Engineering & Functional Programming lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.