### Verify Skunk Setup with IOApp

Source: https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/tutorial/Setup.md

A minimal Cats Effect IOApp that connects to a PostgreSQL database using Skunk, queries the current date, and prints it to the console. It demonstrates basic Skunk session management and query execution.

```scala
import cats.effect._
import skunk._
import skunk.implicits._
import skunk.codec.all._
import org.typelevel.otel4s.trace.Tracer
import org.typelevel.otel4s.metrics.Meter

object Hello extends IOApp {

  implicit val tracer: Tracer[IO] = Tracer.noop              // (1)
  implicit val meter: Meter[IO] = Meter.noop

  val session: Resource[IO, Session[IO]] =
    Session.Builder[IO]                                      // (2)
      .withHost("localhost")
      .withPort(5432)
      .withUserAndPassword("jimmy", "banana")
      .withDatabase("world")
      .single

  def run(args: List[String]): IO[ExitCode] =
    session.use { s =>                                       // (3)
      for {
        d <- s.unique(sql"select current_date".query(date))  // (4)
        _ <- IO.println(s"The current date is $d.")
      } yield ExitCode.Success
    }

}
```

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

### Execute Skunk Full Example

Source: https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/tutorial/Command.md

This code snippet executes the `CommandExample` application defined previously and captures its output. It uses `unsafeRunSyncWithRedirect` to run the IO effect and display the results.

```scala
println("```")
import skunk.mdocext._
CommandExample.run(Nil).unsafeRunSyncWithRedirect()
println("```")
```

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

### Skunk Full Example Application

Source: https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/tutorial/Command.md

An `IOApp` demonstrating the usage of `PetService` with Skunk. It sets up a database session, creates a temporary table, inserts data, and retrieves it, printing the results to the console.

```scala
object CommandExample extends IOApp {

  implicit val tracer: Tracer[IO] = Tracer.noop
  implicit val meter: Meter[IO] = Meter.noop

  // a source of sessions
  val session: Resource[IO, Session[IO]] =
    Session.Builder[IO]
      .withUserAndPassword("jimmy", "banana")
      .withDatabase("world")
      .single

  // a resource that creates and drops a temporary table
  def withPetsTable(s: Session[IO]): Resource[IO, Unit] = {
    val alloc = s.execute(sql"CREATE TEMP TABLE pets (name varchar, age int2)".command).void
    val free  = s.execute(sql"DROP TABLE pets".command).void
    Resource.make(alloc)(_ => free)
  }

  // some sample data
  val bob     = Pet("Bob", 12)
  val beagles = List(Pet("John", 2), Pet("George", 3), Pet("Paul", 6), Pet("Ringo", 3))

  // our entry point
  def run(args: List[String]): IO[ExitCode] =
    session.flatTap(withPetsTable).map(PetService.fromSession(_)).use { s =>
      for {
        _  <- s.insert(bob)
        _  <- s.insert(beagles)
        ps <- s.selectAll
        _  <- ps.traverse(p => IO.println(p))
      } yield ExitCode.Success
    }

}
```

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

### Define and Execute Parameterized Commands

Source: https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/tutorial/Command.md

Demonstrates defining an extended command with parameters and executing it using prepared statements. Includes examples of sequential execution, batch processing with traverse, and streaming with fs2.

```scala
val c: Command[String] = sql"DELETE FROM country WHERE name = $varchar".command

// Sequential execution
s.prepare(c).flatMap { pc =>
  pc.execute("xyzzy") *>
  pc.execute("fnord") *>
  pc.execute("blech")
}

// Batch execution with traverse
s.prepare(c).flatMap { pc =>
  List("xyzzy", "fnord", "blech").traverse(s => pc.execute(s))
}

// Streaming execution
Stream.eval(s.prepare(c)).flatMap { pc =>
  Stream("xyzzy", "fnord", "blech").through(pc.pipe)
}
```

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

### Execute Service Operations in IOApp

Source: https://context7.com/typelevel/skunk/llms.txt

Demonstrates how to initialize a Skunk session and run service operations within an IOApp. This example shows resource management for the session and the service, followed by executing queries.

```scala
object ServiceApp extends IOApp {
  val session: Resource[IO, Session[IO]] = 
    Session.Builder[IO]
      .withUserAndPassword("jimmy", "banana")
      .withDatabase("world")
      .single

  val service: Resource[IO, CountryService[IO]] = 
    session.evalMap(CountryService.fromSession(_))

  def run(args: List[String]): IO[ExitCode] =
    service.use { svc =>
      for {
        ts   <- svc.currentTimestamp
        _    <- IO.println(s"Current time: $ts")
        usa  <- svc.findByCode("USA")
        _    <- IO.println(s"USA: $usa")
        _    <- svc.findByName("U%").evalMap(c => IO.println(c)).compile.drain
      } yield ExitCode.Success
    }
}
```

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

### Install Skunk Dependencies

Source: https://context7.com/typelevel/skunk/llms.txt

Add the core Skunk library and optional Circe JSON support to your Scala project's build configuration.

```scala
libraryDependencies += "org.tpolecat" %% "skunk-core" % "<version>"

// For JSON support with Circe
libraryDependencies += "org.tpolecat" %% "skunk-circe" % "<version>"
```

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

### Create SQL commands with list parameters in Scala

Source: https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/tutorial/Command.md

Demonstrates how to use the .list combinator to handle variable-length parameters in SQL queries. This includes examples for simple IN clauses and bulk INSERT operations using Skunk's encoder combinators.

```scala
def deleteMany(n: Int): Command[List[String]] =
  sql"DELETE FROM country WHERE name IN (${varchar.list(n)})".command

