### Common Query Setup

Source: https://github.com/arturaz/doobie-typesafe/blob/main/docs/002_querying.md

Sets up common imports and column definitions used across various query examples. This includes importing doobie and its implicits, and defining a Columns object for first name and last name.

```scala
import doobie.*
import doobie.implicits.*

import Users as t
val columns = Columns((t.firstName, t.lastName))
```

--------------------------------

### TableDefinition.RowHelpers Examples

Source: https://github.com/arturaz/doobie-typesafe/blob/main/docs/001_table_definitions.md

Demonstrates helper methods provided by TableDefinition.RowHelpers for common SQL operations like insert and select. No specific imports are shown as they are assumed from the context.

```scala
Users.Row.insert
```

```scala
Users.Row.insertOnConflictDoNothing(Columns(Users.Names))
```

```scala
Users.Row.insertOnConflictDoNothing0
```

```scala
Users.Row.selectAll
```

--------------------------------

### Define User Table Schema

Source: https://github.com/arturaz/doobie-typesafe/blob/main/docs/002_querying.md

Defines the schema for the 'users' table, including columns for id, first name, last name, and an optional nickname. This setup is required for subsequent query examples.

```scala
import doobie.*
import doobie.implicits.*

object Users extends TableDefinition("users") {
  val id: Column[Int] = Column("id")
  val firstName: Column[String] = Column("first_name")
  val lastName: Column[String] = Column("last_name")
  val nickname: Column[Option[String]] = Column("nickname")

  case class Names(firstName: String, lastName: String, nickname: Option[String])
  object Names extends WithSQLDefinition[Names](Composite((
    firstName.sqlDef, lastName.sqlDef, nickname.sqlDef
  )))(Names.apply)(Tuple.fromProductTyped)
  
  case class Row(id: Int, names: Names)
  object Row extends WithSQLDefinition[Row](Composite((
    id.sqlDef, Names.sqlDef
  )))(Row.apply)(Tuple.fromProductTyped) with TableDefinition.RowHelpers[Row](this)
}
```

--------------------------------

### Uniform Query Construction with FragmentExtensions.queryOf

Source: https://context7.com/arturaz/doobie-typesafe/llms.txt

Shows how `queryOf` on `Fragment` provides a consistent API for query construction using `SQLDefinition`, `Columns`, or an implicit `Read`. It also demonstrates adding a query label for logging.

```scala
import doobie.*
import doobie.implicits.*
import Users as t

// With SQLDefinition
val q1 = sql"SELECT ${t.Row} FROM $t WHERE ${t.id === 1}".queryOf(t.Row)
// q1: Query0[Users.Row]

// With Columns
val cols = Columns((t.firstName, t.lastName))
val q2   = sql"SELECT $cols FROM $t".queryOf(cols)
// q2: Query0[(String, String)]

// With implicit Read (untyped)
val q3 = sql"SELECT count(*) FROM $t".queryOf[Int]
// q3: Query0[Int]

// With a query label for logging
val q4 = sql"SELECT $cols FROM $t".queryOf(cols, "list-user-names")
```

--------------------------------

### Composite - Bundle Columns into a Case Class

Source: https://context7.com/arturaz/doobie-typesafe/llms.txt

Defines how to create a `SQLDefinition[R]` from a tuple of `SQLDefinition`s, a constructor function, and a deconstructor. This resulting definition can be used for both reading and writing SQL data.

```APIDOC
## `Composite` — Bundle Columns into a Case Class

`Composite` creates a `SQLDefinition[R]` from a tuple of `SQLDefinition`s, a constructor function (`map`), and a deconstructor (`unmap`). The resulting definition works in both SELECT (read) and INSERT/UPDATE (write) positions.

```scala
import doobie.*
import doobie.implicits.*

case class Names(firstName: String, lastName: String, nickname: Option[String])

// Standalone composite
val namesDef: SQLDefinition[Names] = Composite((
  Users.firstName.sqlDef, Users.lastName.sqlDef, Users.nickname.sqlDef
))(Names.apply)(Tuple.fromProductTyped)

// Use in SELECT
val query = sql"SELECT $namesDef FROM $Users WHERE ${Users.id === 1}".queryOf(namesDef)

// Nest composites and columns together
val columns = Columns((Users.id, namesDef))
val q2      = sql"SELECT $columns FROM $Users".queryOf(columns)

// Compose two composites (PersonWithPets example)
case class Pets(pet1: String, pet2: String)
val petsDef: SQLDefinition[Pets] = Composite((
  Pets.pet1Col.sqlDef, Pets.pet2Col.sqlDef
))(Pets.apply)(Tuple.fromProductTyped)

case class PersonWithPets(name: String, pets: Pets)
val personWithPetsDef: SQLDefinition[PersonWithPets] = Composite((namesDef, petsDef))(
  (n, p) => PersonWithPets(n.firstName, p)
)(r => (Names(r.name, "", None), r.pets))
```
```

--------------------------------

### WithSQLDefinition - Inline a Definition on a Companion Object

Source: https://context7.com/arturaz/doobie-typesafe/llms.txt

