Type-Safe SQL in Scala: Building a Production-Grade Doobie Abstraction Layer

Introduction: The Problem with Raw SQL

SQL injection. Runtime string errors. Column order mismatches after migrations. Missing null checks. These are the daily realities of working with raw SQL in production systems.

Traditional database access patterns offer two unsatisfying choices:

1. Raw SQL strings - Fast but error-prone, with no compile-time safety
2. Heavy ORMs - Safe but slow, with performance penalties and learning curves

This article presents a third way: a lightweight, type-safe SQL abstraction layer built on Doobie that gives you compile-time guarantees without sacrificing performance. After processing billions of database operations over multiple years, this approach has eliminated entire classes of bugs while maintaining sub-millisecond query times.

Why Type Safety Matters in Production

Consider this real-world scenario that inspired the column ordering safety pattern:

-- Initial table schema
CREATE TABLE transactions (
  id BIGINT PRIMARY KEY,
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  amount DECIMAL(10,2)
);

-- Case class matches column order
case class Transaction(
  id: Long,
  createdAt: DateTime,
  updatedAt: DateTime,
  amount: BigDecimal
)

-- Query: SELECT * FROM transactions

Three months later, a developer adds a column to the middle of the table:

ALTER TABLE transactions
ADD COLUMN user_id BIGINT AFTER id;

The result? Silent data corruption. The query continues to work, but:

• createdAt gets populated with user_id values
• updatedAt gets populated with created_at timestamps
• amount gets populated with updated_at timestamps
• The actual amount is truncated

No compile error. No runtime exception. Just wrong data in production.

Building the Model Abstraction Layer

Base Model Trait

The foundation is a simple trait that all models extend:

abstract class BaseMySqlModel extends Product {
  def partitionKey: String
  def entityType: EntityType
}

abstract class IdBasedMySqlModel extends BaseMySqlModel {
  def mySqlId: Long
  def createdAt: DateTime
  def updatedAt: DateTime

  override def partitionKey: String = s"${entityType.modelStr}-$mySqlId"
}

Key Design Decisions:

• Extends Product to access case class fields via reflection
• partitionKey provides unique identifier across entity types
• Common timestamp fields enforced at type level

Companion Object Pattern

Every model has a companion object that defines its database mapping:

abstract class BaseMySqlModelCompanionObject[T <: BaseMySqlModel, IdType](
  val TableName: String
) {
  // JSON serialization
  implicit val reads: Reads[T]
  implicit val writes: Writes[T]

  // Field-to-database column mapping
  def columnOrdering: Seq[F[_, _]]

  // ID extraction and management
  def extractId(element: T): IdType
  def setIdAndReturn(id: IdType, model: T): T

  // Pre-create/update hooks
  def preCreateInMemoryChanges(t: T): T
  def preCheckForUpdates(oldObj: T, updatedObj: T): Unit

  // Lifecycle management
  def fieldsToAddForUpdates: Seq[UpdatableField]
}

The Column Ordering Safety Pattern

The critical safety feature: columnOrdering field that explicitly defines column order.

case class Transaction(
  mySqlId: Long,
  userId: Long,
  createdAt: DateTime,
  updatedAt: DateTime,
  amount: BigDecimal,
  entityType: EntityType = EntityType.Transaction
)

object Transaction extends IdBasedMySqlModelCompanionObject[Transaction]("transactions") {
  // Field references with types
  val UserId: F[Long, Long] = F("user_id", TableName)
  val Amount: F[BigDecimal, BigDecimal] = F("amount", TableName)

  // Explicit column ordering - MUST match case class field order
  override val columnOrdering: Seq[F[_, _]] = Seq(
    Id,           // mySqlId
    UserId,       // userId
    CreatedAt,    // createdAt
    UpdatedAt,    // updatedAt
    Amount        // amount
  )

  // JSON serialization
  implicit val reads: Reads[Transaction] = Json.reads[Transaction]
  implicit val writes: Writes[Transaction] = Json.writes[Transaction]

  override def extractId(t: Transaction): Long = t.mySqlId

  override def setIdAndReturn(id: Long, model: Transaction): Transaction =
    model.copy(mySqlId = id)
}

Safe vs Unsafe Selection

Now the query builder can select columns explicitly:

// ❌ UNSAFE: Breaks when columns added to middle of table
queryBuilder
  .where(Transaction.Id, Operator.Equal, transactionId)
  .selectAllUnsafe[Transaction]  // Uses SELECT *
  .runSingle

// ✅ SAFE: Always selects in correct order
queryBuilder
  .where(Transaction.Id, Operator.Equal, transactionId)
  .selectAllExplicit[Transaction](Transaction)  // Uses explicit column list
  .runSingle

The selectAllExplicit implementation:

def selectAllExplicit[A](
  companionObject: BaseMySqlModelCompanionObject[_, _]
)(implicit a: Read[A]): SelectQueryExecutor[A] = {
  val columnFragments = companionObject.columnOrdering.map(_.fNameForSelectClauses)
  // Generates: SELECT id, user_id, created_at, updated_at, amount FROM transactions
  val fragment = fragments.comma(NonEmptyList.fromListUnsafe(columnFragments.toList))
  queryExecutorCreator.createSelectQueryExecutor(getCompleteFragment(fragment).query[A])
}

Type-Safe SQL Fragment Generation

The interpolate() method provides runtime type-safe SQL generation:

def interpolate(any: Any): Fragment = any match {
  case str: String => fr"$str"
  case long: Long => fr"$long"
  case int: Int => fr"$int"
  case bool: Boolean => fr"$bool"
  case bigDecimal: BigDecimal => fr"$bigDecimal"
  case Some(value) => interpolate(value)  // Unwrap Option
  case None => Fragment.const("NULL")
  case value if DoobieGetsAndPuts.putMap.contains(value.getClass) =>
    // Custom types with implicit Meta instances
    implicit val put: Meta[value.type] =
      DoobieGetsAndPuts.putMap(value.getClass).asInstanceOf[Meta[value.type]]
    toPutOps[value.type](value).fr
  case _ =>
    throw new IllegalArgumentException(s"Unsupported type: ${any.getClass}")
}

Custom Type Mappings

The DoobieGetsAndPuts object provides Meta instances for domain types:

object DoobieGetsAndPuts {
  implicit val dateTimeMeta: Meta[DateTime] =
    Meta[Timestamp].timap(ts => new DateTime(ts.getTime))(dt => new Timestamp(dt.getMillis))

  implicit val entityTypeMeta: Meta[EntityType] =
    Meta[String].timap(EntityType.fromString)(_.modelStr)

  implicit val bigDecimalMeta: Meta[BigDecimal] =
    Meta[java.math.BigDecimal].timap(BigDecimal.apply)(_.bigDecimal)

  implicit val jsonMeta: Meta[JsValue] =
    Meta[String].timap(Json.parse)(Json.stringify)

  // Map for runtime lookup
  val putMap: Map[Class[_], Meta[_]] = Map(
    classOf[DateTime] -> dateTimeMeta,
    classOf[EntityType] -> entityTypeMeta,
    classOf[BigDecimal] -> bigDecimalMeta,
    classOf[JsValue] -> jsonMeta
  )
}

Smart UPDATE Generation with diffFragment

The diffFragment() method generates UPDATE statements by comparing old and new objects:

def diffFragment(
  oldObj: T,
  newObj: T,
  shouldUpdateDefaultFieldsForEmptyUserDefinedUpdates: Boolean
): Option[Fragment] = {
  preCheckForUpdates(oldObj, newObj)

  val defaultUpdateFragments = fieldsToAddForUpdates  // e.g., updated_at = NOW()
  val fieldsToIgnore = defaultUpdateFragments.map(_.columnName).toSet

  val userDefinedUpdateFragments = newObj
    .productElementNames
    .zip(oldObj.productIterator)
    .zip(newObj.productIterator)
    .flatMap { case ((name, oldVal), newVal) =>
      if (oldVal != newVal) {
        val dbName = convertCamelToSnake(name)

        if (fieldsToIgnore.contains(dbName)) {
          None  // Don't update managed fields
        } else {
          Some(Fragment.const(s"$dbName =") ++ interpolate(newVal))
        }
      } else {
        None
      }
    }
    .toList

  if (userDefinedUpdateFragments.isEmpty &&
      !shouldUpdateDefaultFieldsForEmptyUserDefinedUpdates) {
    None  // No changes detected
  } else {
    val allFragments = userDefinedUpdateFragments ++
                       defaultUpdateFragments.map(_.getFragment)
    Some(fragments.set(NonEmptyList.fromListUnsafe(allFragments)))
  }
}