val delete3 = deleteMany(3)

def insertMany(n: Int): Command[List[(String, Short)]] = {
  val enc = (varchar ~ int2).values.list(n)
  sql"INSERT INTO pets VALUES $enc".command
}

val insert3 = insertMany(3)
```

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

### Create PostgreSQL Sessions and Pools

Source: https://context7.com/typelevel/skunk/llms.txt

Demonstrates how to configure and manage database connections using the Session.Builder API. Includes examples for both single sessions and connection pools with SSL support.

```scala
import cats.effect._
import skunk._
import skunk.implicits._
import skunk.codec.all._
import org.typelevel.otel4s.trace.Tracer
import org.typelevel.otel4s.metrics.Meter

object DatabaseApp extends IOApp {

  implicit val tracer: Tracer[IO] = Tracer.noop
  implicit val meter: Meter[IO] = Meter.noop

  // Single session resource
  val session: Resource[IO, Session[IO]] =
    Session.Builder[IO]
      .withHost("localhost")
      .withPort(5432)
      .withUserAndPassword("jimmy", "banana")
      .withDatabase("world")
      .single

  // Session pool for concurrent access
  val sessionPool: Resource[IO, Resource[IO, Session[IO]]] =
    Session.Builder[IO]
      .withUserAndPassword("jimmy", "banana")
      .withDatabase("world")
      .withSSL(SSL.System)  // Enable SSL
      .withMax(10)          // Pool size
      .pooled

  def run(args: List[String]): IO[ExitCode] =
    session.use { s =>
      for {
        d <- s.unique(sql"SELECT current_date".query(date))
        _ <- IO.println(s"The current date is $d.")
      } yield ExitCode.Success
    }
}
```

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

### Perform Parameterized Queries with Skunk

Source: https://context7.com/typelevel/skunk/llms.txt

Demonstrates how to define queries with single or multiple parameters using Skunk's DSL. It includes examples for streaming results, fetching unique records, and handling optional results using prepared statements.

```scala
import cats.effect._
import skunk._
import skunk.implicits._
import skunk.codec.all._
import fs2.Stream

case class Country(name: String, population: Int)

val byNamePattern: Query[String, Country] =
  sql"
    SELECT name, population
    FROM   country
    WHERE  name LIKE $varchar
  ".query(varchar *: int4).to[Country]

val byNameAndPop: Query[String *: Int *: EmptyTuple, Country] =
  sql"
    SELECT name, population
    FROM   country
    WHERE  name LIKE $varchar
    AND    population < $int4
  ".query(varchar *: int4).to[Country]

def streamCountries(s: Session[IO]): Stream[IO, Country] =
  Stream.eval(s.prepare(byNamePattern)).flatMap { ps =>
    ps.stream("U%", 64)
  }

def executeQuery(s: Session[IO]): IO[Unit] =
  s.prepare(byNamePattern).flatMap { ps =>
    for {
      list   <- ps.stream("A%", 32).compile.toList
      single <- ps.unique("United States")
      maybe  <- ps.option("Nonexistent")
    } yield ()
  }
```

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

### Skunk Transactions: Basic, Advanced, and Isolated

Source: https://context7.com/typelevel/skunk/llms.txt

Demonstrates how to wrap database operations in transactions with automatic commit/rollback. Includes examples for basic transactions, advanced transactions with savepoints for partial rollbacks, and transactions with custom isolation levels and access modes.

```scala
import cats.effect._
import skunk._
import skunk.implicits._
import skunk.codec.all._

case class Pet(name: String, age: Short)

def transactionExample(s: Session[IO]): IO[Unit] = {
  val insertPet: Command[Pet] =
    sql"INSERT INTO pets VALUES ($varchar, $int2)".command.to[Pet]

  // Basic transaction - auto commit on success, rollback on error/cancel
  s.transaction.use { xa =>
    s.prepare(insertPet).flatMap { pc =>
      pc.execute(Pet("Fluffy", 3)) *> 
      pc.execute(Pet("Whiskers", 5))
    }
  }
}

// Advanced transaction with savepoints for partial rollback
def advancedTransaction(s: Session[IO]): IO[Unit] = {
  val insertPet: Command[Pet] =
    sql"INSERT INTO pets VALUES ($varchar, $int2)".command.to[Pet]

  s.transaction.use { xa =>
    s.prepare(insertPet).flatMap { pc =>
      List(Pet("Alice", 3), Pet("Bob", 5), Pet("Bob", 7)).traverse_ { pet =>
        for {
          sp <- xa.savepoint  // Create savepoint before each insert
          _  <- pc.execute(pet).recoverWith {
                  case SqlState.UniqueViolation(ex) =>
                    IO.println(s"Duplicate: ${pet.name}, rolling back...") *> 
                    xa.rollback(sp)  // Rollback to savepoint, continue transaction
                }
        } yield ()
      }
    }
  }
}

// Transaction with custom isolation level
def isolatedTransaction(s: Session[IO]): IO[Unit] = {
  import skunk.data.TransactionIsolationLevel
  import skunk.data.TransactionAccessMode

  s.transaction(
    TransactionIsolationLevel.Serializable,
    TransactionAccessMode.ReadOnly
  ).use { xa =>
    s.execute(sql"SELECT count(*) FROM pets".query(int8))
      .flatMap(counts => IO.println(s"Count: ${counts.head}"))
  }
}