Extending `WithSQLDefinition[A]` on a companion object makes the object itself a `SQLDefinition[A]`. It exposes `Read`/`Write` givens and all definition methods directly. Combining with `TableDefinition.RowHelpers` provides pre-built INSERT, SELECT-all, and upsert helpers.

```APIDOC
## `WithSQLDefinition` — Inline a Definition on a Companion Object

Extending `WithSQLDefinition[A]` on a companion object makes the companion object itself a `SQLDefinition[A]`, exposing `Read`/`Write` givens and all definition methods directly. Combine with `TableDefinition.RowHelpers` to gain pre-built INSERT, SELECT-all, and upsert helpers.

```scala
import doobie.*
import doobie.implicits.*

object Users extends TableDefinition("users") {
  val id:        Column[Int]              = Column("id")
  val firstName: Column[String]           = Column("first_name")
  val lastName:  Column[String]           = Column("last_name")
  val nickname:  Column[Option[String]]   = Column("nickname")

  case class Names(firstName: String, lastName: String, nickname: Option[String])
  object Names extends WithSQLDefinition[Names](Composite((
    firstName.sqlDef, lastName.sqlDef, nickname.sqlDef
  )))(Names.apply)(Tuple.fromProductTyped)

  case class Row(id: Int, names: Names)
  object Row extends WithSQLDefinition[Row](Composite((
    id.sqlDef, Names.sqlDef
  )))(Row.apply)(Tuple.fromProductTyped) with TableDefinition.RowHelpers[Row](this)
}

// RowHelpers provides:
val insertUpdate: Update[Users.Row]    = Users.Row.insert         // INSERT INTO users (…) VALUES (…)
val selectAll:    Query0[Users.Row]    = Users.Row.selectAll      // SELECT … FROM users
val upsertIgnore: Update[Users.Row]    = Users.Row.insertOnConflictDoNothing0
val upsertOnCols: Update[Users.Row]    =
  Users.Row.insertOnConflictDoNothing(Columns(Users.Names))

// Batch insert
import cats.data.NonEmptyVector
Users.Row.insert.updateMany(NonEmptyVector.of(
  Users.Row(1, Users.Names("Alice", "Smith",  None)),
  Users.Row(2, Users.Names("Bob",   "Jones",  Some("BJ")))
))
```
```

--------------------------------

### Table Aliases for Joins with `.as`

Source: https://context7.com/arturaz/doobie-typesafe/llms.txt

Use `.as("alias")` on a `TableDefinition` to create an `AliasedTableDefinition`. This renders as `table AS alias` and prefixes column references with the alias, preventing name collisions in multi-table queries.

```scala
import doobie.*
import doobie.implicits.*

object Addresses extends TableDefinition("addresses") {
  val id:     Column[Int]    = Column("id")
  val userId: Column[Int]    = Column("user_id")
  val street: Column[String] = Column("street")
  val city:   Column[String] = Column("city")

  case class Address(street: String, city: String)
  object Address extends WithSQLDefinition[Address](Composite((
    street.sqlDef, city.sqlDef
  )))(Address.apply)(Tuple.fromProductTyped)
}

val u = Users     as "u"   // renders as "users AS u"
val a = Addresses as "a"   // renders as "addresses AS a"

// u(_.firstName) → Column prefixed as "u.first_name"
val columns = Columns((u(_.firstName), u(_.lastName), a(_.street), a(_.city)))

val query = sql"""
  SELECT $columns
  FROM   $u
  INNER JOIN $a ON ${u(_.id) === a(_.userId)}
  WHERE ${u(_.id) === 42}
".queryOf(columns)
// query: Query0[(String, String, String, String)]
```

--------------------------------

### Left / Right Joins with `.option`

Source: https://context7.com/arturaz/doobie-typesafe/llms.txt

Call `.option` on a `Column[A]` or `SQLDefinition[A]` (where `A` is not `Option`) to lift it to `Column[Option[A]]` / `SQLDefinition[Option[A]]`. This is useful when a side of a LEFT JOIN might be absent. `Composite.option` handles mixed nullable/non-nullable members.

```scala
import doobie.*
import doobie.implicits.*

val u = Users     as "u"
val a = Addresses as "a"

// a(_.street) is Column[String]; .option lifts it to Column[Option[String]]
val columns = Columns((u(_.firstName), u(_.lastName), a(_.street.option)))

val query = sql"""
  SELECT $columns
  FROM   $u
  LEFT JOIN $a ON ${u(_.id) === a(_.userId)}
".queryOf(columns)
// query: Query0[(String, String, Option[String])]

// Entire composite as Option — None when all join columns are NULL
val personsT = Person as "person"
val petsT    = Pets   as "pet"
val colsWithComposite = Columns((personsT(_.nameCol), petsT(_.Row.option)))
// → Query0[(String, Option[Pets.Row])]  — Pets.Row is None when no pet row joined
```

--------------------------------

### Define a Users Table

Source: https://github.com/arturaz/doobie-typesafe/blob/main/docs/001_table_definitions.md

Defines a 'users' table with id, first_name, last_name, and nickname columns. Imports are required.