Field Name Translation

The system automatically converts camelCase field names to snake_case database columns:

def convertCamelToSnake(name: String): String = {
  val caseClassName = if (name.endsWith("Opt")) {
    name.dropRight(3)  // userIdOpt → userId
  } else {
    name
  }

  CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, caseClassName)
  // userId → user_id
  // createdAt → created_at
}

Override Map for exceptions:

override def overriddenMap: Map[String, String] = Map(
  "mySqlId" -> "id",  // Special case: id instead of my_sql_id
  "jsonData" -> "data"  // Custom column name
)

Query Builder DSL

The fluent query builder provides type-safe query construction:

class QueryBuilder(
  private val rootTableName: String,
  private val whereFragments: Seq[Option[Fragment]],
  private val orderByFragments: Seq[Option[Fragment]],
  private val limitFragmentOpt: Option[Fragment],
  queryExecutorCreator: QueryExecutorCreator
) {

  // Type-safe where clauses with operators
  def where[T: Put](f: F[T, _], operator: BinaryOperator, value: T): QueryBuilder

  def where[T: Put](f: F[T, _], operator: ManyOperator, value1: T, remaining: Iterable[T]): QueryBuilder

  def where[T: Put](f: F[T, _], operator: UnaryOperator): QueryBuilder

  // Joins
  def leftJoin[T](
    otherTable: QueryBuilder,
    currentTableColumn: F[T, _],
    joinTableColumn: F[T, _],
    shouldSearchForeignTable: Boolean
  ): QueryBuilder

  def innerJoin[T](/* similar */): QueryBuilder

  // Ordering
  def orderBy[T](f: F[T, _], sortOrder: SortOrder): QueryBuilder

  // Pagination
  def withPageNoAndPageSize(pageNo: Int, pageSize: Int): QueryBuilder =
    withLimit(pageSize).withOffset((pageNo - 1) * pageSize)

  // Execution
  def count: CountQueryExecutor
  def exists: ExistsQueryExecutor
  def select[R1](f1: F[_, R1])(implicit reads: Read[R1]): SelectQueryExecutor[R1]
  def selectAllExplicit[A](companion: BaseMySqlModelCompanionObject[_, _]): SelectQueryExecutor[A]
}

Operator Type Safety

Different operators require different numbers of arguments:

sealed trait Operator

// Binary operators: field OP value
sealed trait BinaryOperator extends Operator {
  def getFragment[T: Put](f: F[T, _], value: T): Fragment
}

case object Equal extends BinaryOperator {
  override def getFragment[T: Put](f: F[T, _], value: T): Fragment =
    f.fNameForNonSelectClauses ++ fr"= $value"
}

case object GreaterThan extends BinaryOperator { /* ... */ }
case object LessThan extends BinaryOperator { /* ... */ }

// Unary operators: field OP
sealed trait UnaryOperator extends Operator {
  def getFragment[T](f: F[T, _]): Fragment
}

case object IsNull extends UnaryOperator {
  override def getFragment[T](f: F[T, _]): Fragment =
    f.fNameForNonSelectClauses ++ Fragment.const("IS NULL")
}

case object IsNotNull extends UnaryOperator { /* ... */ }

// Many operators: field IN (value1, value2, ...)
sealed trait ManyOperator extends Operator {
  def getFragment[T: Put](f: F[T, _], value1: T, remaining: Iterable[T]): Fragment
}

case object In extends ManyOperator {
  override def getFragment[T: Put](f: F[T, _], value1: T, remaining: Iterable[T]): Fragment =
    f.fNameForNonSelectClauses ++ fr"IN (" ++
    fragments.comma(NonEmptyList(fr"$value1", remaining.map(v => fr"$v").toList)) ++
    Fragment.const(")")
}

// Three operators: field BETWEEN low AND high
sealed trait ThreeOperator extends Operator {
  def getFragment[T: Put](f: F[T, _], low: T, high: T): Fragment
}

case object Between extends ThreeOperator { /* ... */ }

Usage Examples