```

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

### Execute Multi-Parameter Query with Stream

Source: https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/tutorial/Query.md

Executes a multi-parameter query using a prepared statement and streams the results. This example demonstrates passing a tuple of values corresponding to the defined parameters and streaming the results in blocks.

```scala
// assume s: Session[IO]
s.prepare(f).flatMap { ps =>
  ps.stream(("U%", 2000000), 64)
    .evalMap(c => IO.println(c))
    .compile
    .drain
} // IO[Unit]
```

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

### Execute Parameterized Query with Stream (Prepared Statement)

Source: https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/tutorial/Query.md

Executes a parameterized query using a prepared statement and streams the results. This example demonstrates how to prepare a statement, stream results with a given parameter, and process them using fs2. It utilizes constant space for streaming.

```scala
// assume s: Session[IO]
s.prepare(e).flatMap { ps =>
  ps.stream("U%", 64)
    .evalMap(c => IO.println(c))
    .compile
    .drain
} // IO[Unit]
```

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

### Build Skunk World Docker Image

Source: https://github.com/typelevel/skunk/blob/main/world/README.md

Builds the Skunk world Docker image and tags it with 'tpolecat/skunk-world:latest'. This command requires Docker to be installed and configured.

```bash
docker build -t tpolecat/skunk-world:latest .
```

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

### Execute test suites using SBT

Source: https://github.com/typelevel/skunk/blob/main/CONTRIBUTING.md

Commands to trigger the test suite via SBT. Users can run the entire suite or target specific test classes for faster iteration.

```sbt
sbt test
```

```sbt
sbt
test:testOnly tests.simulation.StartupSimTest
```

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

### Manage local test network infrastructure

Source: https://github.com/typelevel/skunk/blob/main/CONTRIBUTING.md

Commands to initialize and terminate the local network environment required for running Skunk tests. Ensure no conflicting local Postgres instances are running before execution.

```bash
./bin/local up
./bin/local down
```

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

### Use 'list' Combinator with 'values'

Source: https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/reference/Encoders.md

Combines the 'values' combinator with the 'list' combinator to create an encoder for a list of values, each enclosed in parentheses. This example specifies a list of 3 items.

```scala
val enc = (varchar ~ int4).values

sql"INSERT INTO person (name, age) VALUES ${enc.list(3)}"
```

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

### Implement PetService with Skunk

Source: https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/tutorial/Command.md

Provides a concrete implementation of `PetService` using Skunk, defining SQL commands for insertion and queries for selection, and a constructor to create the service from a Skunk session.

```scala
// a companion with a constructor
object PetService {

  // command to insert a pet
  private val insertOne: Command[Pet] =
    sql"INSERT INTO pets VALUES ($varchar, $int2)"
      .command
      .to[Pet]

  // command to insert a specific list of pets
  private def insertMany(ps: List[Pet]): Command[ps.type] = {
    val enc = (varchar *: int2).to[Pet].values.list(ps)
    sql"INSERT INTO pets VALUES $enc".command
  }

  // query to select all pets
  private val all: Query[Void, Pet] =
    sql"SELECT name, age FROM pets"
      .query(varchar *: int2)
      .to[Pet]

  // construct a PetService
  def fromSession[F[_]: Monad](s: Session[F]): PetService[F] =
    new PetService[F] {
      def insert(pet: Pet): F[Unit] = s.prepare(insertOne).flatMap(_.execute(pet)).void
      def insert(ps: List[Pet]): F[Unit] = s.prepare(insertMany(ps)).flatMap(_.execute(ps)).void
      def selectAll: F[List[Pet]] = s.execute(all)
    }

}
```

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

### Prepare and Execute Applied Fragments

Source: https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/reference/Fragments.md

Demonstrates the process of preparing and executing an `AppliedFragment` using a Skunk `Session`. It shows how to extract the underlying `fragment` and `argument` from an `AppliedFragment` to create a `Query` for execution.

```scala
import cats.effect._
import skunk._
import skunk.implicits._
import skunk.codec.all._

def usage(s: Session[IO]) = {
  val f = countryQuery(Some("Un%"), None) // AppliedFragment
  val q = f.fragment.query(varchar)       // Query[f.A, String]
  s.prepare(q).flatMap(_.stream(f.argument, 64).compile.to(List))
}
```

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

### Contramap and Map Commands to Case Classes

Source: https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/tutorial/Command.md

Shows how to map custom case classes to command parameters. Uses contramap for manual mapping or the .to method for automatic isomorphism when field orders match.

```scala
case class Info(code: String, population: Int)

val update2: Command[Info] = sql"""
    UPDATE country
    SET    population = $int4
    WHERE  code = ${bpchar(3)}
  """.command
     .contramap { case Info(code, pop) => pop *: code *: EmptyTuple }

case class Info2(population: Int, code: String)
val update3: Command[Info2] = sql"""
    UPDATE country
    SET    population = $int4
    WHERE  code = ${bpchar(3)}
  """.command
     .to[Info2]
```

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

### Map PostgreSQL JSON Types to io.circe.Json

Source: https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/reference/SchemaTypes.md

Demonstrates mapping PostgreSQL's `json` and `jsonb` types to `io.circe.Json`. It shows how to create codecs for basic types combined with JSON.

```scala
// Codec for Int ~ Json as (int4, jsonb)
val codec1: Codec[Int ~ Json] = int4 ~ jsonb
```

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

### Listening to a Channel

Source: https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/tutorial/Channels.md

Shows how to create an fs2 Stream to listen for notifications on a channel. It requires a buffer size to manage enqueued messages and automatically handles LISTEN/UNLISTEN commands.

```scala
val nbs = ch.listen(1024) // Stream[IO, Notification[String]]
```

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

### Execute SQL Commands with Skunk

Source: https://context7.com/typelevel/skunk/llms.txt

Shows how to perform DDL and DML operations like INSERT, UPDATE, and DELETE. Covers both simple commands and prepared commands for repeated execution or batch processing.

```scala
import cats.effect._
import skunk._
import skunk.implicits._
import skunk.codec.all._
import skunk.data.Completion