```scala
import doobie.*

object Users extends TableDefinition("users") {
  val id: Column[Int] = Column("id")
  val firstName: Column[String] = Column("first_name")
  val lastName: Column[String] = Column("last_name")
  val nickname: Column[Option[String]] = Column("nickname")
}
```

--------------------------------

### Inline Definition on Companion Object

Source: https://context7.com/arturaz/doobie-typesafe/llms.txt

Extend `WithSQLDefinition[A]` on a companion object to make it a `SQLDefinition[A]`. This exposes `Read`/`Write` givens and definition methods directly. Combine with `TableDefinition.RowHelpers` for pre-built INSERT, SELECT-all, and upsert helpers.

```scala
import doobie.*
import doobie.implicits.*

object Users extends TableDefinition("users") {
  val id:        Column[Int]              = Column("id")
  val firstName: Column[String]           = Column("first_name")
  val lastName:  Column[String]           = Column("last_name")
  val nickname:  Column[Option[String]]   = Column("nickname")

  case class Names(firstName: String, lastName: String, nickname: Option[String])
  object Names extends WithSQLDefinition[Names](Composite(( 
    firstName.sqlDef, lastName.sqlDef, nickname.sqlDef
  )))(Names.apply)(Tuple.fromProductTyped))

  case class Row(id: Int, names: Names)
  object Row extends WithSQLDefinition[Row](Composite(( 
    id.sqlDef, Names.sqlDef
  )))(Row.apply)(Tuple.fromProductTyped)) with TableDefinition.RowHelpers[Row](this)
}

// RowHelpers provides:
val insertUpdate: Update[Users.Row]    = Users.Row.insert         // INSERT INTO users (…)
val selectAll:    Query0[Users.Row]    = Users.Row.selectAll      // SELECT … FROM users
val upsertIgnore: Update[Users.Row]    = Users.Row.insertOnConflictDoNothing0
val upsertOnCols: Update[Users.Row]    =
  Users.Row.insertOnConflictDoNothing(Columns(Users.Names))

// Batch insert
import cats.data.NonEmptyVector
Users.Row.insert.updateMany(NonEmptyVector.of(
  Users.Row(1, Users.Names("Alice", "Smith",  None)),
  Users.Row(2, Users.Names("Bob",   "Jones",  Some("BJ")))
))
```

--------------------------------

### Batch Insert Using Case Class

Source: https://github.com/arturaz/doobie-typesafe/blob/main/docs/003_inserting.md

Perform batch inserts efficiently by providing a collection of case class instances. This is ideal for inserting multiple rows at once.

```scala
// Batch insert.
Users.Row.insert.updateMany(NonEmptyVector.of(
  Users.Row(42, Users.Names("John", "Doe", Some("Johnny"))),
  Users.Row(43, Users.Names("Jane", "Doe", None))
))
```

--------------------------------

### Perform Type-Safe Left Join with Optional Columns

Source: https://github.com/arturaz/doobie-typesafe/blob/main/docs/002_querying.md

Constructs a type-safe SQL query for a left join between Users and Addresses. It shows how to handle potentially null columns from the right side of the join using `.option`.

```scala
import doobie.*
import doobie.implicits.*

val id = 42

// Construct the prefixed versions of the tables. They will be known as `u` and `a` in the query.
val u = Users as "u"
val a = Addresses as "a"

val columns = Columns((u(_.firstName), u(_.lastName), a(_.street.option)))

val query1Sql =
  sql"SELECT $columns FROM $u LEFT JOIN $a ON ${u(_.id) === a(_.userId)} WHERE ${u(_.id) === id}"
val query1 = query1Sql.queryOf(columns)
```

--------------------------------

### Add doobie-typesafe snapshot to build.sbt

Source: https://github.com/arturaz/doobie-typesafe/blob/main/docs/000_installation.md

Use this snippet to add the snapshot version of doobie-typesafe to your project's build.sbt file. Ensure sonatypeOssRepos is added to resolvers.

```scala
resolvers ++= Resolver.sonatypeOssRepos("snapshots")
libraryDependencies += "io.github.arturaz" %% "doobie-typesafe" % "@SNAPSHOT_VERSION@"
```

--------------------------------

### Type-Safe Null Check Query (Is Null)

Source: https://github.com/arturaz/doobie-typesafe/blob/main/docs/002_querying.md

Constructs a type-safe SQL query to select users where the nickname is null. It demonstrates checking for nullability using `=== None`.

```scala
val hasNoNicknameQuerySql = sql"SELECT $columns FROM $t WHERE ${t.nickname === None}"
val hasNoNicknameQuery = hasNoNicknameQuerySql.queryOf(columns)
```

--------------------------------

### Add doobie-typesafe snapshot to build.mill

Source: https://github.com/arturaz/doobie-typesafe/blob/main/docs/000_installation.md

Use this snippet to add the snapshot version of doobie-typesafe to your project's build.mill file. Ensure sonatype snapshots repository is added.

```scala
override def repositoriesTask = T.task {
  super.repositoriesTask() ++ Seq(
    coursier.Repositories.sonatype("snapshots")
  )
}

override def mvnDeps = Agg(
  mvn"io.github.arturaz::doobie-typesafe:@SNAPSHOT_VERSION@"
)
```