// Simple query
Transaction
  .getDefaultQuery(queryContext, queryExecutor)
  .where(Transaction.UserId, Operator.Equal, userId)
  .selectAllExplicit[Transaction](Transaction)
  .runSingle

// Complex query with joins
Transaction
  .getDefaultQuery(queryContext, queryExecutor)
  .leftJoin(
    User.getBaseQuery(queryExecutor),
    Transaction.UserId,
    User.Id,
    shouldSearchForeignTable = false
  )
  .where(Transaction.Amount, Operator.GreaterThan, BigDecimal(100))
  .where(Transaction.CreatedAt, Operator.Between, startDate, endDate)
  .orderBy(Transaction.CreatedAt, SortOrder.Desc)
  .withPageNoAndPageSize(pageNo = 1, pageSize = 50)
  .selectAllExplicit(Transaction, User)
  .runList

// IN clause
Transaction
  .getDefaultQuery(queryContext, queryExecutor)
  .where(Transaction.Id, Operator.In, id1, idList.tail)
  .selectAllExplicit[Transaction](Transaction)
  .runList

// NULL checks
Transaction
  .getDefaultQuery(queryContext, queryExecutor)
  .where(Transaction.UserId, Operator.IsNull)
  .selectAllExplicit[Transaction](Transaction)
  .runList

Testing and Migration Strategies

Column Ordering Tests

Validate column ordering matches case class field order:

class TransactionModelSpec extends BaseMySqlModelSpec {
  "Transaction companion object" should {
    "have columnOrdering matching case class field order" in {
      val expectedFields = Seq("mySqlId", "userId", "createdAt", "updatedAt", "amount")
      val actualFields = Transaction.columnOrdering.map(_.columnName)

      actualFields should contain theSameElementsInOrderAs expectedFields.map(
        CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, _)
      )
    }
  }
}

Migration Safety

When adding columns:

// ✅ SAFE: Add to end of table
ALTER TABLE transactions
ADD COLUMN notes TEXT;

// Update case class
case class Transaction(
  mySqlId: Long,
  userId: Long,
  createdAt: DateTime,
  updatedAt: DateTime,
  amount: BigDecimal,
  notesOpt: Option[String] = None  // New field at end
)

// Update columnOrdering
override val columnOrdering = Seq(
  Id, UserId, CreatedAt, UpdatedAt, Amount,
  NotesOpt  // Added at end
)

If you must add in the middle:

// ⚠️ RISKY: Adding to middle
ALTER TABLE transactions
ADD COLUMN currency VARCHAR(3) AFTER amount;

// CRITICAL: Update columnOrdering immediately
override val columnOrdering = Seq(
  Id, UserId, CreatedAt, UpdatedAt, Amount,
  Currency,  // NEW - matches DB position
  NotesOpt
)

// Case class field order doesn't matter - columnOrdering controls DB mapping
case class Transaction(
  mySqlId: Long,
  currency: String,  // Can be anywhere in case class
  userId: Long,
  amount: BigDecimal,
  notesOpt: Option[String],
  createdAt: DateTime,
  updatedAt: DateTime
)

Multi-Tenancy with Query Context

The query context pattern enforces tenant isolation at compile time:

sealed trait MySqlQueryContext {
  def teamIdOpt: Option[Long]
  def shouldIncludeDeletedRecords: Boolean
}

case class AllScopedQueryContext(
  shouldIncludeDeletedRecords: Boolean = false
) extends MySqlQueryContext {
  override def teamIdOpt: Option[Long] = None
}

case class TeamScopedQueryContext(
  teamId: Long,
  shouldIncludeDeletedRecords: Boolean = false
) extends MySqlQueryContext {
  override def teamIdOpt: Option[Long] = Some(teamId)
}

Automatic Team Filtering

Team-scoped models automatically filter by team ID:

abstract class TeamScopedMySqlModel extends IdBasedMySqlModel {
  def teamId: Long
}

abstract class TeamScopedMySqlModelCompanionObject[T <: TeamScopedMySqlModel](
  override val TableName: String
) extends IdBasedMySqlModelCompanionObject[T](TableName) {
  val TeamId: F[Long, Long] = F("team_id", TableName)

  override def getDefaultQuery(
    qc: MySqlQueryContext,
    queryExecutorCreator: QueryExecutorCreator
  ): QueryBuilder = {
    val queryBuilder = super.getDefaultQuery(qc, queryExecutorCreator)
    qc.teamIdOpt match {
      case Some(teamId) =>
        queryBuilder.where(TeamId, Operator.Equal, teamId)  // Auto-add team filter
      case None =>
        queryBuilder  // Admin access
    }
  }
}