case class Pet(name: String, age: Short)

val setTimezone: Command[Void] = sql"SET TIMEZONE TO 'UTC'".command

val insertPet: Command[Pet] = sql"INSERT INTO pets (name, age) VALUES ($varchar, $int2)".command.to[Pet]

val deletePet: Command[String] = sql"DELETE FROM pets WHERE name = $varchar".command

case class UpdateInfo(population: Int, code: String)
val updateCountry: Command[UpdateInfo] = sql"
    UPDATE country
    SET    population = $int4
    WHERE  code = ${bpchar(3)}
  ".command.to[UpdateInfo]

def executeCommands(s: Session[IO]): IO[Unit] =
  for {
    _ <- s.execute(setTimezone)
    _ <- s.prepare(insertPet).flatMap { pc =>
      for {
        _ <- pc.execute(Pet("Bob", 3))
        _ <- pc.execute(Pet("Alice", 5))
      } yield ()
    }
    _ <- s.prepare(deletePet).flatMap { pc =>
      List("Bob", "Alice").traverse(name => pc.execute(name))
    }
  } yield ()
```

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

### Constructing a Skunk Channel

Source: https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/tutorial/Channels.md

Demonstrates how to create a Channel instance from a Skunk Session using an Identifier. The resulting channel allows for bidirectional communication with a specific Postgres channel.

```scala
val ch = s.channel(id"my_channel") // Channel[IO, String, String]
```

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

### Construct and Execute Simple Queries in Skunk

Source: https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/tutorial/Query.md

Demonstrates how to define a SQL query using the sql interpolator and execute it against a database session. It covers simple queries without parameters and the use of decoders to map SQL types to Scala types.

```scala
val a: Query[Void, String] = sql"SELECT name FROM country".query(varchar)

// Assuming s: Session[IO]
s.execute(a) // Returns IO[List[String]]
```

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

### Execute a Simple Command

Source: https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/tutorial/Command.md

Executes a simple command using the session's execute method. It returns an IO containing a Completion object representing the database response.

```scala
s.execute(a) // IO[Completion]
```

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

### Construct Dynamic SQL Queries with Applied Fragments

Source: https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/reference/Fragments.md

Shows how to use `AppliedFragment` to construct SQL queries dynamically. `AppliedFragment` combines a `Fragment` with its arguments and forms a monoid, allowing for flexible query building, especially with optional conditions.

```scala
import cats.syntax.all._
import cats.effect._
import skunk._
import skunk.implicits._
import skunk.codec.all._

def countryQuery(name: Option[String], pop: Option[Int]): AppliedFragment = {
  val base = sql"SELECT code FROM country"
  val nameLike = sql"name LIKE $varchar"
  val popGreaterThan = sql"population > $int4"

  val conds: List[AppliedFragment] =
    List(
      name.map(nameLike),
      pop .map(popGreaterThan),
    ).flatten

  val filter =
    if (conds.isEmpty) AppliedFragment.empty
    else conds.foldSmash(void" WHERE ", void" AND ", AppliedFragment.empty)

  base(Void) |+| filter
}

countryQuery(Some("Un%"), Some(99999)).fragment.sql
countryQuery(Some("Un%"), None).fragment.sql
countryQuery(None, None).fragment.sql
```

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

### Create Basic SQL Fragments

Source: https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/reference/Fragments.md

Demonstrates the creation of Skunk SQL fragments using the `sql` interpolator. Fragments can be created with no parameters or with interpolated encoders for specific data types.

```scala
import skunk._
import skunk.implicits._
import skunk.codec.all._

// A fragment with no interpolated encoders.
val f1 = sql"SELECT 42"

// A fragment with an interpolated encoder.
val f2 = sql"SELECT foo FROM bar WHERE baz = $int8"
```

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

### Skunk SQL Fragments: Dynamic Query Building

Source: https://context7.com/typelevel/skunk/llms.txt

Demonstrates composing SQL fragments dynamically for conditional queries and complex filtering. Covers basic fragment composition, dynamic query building with AppliedFragment, and interpolating literal strings (with a caution about SQL injection).

```scala
import skunk._
import skunk.implicits._
import skunk.codec.all._

// Basic fragment composition
val selectPart = sql"SELECT code FROM country"
val wherePart = sql"WHERE population > $int4"
val combined = selectPart *: wherePart  // Fragment[Int]

// Dynamic query building with AppliedFragment
def countrySearch(
  name: Option[String],
  minPop: Option[Int],
  maxPop: Option[Int]
): AppliedFragment = {
  val base = sql"SELECT name, population FROM country"

  val conditions: List[AppliedFragment] = List(
    name.map(n => sql"name LIKE $varchar"(n)),
    minPop.map(p => sql"population > $int4"(p)),
    maxPop.map(p => sql"population < $int4"(p))
  ).flatten

  val filter =
    if (conditions.isEmpty) AppliedFragment.empty
    else conditions.foldSmash(void" WHERE ", void" AND ", AppliedFragment.empty)

  base(Void) |+| filter
}