--------------------------------

### Add doobie-typesafe to build.sbt

Source: https://context7.com/arturaz/doobie-typesafe/llms.txt

Add the doobie-typesafe library dependency to your build.sbt file for Scala 3 projects. Snapshot builds require specific resolvers.

```scala
// build.sbt
libraryDependencies += "io.github.arturaz" %% "doobie-typesafe" % "<version>"

// Snapshot builds from main:
resolvers ++= Resolver.sonatypeOssRepos("snapshots")
libraryDependencies += "io.github.arturaz" %% "doobie-typesafe" % "<snapshot-version>"

// Mill (build.mill):
override def mvnDeps = Agg(
  mvn"io.github.arturaz::doobie-typesafe:<version>"
)
```

--------------------------------

### Define Entire Row Case Class with Helpers

Source: https://github.com/arturaz/doobie-typesafe/blob/main/docs/001_table_definitions.md

Defines a 'Row' case class for the entire table, including composite columns, and utilizes TableDefinition.RowHelpers. Requires doobie and doobie.implicits imports.

```scala
import doobie.*
import doobie.implicits.*

object Users extends TableDefinition("users") {
  val id: Column[Int] = Column("id")
  val firstName: Column[String] = Column("first_name")
  val lastName: Column[String] = Column("last_name")
  val nickname: Column[Option[String]] = Column("nickname")

  case class Names(firstName: String, lastName: String, nickname: Option[String])
  object Names extends WithSQLDefinition[Names](Composite((
    firstName.sqlDef, lastName.sqlDef, nickname.sqlDef
  ))(Names.apply)(Tuple.fromProductTyped))
  
  case class Row(id: Int, names: Names)
  object Row extends WithSQLDefinition[Row](Composite((
    id.sqlDef, Names.sqlDef
  ))(Row.apply)(Tuple.fromProductTyped)) with TableDefinition.RowHelpers[Row](this)
}

val id = 42
import Users as t
val columns = t.Row
val querySql = sql"SELECT $columns FROM $t WHERE ${t.id === id}"
val query = querySql.queryOf(columns)
```

--------------------------------

### ON CONFLICT Upsert Operations in doobie-typesafe

Source: https://context7.com/arturaz/doobie-typesafe/llms.txt

Demonstrates how to perform ON CONFLICT operations using `insertOnConflictDoNothing` for ignoring duplicates or building manual `ON CONFLICT DO UPDATE` clauses with the `EXCLUDED` pseudo-table.

```scala
import doobie.*
import doobie.implicits.*

// Ignore duplicate on the Names columns
val upsertIgnore: Update[Users.Row] =
  Users.Row.insertOnConflictDoNothing(Columns(Users.Names))

// Manual DO UPDATE with EXCLUDED pseudo-table via .excluded
val t = Person `as` "t"
val upsertUpdate = sql"""
  ${insertInto(t, t(_.nameCol) ==> "Steve")}
  ON CONFLICT (${t(_.nameCol)})
  DO UPDATE SET ${t(_.ageCol)} = ${t(_.ageCol)} + ${t(_.ageCol).excluded}
"""
```

--------------------------------

### Type-Safe Null Check Query (Is Not Null)

Source: https://github.com/arturaz/doobie-typesafe/blob/main/docs/002_querying.md

Constructs a type-safe SQL query to select users where the nickname is not null. It uses `!== None` to check for non-nullability.

```scala
val hasNicknameQuerySql = sql"SELECT $columns FROM $t WHERE ${t.nickname !== None}"
val hasNicknameQuery = hasNicknameQuerySql.queryOf(columns)
```

--------------------------------

### Type-Safe INSERT with `insertInto`

Source: https://context7.com/arturaz/doobie-typesafe/llms.txt

Use `-->` to bind a single column to a value, producing a `(Fragment, Fragment)` pair. Pass a `NonEmptyVector` of these pairs to `insertInto`. Alternatively, use `==>` on an `SQLDefinition` to expand a whole composite at once.

```scala
import doobie.*
import doobie.implicits.*
import cats.data.NonEmptyVector

// Column-by-column binding
val insert1 = Users.insertInto(NonEmptyVector.of(
  Users.id        --> 42,
  Users.firstName --> "John",
  Users.lastName  --> "Doe",
  Users.nickname  --> None,
))
// SQL: INSERT INTO users (id, first_name, last_name, nickname) VALUES (?, ?, ?, ?)

// Composite expansion via ==>
val insert2 = Users.insertInto(
  NonEmptyVector.of(Users.id --> 42) ++: (Users.Names ==> Users.Names("John", "Doe", Some("JD")))
)

// Full Row via RowHelpers
val insert3 = Users.insertInto(Users.Row ==> Users.Row(42, Users.Names("John", "Doe", None)))

// Batch via RowHelpers
Users.Row.insert.updateMany(NonEmptyVector.of(
  Users.Row(42, Users.Names("John", "Doe", Some("Johnny"))),
  Users.Row(43, Users.Names("Jane", "Doe", None))
))
```