Soft Deletes

abstract class TeamScopedDeletableMySqlModel extends TeamScopedMySqlModel {
  def deletedAtOpt: Option[DateTime]
  final def isDeleted: Boolean = deletedAtOpt.isDefined
}

abstract class TeamScopedDeletableMySqlModelCompanionObject[T <: TeamScopedDeletableMySqlModel](
  override val TableName: String
) extends TeamScopedMySqlModelCompanionObject[T](TableName) {
  val DeletedAtOpt: F[DateTime, Option[DateTime]] = F.opt("deleted_at", TableName)

  override def getDefaultQuery(qc: MySqlQueryContext, queryExecutorCreator: QueryExecutorCreator): QueryBuilder = {
    val queryBuilder = super.getDefaultQuery(qc, queryExecutorCreator)
    if (qc.shouldIncludeDeletedRecords) {
      queryBuilder
    } else {
      queryBuilder.where(DeletedAtOpt, Operator.IsNull)  // Auto-filter deleted
    }
  }
}

Performance Considerations

Fragment Compilation

Doobie compiles fragments at query execution time. For hot paths, cache compiled queries:

object TransactionQueries {
  private val findByIdQuery = Transaction
    .getBaseQuery(queryExecutor)
    .where(Transaction.Id, Operator.Equal, 0L)  // Placeholder
    .selectAllExplicit[Transaction](Transaction)
    .query

  def findById(id: Long): ConnectionIO[Option[Transaction]] = {
    // Reuse compiled query structure, only bind new parameter
    findByIdQuery
      .toQuery0(id)
      .option
  }
}

Batch Operations

Use Doobie’s batch update for bulk operations:

def insertBatch(transactions: List[Transaction]): ConnectionIO[Int] = {
  val sql = s"INSERT INTO ${Transaction.TableName} (user_id, amount, created_at, updated_at) VALUES (?, ?, ?, ?)"

  Update[Transaction](sql).updateMany(transactions)
}

Connection Pooling

Use HikariCP with appropriate settings:

val hikariConfig = new HikariConfig()
hikariConfig.setJdbcUrl("jdbc:mysql://localhost:3306/mydb")
hikariConfig.setMaximumPoolSize(20)
hikariConfig.setMinimumIdle(5)
hikariConfig.setIdleTimeout(300000)  // 5 minutes
hikariConfig.setConnectionTimeout(10000)  // 10 seconds

val xa = Transactor.fromDataSource[IO](
  new HikariDataSource(hikariConfig),
  ExecutionContexts.synchronous
)

When to Use This Pattern

✅ Use When:

• Building production systems with long lifespans
• Multiple developers working on schema migrations
• Need compile-time safety without ORM overhead
• Performance is critical (sub-10ms queries)
• Complex queries with joins and aggregations

❌ Avoid When:

• Prototyping or short-lived projects
• Schema changes are infrequent
• Team is small (<3 developers)
• Using an existing ORM ecosystem (e.g., Django, Rails)

Conclusion

Type-safe SQL doesn’t require a heavy ORM. With Doobie and thoughtful abstractions, you can have:

1. Compile-time safety - Column order mismatches caught at compile time
2. Migration resilience - Explicit column ordering survives schema changes
3. Type-safe operators - Compiler enforces correct operator usage
4. Smart updates - Automatic diff detection with field-level granularity
5. Multi-tenancy - Query context enforces tenant isolation
6. Performance - Zero runtime overhead compared to raw SQL

The columnOrdering pattern alone has prevented countless production bugs. Combined with type-safe query building and automatic tenant filtering, this approach provides enterprise-grade safety without sacrificing the flexibility and performance of SQL.

The code examples are battle-tested in production systems processing millions of transactions daily. The patterns scale from small startups to large enterprises, and the type safety gives confidence during rapid iteration.

Further Reading

• Doobie Documentation
• Functional Programming in Scala (Red Book)
• Cats Effect Documentation
• HikariCP Performance Guide