// Usage
def executeSearch(s: Session[IO]): IO[List[(String, Int)]] = {
  val af = countrySearch(Some("U%"), Some(1000000), None)
  val query = af.fragment.query(varchar ~ int4)
  s.prepare(query).flatMap(_.stream(af.argument, 64).compile.toList)
}

// Interpolating literal strings (use with caution - SQL injection risk)
def dynamicTable(tableName: String): Fragment[Int] =
  sql"SELECT * FROM #$tableName WHERE id = $int4"

```

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

### Migrate Skunk Command Syntax

Source: https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/reference/TwiddleLists.md

Demonstrates the use of legacy twiddle syntax via an import and the updated tuple-based syntax for Skunk commands. The legacy approach uses the tilde operator for mapping, while the modern approach utilizes standard Scala tuples.

```scala
import skunk.feature.legacyCommandSyntax

val insertCityLegacy: Command[City] =
  sql"""
      INSERT INTO city
      VALUES ($int4, $varchar, ${bpchar(3)}, $varchar, $int4)
    """.command.contramap {
          c => c.id ~ c.name ~ c.code ~ c.district ~ c.pop
        }
```

```scala
val insertCity2: Command[City] =
  sql"""
      INSERT INTO city
      VALUES ($int4, $varchar, ${bpchar(3)}, $varchar, $int4)
    """.command.contramap {
          c => (c.id, c.name, c.code, c.district, c.pop)
        }
```

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

### Execute Simple SQL Queries

Source: https://context7.com/typelevel/skunk/llms.txt

Defines queries without parameters and demonstrates how to execute them against a session using different return types like List, Option, or unique values.

```scala
import skunk._
import skunk.implicits._
import skunk.codec.all._

// Single-column query
val countryNames: Query[Void, String] =
  sql"SELECT name FROM country".query(varchar)

// Multi-column query with twiddle list decoder
val countryData: Query[Void, String ~ Int] =
  sql"SELECT name, population FROM country".query(varchar ~ int4)
```

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

### Map Query Results to Case Classes

Source: https://context7.com/typelevel/skunk/llms.txt

Shows how to map database rows to Scala case classes using manual pattern matching, the 'to' method for automatic derivation, or reusable decoders.

```scala
import skunk._
import skunk.implicits._
import skunk.codec.all._

case class Country(name: String, code: String, population: Int)

// Manual mapping with pattern matching
val countryQuery1: Query[Void, Country] =
  sql"SELECT name, code, population FROM country"
    .query(varchar ~ bpchar(3) ~ int4)
    .map { case n ~ c ~ p => Country(n, c, p) }

// Automatic mapping with `to` (requires matching field order)
val countryQuery2: Query[Void, Country] =
  sql"SELECT name, code, population FROM country"
    .query(varchar *: bpchar(3) *: int4)
    .to[Country]

// Create a reusable decoder
val countryDecoder: Decoder[Country] =
  (varchar *: bpchar(3) *: int4).to[Country]

val countryQuery3: Query[Void, Country] =
  sql"SELECT name, code, population FROM country".query(countryDecoder)
```

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

### Run Skunk World with Docker Network for psql

Source: https://github.com/typelevel/skunk/blob/main/world/README.md

Creates a Docker network named 'skunknet', runs the Skunk world image connected to this network, and then connects to the database using `psql` from a separate container.

```bash
docker network create skunknet
docker run -p5432:5432 -d --name skunkdb --network skunknet tpolecat/skunk-world
docker network create skunknet
docker run -it --rm --network skunknet postgres psql -h skunkdb -U postgres
```

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

### Skunk Scala JSON Support with Circe

Source: https://context7.com/typelevel/skunk/llms.txt

Illustrates how to integrate Circe for handling JSON data within Skunk. This includes using generic JSON codecs and type-safe codecs for case classes, as well as commands for inserting JSON data.

```scala
import skunk._
import skunk.circe.codec.all._  // Import JSON codecs
import skunk.codec.all._
import io.circe.Json
import io.circe.generic.auto._

case class Metadata(tags: List[String], priority: Int)

// Generic JSON codec
val jsonQuery: Query[Void, Int ~ Json] =
  sql"SELECT id, data FROM documents".query(int4 ~ json)

// Typed JSON codec (requires Circe Encoder/Decoder)
val typedJsonQuery: Query[Void, Int ~ Metadata] =
  sql"SELECT id, metadata FROM documents".query(int4 ~ jsonb[Metadata])

// Insert JSON data
val insertDoc: Command[Int ~ Metadata] =
  sql"INSERT INTO documents (id, metadata) VALUES ($int4, ${jsonb[Metadata]})".command

```

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

### Execute Simple and Extended Queries with Skunk

Source: https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/tutorial/Query.md

This Scala code snippet demonstrates how to execute both simple and extended queries using the Skunk library. It sets up a database session, defines a simple query for the current timestamp and an extended query for country data with a text parameter. It then executes these queries and prints the results. Dependencies include cats-effect, skunk, and otel4s.

```scala
import cats.effect._
import skunk._
import skunk.implicits._
import skunk.codec.all._
import java.time.OffsetDateTime
implicit def dummyTrace: org.typelevel.otel4s.trace.Tracer[IO] = org.typelevel.otel4s.trace.Tracer.noop
implicit def dummyMeter: org.typelevel.otel4s.metrics.Meter[IO] = org.typelevel.otel4s.metrics.Meter.noop

object QueryExample extends IOApp {