--------------------------------

### Add doobie-typesafe to build.mill

Source: https://github.com/arturaz/doobie-typesafe/blob/main/docs/000_installation.md

Use this snippet to add the stable version of doobie-typesafe to your project's build.mill file.

```scala
override def mvnDeps = Agg(
  mvn"io.github.arturaz::doobie-typesafe:@VERSION@"
)
```

--------------------------------

### Add doobie-typesafe to build.sbt

Source: https://github.com/arturaz/doobie-typesafe/blob/main/docs/000_installation.md

Use this snippet to add the stable version of doobie-typesafe to your project's build.sbt file.

```scala
libraryDependencies += "io.github.arturaz" %% "doobie-typesafe" % "@VERSION@"
```

--------------------------------

### Define a SQL Table with TableDefinition

Source: https://context7.com/arturaz/doobie-typesafe/llms.txt

Extend TableDefinition with the table's SQL name and declare each column as a Column[A] value. The object can be used directly in sql string interpolations as the table name.

```scala
import doobie.*
import doobie.implicits.*

object Users extends TableDefinition("users") {
  val id: Column[Int]              = Column("id")
  val firstName: Column[String]    = Column("first_name")
  val lastName: Column[String]     = Column("last_name")
  val nickname: Column[Option[String]] = Column("nickname")
}

// Usage in a raw SQL fragment — Users interpolates as the table name "users"
val fragment: Fragment = sql"SELECT ${Users.id} FROM $Users WHERE ${Users.id === 1}"
```

--------------------------------

### Define Composite for Case Class

Source: https://context7.com/arturaz/doobie-typesafe/llms.txt

Use `Composite` to create a `SQLDefinition` from a tuple of `SQLDefinition`s, a constructor function, and a deconstructor. This definition works for both SELECT (read) and INSERT/UPDATE (write) operations.

```scala
import doobie.*
import doobie.implicits.*

case class Names(firstName: String, lastName: String, nickname: Option[String])

// Standalone composite
val namesDef: SQLDefinition[Names] = Composite((
  Users.firstName.sqlDef, Users.lastName.sqlDef, Users.nickname.sqlDef
))(Names.apply)(Tuple.fromProductTyped)

// Use in SELECT
val query = sql"SELECT $namesDef FROM $Users WHERE ${Users.id === 1}".queryOf(namesDef)

// Nest composites and columns together
val columns = Columns((Users.id, namesDef))
val q2      = sql"SELECT $columns FROM $Users".queryOf(columns)

// Compose two composites (PersonWithPets example)
case class Pets(pet1: String, pet2: String)
val petsDef: SQLDefinition[Pets] = Composite((
  Pets.pet1Col.sqlDef, Pets.pet2Col.sqlDef
))(Pets.apply)(Tuple.fromProductTyped)

case class PersonWithPets(name: String, pets: Pets)
val personWithPetsDef: SQLDefinition[PersonWithPets] = Composite((namesDef, petsDef))(
  (n, p) => PersonWithPets(n.firstName, p)
)(r => (Names(r.name, "", None), r.pets))
```

--------------------------------

### Query with Composite Column

Source: https://github.com/arturaz/doobie-typesafe/blob/main/docs/001_table_definitions.md

Constructs a SQL query selecting a composite 'names' column from the 'users' table. Requires doobie and doobie.implicits imports.

```scala
import doobie.*
import doobie.implicits.*

val id = 42
import Users as t
val columns = names
val querySql = sql"SELECT $columns FROM $t WHERE ${t.id === id}"
val query = querySql.queryOf(columns)
```

--------------------------------

### Insert Individual Columns

Source: https://github.com/arturaz/doobie-typesafe/blob/main/docs/003_inserting.md

Specify individual columns and their values when inserting a new row. Ensure all non-nullable columns are provided.

```scala
import cats.data.NonEmptyVector

Users.insertInto(NonEmptyVector.of(
  Users.id --> 42,
  Users.firstName --> "John",
  Users.lastName --> "Doe",
  Users.nickname --> None,
))
```

--------------------------------

### Define Case Class and Composite within TableDefinition

Source: https://github.com/arturaz/doobie-typesafe/blob/main/docs/001_table_definitions.md

Defines a 'Names' case class and its SQLDefinition directly within the 'Users' TableDefinition object. Requires doobie and doobie.implicits imports.

```scala
import doobie.*
import doobie.implicits.*

object Users extends TableDefinition("users") {
  val id: Column[Int] = Column("id")
  val firstName: Column[String] = Column("first_name")
  val lastName: Column[String] = Column("last_name")
  val nickname: Column[Option[String]] = Column("nickname")
  
  case class Names(firstName: String, lastName: String, nickname: Option[String])
  object Names extends WithSQLDefinition[Names](Composite((
    firstName.sqlDef, lastName.sqlDef, nickname.sqlDef
  ))(Names.apply)(Tuple.fromProductTyped))
}

val id = 42
import Users as t
val columns = Columns((t.id, t.Names))
val querySql = sql"SELECT $columns FROM $t WHERE ${t.id === id}"
val query = querySql.queryOf(columns)
```

--------------------------------

### Type-Safe Specific Null Value Query

Source: https://github.com/arturaz/doobie-typesafe/blob/main/docs/002_querying.md

Constructs a type-safe SQL query to select users with a specific nickname value. It shows how to compare an optional column with `Some`.

```scala
val hasSpecificNicknameQuerySql = sql"SELECT $columns FROM $t WHERE ${t.nickname === Some("Johnny")}"
val hasSpecificNicknameQuery = hasSpecificNicknameQuerySql.queryOf(columns)
```

--------------------------------

### Insert with Auto-Generated Keys using `updateManyWithGeneratedKeys`

Source: https://context7.com/arturaz/doobie-typesafe/llms.txt

The `Update[A].updateManyWithGeneratedKeys(sqlDef)` extension method provides a type-safe wrapper around doobie's `updateManyWithGeneratedKeys`, returning a typed `Stream[ConnectionIO, K]` for generated keys.

```scala
import doobie.*
import doobie.implicits.*
import fs2.Stream

// Cars table has an auto-increment id column
val data = Vector(
  Cars.RowWithoutAutogenerated("Ford"),
  Cars.RowWithoutAutogenerated("Mustang")
)

val stream: Stream[ConnectionIO, Long] = 
  Cars.RowWithoutAutogenerated.insert
    .updateManyWithGeneratedKeys(Cars.idCol)  // sqlDef for the generated key
    .apply(data)

// stream emits 1L, 2L  (the auto-generated ids)
```

--------------------------------

### Type-Safe UPDATE with `updateTable`

Source: https://context7.com/arturaz/doobie-typesafe/llms.txt

`updateTable` generates `UPDATE table SET col1 = ?, col2 = ?` from the same `(Fragment, Fragment)` pairs used in `insertInto`, ensuring consistency between insert and update logic.

```scala
import doobie.*
import doobie.implicits.*
import cats.data.NonEmptyVector

// Vararg form
val update1 = sql"""
  ${Users.updateTable(
    Users.firstName --> "Jane",
    Users.lastName  --> "Smith"
  )} WHERE ${Users.id === 42}
"""
// SQL: UPDATE users SET first_name = ?, last_name = ? WHERE id = ?

// Collection form
val update2 = sql"""
  ${Users.updateTable(NonEmptyVector.of(
    Users.firstName --> "Jane",
    Users.lastName  --> "Smith"
  ))} WHERE ${Users.id === 42}
"""

// Composite expansion
val update3 = sql"""
  ${Users.updateTable(Users.Names ==> Users.Names("Jane", "Smith", Some("JS")))}
  WHERE ${Users.id === 42}
"""
```

--------------------------------

### Type-Safe Equality Query

Source: https://github.com/arturaz/doobie-typesafe/blob/main/docs/002_querying.md

Constructs a type-safe SQL query to select users based on their ID using the equality operator. It demonstrates how to use the `===` operator for comparisons.

```scala
val querySql = sql"SELECT $columns FROM $t WHERE ${t.id === 42}"
val query = querySql.queryOf(columns)
```

--------------------------------

### Define Composite Columns for Names

Source: https://github.com/arturaz/doobie-typesafe/blob/main/docs/001_table_definitions.md

Bundles first_name, last_name, and nickname columns into a Scala case class 'Names'. Requires doobie and doobie.implicits imports.

```scala
import doobie.*
import doobie.implicits.*

case class Names(firstName: String, lastName: String, nickname: Option[String])

lazy val names: SQLDefinition[Names] = Composite((
  Users.firstName.sqlDef, Users.lastName.sqlDef, Users.nickname.sqlDef
))(Names.apply)(Tuple.fromProductTyped)
```

--------------------------------

### Perform Type-Safe Inner Join

Source: https://github.com/arturaz/doobie-typesafe/blob/main/docs/002_querying.md

Constructs a type-safe SQL query to perform an inner join between the Users and Addresses tables. It demonstrates aliasing tables and columns to prevent name clashes and using the `===` operator for join conditions.

```scala
import doobie.*
import doobie.implicits.*

val id = 42

// Construct the prefixed versions of the tables. They will be known as `u` and `a` in the query.
val u = Users as "u"
val a = Addresses as "a"

// Construct the prefixed versions of the columns to prevent name clashes between the tables.
// They will be known as `u.first_name`, `u.last_name`, `a.street` and `a.city` in the query.
val columns = Columns((u(_.firstName), u(_.lastName), a(_.street), a(_.city)))

// You can join the tables using the `===` operator as well.
val querySql =
  sql"SELECT $columns FROM $u INNER JOIN $a ON ${u(_.id) === a(_.userId)} WHERE ${u(_.id) === id}"
val query = querySql.queryOf(columns)
```

--------------------------------

### Typed Multi-Column Select Lists with Columns[A]

Source: https://context7.com/arturaz/doobie-typesafe/llms.txt

Columns[A] wraps a tuple of TypedMultiFragment values into a single SQL fragment for SELECT clauses. It's used with queryOf to produce a Query0[A] with inferred return types.