  // a source of sessions
  val session: Resource[IO, Session[IO]] =
    Session.Builder[IO]
      .withUserAndPassword("jimmy", "banana")
      .withDatabase("world")
      .single

  // a data model
  case class Country(name: String, code: String, population: Int)

  // a simple query
  val simple: Query[Void, OffsetDateTime] =
    sql"select current_timestamp".query(timestamptz)

  // an extended query
  val extended: Query[String, Country] =
    sql"""
      SELECT name, code, population
      FROM   country
      WHERE  name like $text
    """.query(varchar *: bpchar(3) *: int4)
       .to[Country]

  // run our simple query
  def doSimple(s: Session[IO]): IO[Unit] =
    for {
      ts <- s.unique(simple) // we expect exactly one row
      _  <- IO.println(s"timestamp is $ts")
    } yield ()

  // run our extended query
  def doExtended(s: Session[IO]): IO[Unit] =
    s.prepare(extended).flatMap {
      ps =>
      ps.stream("U%", 32)
        .evalMap(c => IO.println(c))
        .compile
        .drain
    }

  // our entry point
  def run(args: List[String]): IO[ExitCode] =
    session.use {
      s =>
      for {
        _ <- doSimple(s)
        _ <- doExtended(s)
      } yield ExitCode.Success
    }

}
```

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

### Define a Simple Command

Source: https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/tutorial/Command.md

Constructs a simple SQL command with no parameters using the sql interpolator. The command is parameterized by Void as it requires no input.

```scala
val a: Command[Void] = sql"SET SEED TO 0.123".command
```

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

### Map Twiddle Lists to Case Classes

Source: https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/reference/TwiddleLists.md

Illustrates how to convert between Twiddle lists and case classes using manual pattern matching or the Iso typeclass provided by the Twiddles library.

```scala
case class Person(name: String, age: Int, active: Boolean)

// Manual mapping
def fromTwiddle(t: String *: Int *: Boolean *: EmptyTuple): Person =
  t match {
    case s *: n *: b *: EmptyTuple => Person(s, n, b)
  }

// Automatic mapping with Iso
val bob = Person("Bob", 42, true)
val iso = Iso.product[Person]
val twiddle = iso.to(bob)
val bob2 = iso.from(twiddle)
```

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

### Skunk Channels: LISTEN/NOTIFY for Real-time Notifications

Source: https://context7.com/typelevel/skunk/llms.txt

Utilizes PostgreSQL's pub/sub functionality for real-time notifications between sessions. Shows how to create a channel, send notifications, listen for incoming notifications as a stream, and stream query results to a channel.

```scala
import cats.effect._
import skunk._
import skunk.implicits._
import skunk.codec.all._
import skunk.data.Notification
import fs2.Stream

def channelExample(s: Session[IO]): IO[Unit] = {
  // Create a channel
  val channel = s.channel(id"my_channel")

  // Send a notification
  val notify: IO[Unit] = channel.notify("Hello, World!")

  // Listen for notifications (returns a stream)
  val listen: Stream[IO, Notification[String]] = 
    channel.listen(1024)  // Max queue size before blocking

  // Stream query results to a channel
  def streamToChannel: IO[Unit] = 
    s.prepare(sql"SELECT name FROM country".query(varchar)).flatMap { ps =>
      ps.stream(Void, 512)
        .through(s.channel(id"country_names"))
        .compile
        .drain
    }

  // Run listener concurrently
  listen
    .evalMap(n => IO.println(s"Received: ${n.value} from pid ${n.pid}"))
    .compile
    .drain
}

```

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

### Define and Implement a Skunk Service Interface

Source: https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/tutorial/Query.md

This snippet shows how to define a service trait for database operations and implement it using a Skunk Session. The implementation uses prepared statements for efficiency and provides a clean interface for the rest of the application.

```scala
import cats.syntax.all._
import cats.effect._
import skunk._
import skunk.implicits._
import skunk.codec.all._
import java.time.OffsetDateTime
import org.typelevel.otel4s.trace.Tracer
import org.typelevel.otel4s.metrics.Meter
import fs2.Stream
import cats.Applicative

case class Country(name: String, code: String, population: Int)

trait Service[F[_]] {
  def currentTimestamp: F[OffsetDateTime]
  def countriesByName(pat: String): Stream[F, Country]
}

object Service {
  private val timestamp: Query[Void, OffsetDateTime] = sql"select current_timestamp".query(timestamptz)
  private val countries: Query[String, Country] = sql"SELECT name, code, population FROM country WHERE name like $text".query(varchar *: bpchar(3) *: int4).to[Country]

  def fromSession[F[_]: Applicative](s: Session[F]): F[Service[F]] = s.prepare(countries).map {
    pq => new Service[F] {
      def currentTimestamp: F[OffsetDateTime] = s.unique(timestamp)
      def countriesByName(pat: String): Stream[F,Country] = pq.stream(pat, 32)
    }
  }
}

object QueryExample2 extends IOApp {
  implicit val tracer: Tracer[IO] = Tracer.noop
  implicit val meter: Meter[IO] = Meter.noop

  val session: Resource[IO, Session[IO]] = Session.Builder[IO].withUserAndPassword("jimmy", "banana").withDatabase("world").single
  val service: Resource[IO, Service[IO]] = session.evalMap(Service.fromSession(_))