```scala
import doobie.*
import doobie.implicits.*
import Users as t

// Select two columns → query returns (String, String)
val columns = Columns((t.firstName, t.lastName))
val query: Query0[(String, String)] = 
  sql"SELECT $columns FROM $t WHERE ${t.id === 42}".queryOf(columns)

// Single column → query returns String
val singleCol = Columns(t.firstName)
val q2: Query0[String] = sql"SELECT $singleCol FROM $t".queryOf(singleCol)

// Up to 22 columns are supported with full type inference
val wide = Columns((t.id, t.firstName, t.lastName, t.nickname))
// → Query0[(Int, String, String, Option[String])]
val q3 = sql"SELECT $wide FROM $t".queryOf(wide)
```

--------------------------------

### Composite.readOnly - Read-Only Composite

Source: https://context7.com/arturaz/doobie-typesafe/llms.txt

Creates a `SQLDefinitionRead[R]` for read-only operations when you only need to retrieve data. It uses `SQLDefinitionRead` members obtained via `.sqlDefr`, which is useful for projections involving columns from joined tables.

```APIDOC
## `Composite.readOnly` — Read-Only Composite

When you only need to read data (not write), `Composite.readOnly` creates a `SQLDefinitionRead[R]` from `SQLDefinitionRead` members obtained via `.sqlDefr`. This is useful for projections that mix column types from joined tables.

```scala
import doobie.*
import doobie.implicits.*

val persons = Person `as` "person"
val pets    = Pets   `as` "pet"

case class Result(name: String, pet: Option[Pets.Row])

// .sqlDefr widens Column/SQLDefinition to SQLDefinitionRead
val composite = Composite.readOnly(
  (persons(_.nameCol).sqlDefr, pets(_.Row.option).sqlDefr)
)(Result.apply)

val columns = Columns(composite)
val query   = sql"""
  SELECT $columns
  FROM   $persons
  LEFT JOIN $pets ON ${persons(_.nameCol) === pets(_ => nameCol)}
""".queryOf(columns)
// query: Query0[Result]
```
```

--------------------------------

### Define Address Table Schema

Source: https://github.com/arturaz/doobie-typesafe/blob/main/docs/002_querying.md

Defines the schema for the 'addresses' table, including columns for id, userId, street, and city. This is used in conjunction with the Users table for join operations.

```scala
import doobie.*

object Addresses extends TableDefinition("addresses") {
  val id: Column[Int] = Column("id")
  val userId: Column[Int] = Column("user_id")
  val street: Column[String] = Column("street")
  val city: Column[String] = Column("city")
  
  case class Address(street: String, city: String)
  object Address extends WithSQLDefinition[Address](Composite((
    street.sqlDef, city.sqlDef
  )))(Address.apply)(Tuple.fromProductTyped)
}
```

--------------------------------

### Insert Single Row Using Case Class

Source: https://github.com/arturaz/doobie-typesafe/blob/main/docs/003_inserting.md

Insert a single row by mapping a case class to the table definition. This provides a type-safe way to insert data.

```scala
// Insert single row.
Users.insertInto(Users.Row ==> Users.Row(42, Users.Names("John", "Doe", Some("Johnny"))))
```

--------------------------------

### Insert Using Composite Columns

Source: https://github.com/arturaz/doobie-typesafe/blob/main/docs/003_inserting.md

Insert data using composite columns, which group related fields. This is useful for inserting structured data like addresses or names.

```scala
import cats.data.NonEmptyVector

Users.insertInto(NonEmptyVector.of(
  Users.id --> 42,
) ++: (Users.Names ==> Users.Names("John", "Doe", Some("Johnny"))))
```

--------------------------------

### Type-Safe Inequality Query

Source: https://github.com/arturaz/doobie-typesafe/blob/main/docs/002_querying.md

Constructs a type-safe SQL query to select users whose ID does not match a specific value. It uses the `!==` operator for inequality checks.

```scala
val notEqualQuerySql = sql"SELECT $columns FROM $t WHERE ${t.id !== 42}"
val notEqualQuery = querySql.queryOf(columns)
```

--------------------------------

### Define Read-Only Composite

Source: https://context7.com/arturaz/doobie-typesafe/llms.txt

Use `Composite.readOnly` when only reading data is required. It creates a `SQLDefinitionRead[R]` from `SQLDefinitionRead` members obtained via `.sqlDefr`. This is useful for projections mixing column types from joined tables.

```scala
import doobie.*
import doobie.implicits.*

val persons = Person `as` "person"
val pets    = Pets   `as` "pet"

case class Result(name: String, pet: Option[Pets.Row])

// .sqlDefr widens Column/SQLDefinition to SQLDefinitionRead
val composite = Composite.readOnly(
  (persons(_.nameCol).sqlDefr, pets(_.Row.option).sqlDefr)
)(Result.apply)

val columns = Columns(composite)
val query   = sql"""
  SELECT $columns
  FROM   $persons
  LEFT JOIN $pets ON ${persons(_.nameCol) === pets(_ => nameCol)}
""".queryOf(columns)
// query: Query0[Result]
```

--------------------------------

### Define Read-only Composite Columns

Source: https://github.com/arturaz/doobie-typesafe/blob/main/docs/001_table_definitions.md

Creates a read-only composite column definition for 'Names' using 'Composite.readOnly'. Requires members to be SQLDefinitionRead.

```scala
Composite.readOnly((
    Users.firstName.sqlDefr, Users.lastName.sqlDefr, Users.nickname.sqlDefr
))(Users.Names.apply)
```

--------------------------------

### Type-Safe Column Operations with Column[A]

Source: https://context7.com/arturaz/doobie-typesafe/llms.txt

Column[A] provides type-safe operators for building WHERE clause fragments, column binding for INSERT/UPDATE, and option-specific helpers. It automatically handles NULL comparisons.

```scala
import doobie.*
import doobie.implicits.*
import Users.*

// Equality / inequality (handles NULL automatically via IS NULL / IS NOT NULL)
val eqFrag:    Fragment = id === 42         // "id = ?"
val neqFrag:   Fragment = id !== 42         // "id <> ?"
val nullFrag:  Fragment = nickname === None  // "nickname IS NULL"
val nnullFrag: Fragment = nickname !== None  // "nickname IS NOT NULL"
val someFrag:  Fragment = nickname === Some("Johnny") // "nickname = ?"

// Comparisons
val ltFrag:  Fragment = id < 100
val gteFrag: Fragment = id >= 10

// Set membership — returns FALSE when collection is empty
val inFrag:    Fragment = id.in(Vector(1, 2, 3))        // "id IN (?,?,?)"
val notInFrag: Fragment = id.notIn(Set(4, 5))           // "id NOT IN (?,?)"

// Column binding for INSERT/UPDATE — produces (columnName, value) pairs
val binding: (Fragment, Fragment)                       = id --> 42
val bindings: NonEmptyVector[(Fragment, Fragment)]      = id ==> 42

// Option-specific helpers
val isNull:    Fragment = nickname.isNull
val isNotNull: Fragment = nickname.isNotNull
val setNull:   Fragment = nickname.setToNull
```

--------------------------------

### Type-Safe Set Membership Query

Source: https://github.com/arturaz/doobie-typesafe/blob/main/docs/002_querying.md

Constructs a type-safe SQL query to select users whose IDs are within a given vector of values. It utilizes the `in` operator for set membership.

```scala
val multiQuerySql = sql"SELECT $columns FROM $t WHERE ${t.id in Vector(1, 2, 3)}"
val multiQuery = multiQuerySql.queryOf(columns)
```

--------------------------------

### Update Single Columns

Source: https://github.com/arturaz/doobie-typesafe/blob/main/docs/004_updating.md

Use `updateTable` with individual column assignments to update specific fields. Ensure a WHERE clause is provided to target the correct rows.

```scala
import cats.data.NonEmptyVector
import doobie.*
import doobie.implicits.*


sql""".
${Users.updateTable(
  Users.firstName --> "John",
  Users.lastName --> "Doe"
)} WHERE ${Users.id === 42}
"""

// Or using a collection:
sql""".
${Users.updateTable(NonEmptyVector.of(
  Users.firstName --> "John",
  Users.lastName --> "Doe"
))} WHERE ${Users.id === 42}
"""
```

--------------------------------

### Query with Composite Column in Columns

Source: https://github.com/arturaz/doobie-typesafe/blob/main/docs/001_table_definitions.md

Uses a composite column definition within a larger Columns definition for a SQL query. Requires doobie and doobie.implicits imports.

```scala
import doobie.*
import doobie.implicits.*

val id = 42
import Users as t
val columns = Columns((t.id, names))
val querySql = sql"SELECT $columns FROM $t WHERE ${t.id === id}"
val query = querySql.queryOf(columns)
```

--------------------------------

### Type-Safe Not In Set Query

Source: https://github.com/arturaz/doobie-typesafe/blob/main/docs/002_querying.md

Constructs a type-safe SQL query to select users whose IDs are not present in a given vector of values. It uses the `notIn` operator.

```scala
val notInMultiQuerySql = sql"SELECT $columns FROM $t WHERE ${t.id notIn Vector(1, 2, 3)}"
val notInMultiQuery = multiQuerySql.queryOf(columns)
```

--------------------------------

### Update Single Row Using Case Class

Source: https://github.com/arturaz/doobie-typesafe/blob/main/docs/003_inserting.md

Update a single row using a case class. This method is suitable for modifying existing records.

```scala
Users.Row.insert.toUpdate0(Users.Row(42, Users.Names("John", "Doe", Some("Johnny"))))
```

--------------------------------

### Update Composite Columns

Source: https://github.com/arturaz/doobie-typesafe/blob/main/docs/004_updating.md

Update composite columns by providing a `doobie.util.composite.Composite` instance. This is useful for updating related fields together.

```scala
import cats.data.NonEmptyVector
import doobie.*
import doobie.implicits.*

sql""".
${Users.updateTable(Users.Names ==> Users.Names("John", "Doe", Some("Fast Johnny")))}
WHERE ${Users.id === 42}
"""
```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.