  def run(args: List[String]): IO[ExitCode] = service.use { s =>
    for {
      ts <- s.currentTimestamp
      _  <- IO.println(s"timestamp is $ts")
      _  <- s.countriesByName("U%").evalMap(c => IO.println(c)).compile.drain
    } yield ExitCode.Success
  }
}
```

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

### Configure SSL for Skunk Session

Source: https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/reference/Sessions.md

Demonstrates how to configure SSL encryption when establishing a Skunk database session. It shows how to use system defaults or custom SSL contexts for secure connections.

```scala
Session.Builder[IO]
  .withUserAndPassword("jimmy", "banana")
  .withDatabase("world")
  .withSSL(SSL.System)  // Use SSL with the system default SSLContext
  .single
```

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

### Map Multi-Column Queries to Data Types

Source: https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/tutorial/Query.md

Shows how to handle multiple columns using twiddle lists and how to map these results into custom Scala case classes. This includes both mapping the query result directly and creating reusable decoders.

```scala
case class Country(name: String, population: Int)

// Mapping query results
val c: Query[Void, Country] = sql"SELECT name, population FROM country"
  .query(varchar ~ int4)
  .map { case n ~ p => Country(n, p) }

// Mapping decoder results
val country: Decoder[Country] = (varchar ~ int4).map { case (n, p) => Country(n, p) }
val d: Query[Void, Country] = sql"SELECT name, population FROM country".query(country)
```

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

### Run Skunk World Docker Image

Source: https://github.com/typelevel/skunk/blob/main/world/README.md

Runs the Skunk world Docker image, exposing port 5432 and detaching the container. Note that the database initialization takes a few seconds.

```bash
docker run -p5432:5432 -d tpolecat/skunk-world
```

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

### Adapt Codecs to Case Classes

Source: https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/reference/TwiddleLists.md

Demonstrates using the .to method on a Codec to automatically map a Twiddle list codec to a case class codec.

```scala
val codec = varchar *: int4 *: bool
val personCodec = codec.to[Person]
```

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

### Skunk Scala Codecs for PostgreSQL Types

Source: https://context7.com/typelevel/skunk/llms.txt

Demonstrates the use of Skunk's built-in codecs to map various PostgreSQL data types to their corresponding Scala types. This includes numeric, text, date/time, boolean, UUID, byte array, and array types. It also shows how to create nullable codecs and custom enum codecs.

```scala
import skunk._
import skunk.codec.all._
import skunk.data.{Arr, Type}
import java.time._
import java.util.UUID

// Numeric types
val shortCodec: Codec[Short] = int2           // smallint
val intCodec: Codec[Int] = int4               // integer
val longCodec: Codec[Long] = int8             // bigint
val floatCodec: Codec[Float] = float4         // real
val doubleCodec: Codec[Double] = float8       // double precision
val decimalCodec: Codec[BigDecimal] = numeric // numeric/decimal

// Text types
val varcharCodec: Codec[String] = varchar     // varchar (unbounded)
val varchar100: Codec[String] = varchar(100)  // varchar(100)
val charCodec: Codec[String] = bpchar(3)      // char(3)
val textCodec: Codec[String] = text           // text

// Date/time types
val dateCodec: Codec[LocalDate] = date
val timeCodec: Codec[LocalTime] = time
val timestampCodec: Codec[LocalDateTime] = timestamp
val timestamptzCodec: Codec[OffsetDateTime] = timestamptz
val intervalCodec: Codec[Duration] = interval

// Other types
val boolCodec: Codec[Boolean] = bool
val uuidCodec: Codec[UUID] = uuid
val bytesCodec: Codec[Array[Byte]] = bytea

// Nullable columns with .opt
val nullableInt: Codec[Option[Int]] = int4.opt
val nullableName: Codec[Option[String]] = varchar.opt

// Array types
val intArray: Codec[Arr[Int]] = _int4
val textArray: Codec[Arr[String]] = _text

// Custom enum type
sealed abstract class MyStatus(val label: String)
object MyStatus {
  case object Active extends MyStatus("active")
  case object Inactive extends MyStatus("inactive")
  val values = List(Active, Inactive)
  def fromLabel(s: String): Option[MyStatus] = values.find(_.label == s)
}

val statusCodec: Codec[MyStatus] =
  `enum`[MyStatus](_.label, MyStatus.fromLabel, Type("mystatus"))

```

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

### Import Skunk Encoder Components

Source: https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/reference/Encoders.md

Imports necessary components from the skunk library for working with encoders, including implicits and all codecs.

```scala
import skunk._
import skunk.implicits._
import skunk.codec.all._
```

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

### Define Queries with Twiddle Lists

Source: https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/reference/TwiddleLists.md

Demonstrates how to define Skunk Queries using the *: operator to construct parameter and row types. This approach allows for type-safe mapping of database columns to Scala types.

```scala
val q: Query[Short *: String *: String *: Int *: EmptyTuple] =
  sql"""
    SELECT name, age
    FROM   person
    WHERE  age < $int2
    AND    status = $
  """.query(varchar *: int4)
```

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

### Notifying a Channel

Source: https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/tutorial/Channels.md

Demonstrates sending a message to a channel using the notify method or by using the Channel as an fs2 Pipe to stream data into a Postgres channel.

```scala
ch.notify("hello") // IO[Unit]

s.prepare(sql"select name from country".query(varchar)).flatMap { ps =>
  ps.stream(Void, 512)
    .through(s.channel(id"country_names"))
    .compile
    .drain
}
```

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

### Print Default Skunk Session Parameters

Source: https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/reference/Sessions.md

This snippet demonstrates how to print the default session parameters used by Skunk. It iterates through a map of key-value pairs and formats them into a markdown table string.

```scala
println(Session.DefaultConnectionParameters.map { case (k, v) => s"| `$k` | `$v` |" } .mkString("\n"))
```

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

### Manage Skunk Session Resources and Transactions

Source: https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/tutorial/Transactions.md

Shows how to configure a Skunk session, manage temporary table lifecycles using Resources, and monitor transaction status changes.

```scala
object TransactionExample extends IOApp {
  val session: Resource[IO, Session[IO]] = Session.Builder[IO]
    .withUserAndPassword("jimmy", "banana")
    .withDatabase("world").single

  def withPetsTable(s: Session[IO]): Resource[IO, Unit] = {
    val alloc = s.execute(sql"CREATE TEMP TABLE pets (name varchar unique, age int2)".command).void
    val free  = s.execute(sql"DROP TABLE pets".command).void
    Resource.make(alloc)(_ => free)
  }

  def run(args: List[String]): IO[ExitCode] = {
    val resource = for {
      s  <- session
      _  <- withPetsTable(s)
      ps <- Resource.eval(PetService.fromSession(s))
    } yield ps

    resource.use { ps =>
      for {
        _   <- ps.tryInsertAll(List(Pet("Alice", 3), Pet("Bob", 42)))
        all <- ps.selectAll
      } yield ExitCode.Success
    }
  }
}
```

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

### Perform Bulk Inserts and List-based Operations

Source: https://context7.com/typelevel/skunk/llms.txt

Explains how to use list encoders to perform bulk inserts and dynamic IN clause queries. Demonstrates both dynamic list sizing and compile-time type-safe list handling.

```scala
import cats.effect._
import skunk._
import skunk.implicits._
import skunk.codec.all._

case class Pet(name: String, age: Short)

def insertMany(n: Int): Command[List[Pet]] = {
  val enc = (varchar *: int2).to[Pet].values.list(n)
  sql"INSERT INTO pets (name, age) VALUES $enc".command
}

def insertExactly(ps: List[Pet]): Command[ps.type] = {
  val enc = (varchar *: int2).to[Pet].values.list(ps)
  sql"INSERT INTO pets (name, age) VALUES $enc".command
}

def deleteMany(n: Int): Command[List[String]] =
  sql"DELETE FROM pets WHERE name IN (${varchar.list(n)})".command

def bulkInsert(s: Session[IO]): IO[Unit] = {
  val pets = List(Pet("John", 2), Pet("Paul", 6), Pet("George", 3))
  s.prepare(insertExactly(pets)).flatMap(_.execute(pets)).void
}
```

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

### Add skunk-circe Dependency for JSON Support

Source: https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/reference/SchemaTypes.md

Adds the skunk-circe module as a project dependency to enable JSON support in Skunk. This module provides codecs for PostgreSQL's json and jsonb types.

```scala
libraryDependencies += "org.tpolecat" %% "skunk-circe" % "@VERSION@"
```

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

### Construct and Type Twiddle Lists

Source: https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/reference/TwiddleLists.md

Shows the equivalence between standard Scala tuples and Twiddle lists. It highlights the use of the *: operator and EmptyTuple for building structures compatible with Skunk.

```scala
import org.typelevel.twiddles._ // required for Scala 2

val t1: (Short, String, String, Int) = (42.toShort, "Edgar", "Dijkstra", 100)
val t2: (Short, String, String, Int) = 42.toShort *: "Edgar" *: "Dijkstra" *: 100 *: EmptyTuple
val t3: Short *: String *: String *: Int *: EmptyTuple = 42.toShort *: "Edgar" *: "Dijkstra" *: 100 *: EmptyTuple
val t4: Short *: String *: String *: Int *: EmptyTuple = (42.toShort, "Edgar", "Dijkstra", 100)
```

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

### Implement Database Service Pattern with Skunk

Source: https://context7.com/typelevel/skunk/llms.txt

Defines a service trait for database operations and provides a constructor that prepares queries using a Skunk session. This pattern ensures efficient query execution by preparing statements once and exposing them through a clean, typed interface.

```scala
import cats.effect._
import cats.syntax.all._
import skunk._
import skunk.implicits._
import skunk.codec.all._
import fs2.Stream
import java.time.OffsetDateTime

case class Country(name: String, code: String, population: Int)

trait CountryService[F[_]] {
  def currentTimestamp: F[OffsetDateTime]
  def findByName(pattern: String): Stream[F, Country]
  def findByCode(code: String): F[Option[Country]]
}

object CountryService {
  private val timestampQuery: Query[Void, OffsetDateTime] = sql"SELECT current_timestamp".query(timestamptz)

  private val byNameQuery: Query[String, Country] = sql"""
      SELECT name, code, population FROM country
      WHERE name LIKE $text
    """.query(varchar *: bpchar(3) *: int4).to[Country]

  private val byCodeQuery: Query[String, Country] = sql"""
      SELECT name, code, population FROM country
      WHERE code = ${bpchar(3)}
    """.query(varchar *: bpchar(3) *: int4).to[Country]

  def fromSession[F[_]: cats.Applicative](s: Session[F]): F[CountryService[F]] =
    (s.prepare(byNameQuery), s.prepare(byCodeQuery)).mapN { (byName, byCode) =>
      new CountryService[F] {
        def currentTimestamp: F[OffsetDateTime] = s.unique(timestampQuery)
        def findByName(pattern: String): Stream[F, Country] = byName.stream(pattern, 64)
        def findByCode(code: String): F[Option[Country]] = byCode.option(code)
      }
    }
}
```