Skunk Documentation

repository·main·Indexed 23 days ago

A type-safe data access library specifically designed for Scala and PostgreSQL. It provides tools for interacting with Postgres databases, including Encoder and Decoder typeclasses for transforming Scala types to Postgres text-format data, a Channel API for Pub/Sub using LISTEN and NOTIFY, and SqlState extractors for handling PostgresErrorException.

Tokens: 21.7K
Snippets: 52
Records: 100
Agent score: 81%

What's inside Skunk

Overview of Skunk
main
Skunk is a data access library designed for Scala and PostgreSQL. It provides a way to interact with Postgres databases within the Scala ecosystem.
https://github.com/typelevel/skunk/blob/main/README.md
What is a Command in Skunk?
main
A Command is a SQL statement that does not return rows (e.g., INSERT, UPDATE, DELETE, or SET).
Commands are parameterized by their input type. A command with no parameters has the input type Void.
```
val a: Command[Void] =
  sql"SET SEED TO 0.123".command
```
https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/tutorial/Command.md

Choosing between Simple and Extended Command Protocols

main

When deciding how to execute a command, consider the following:

Protocol	Method	Use when...
Simple	`Session#execute`	Command has no parameters AND you only use it once per session.
Extended	`Session#prepare`	Command has parameters OR you intend to reuse the command multiple times per session.

Understand PostgreSQL transaction statuses in Skunk

main

A Skunk Session is always in one of three transaction states. You can monitor this state via the session.transactionStatus member, which is an fs2 Signal.

Status	Description
Idle	No transaction is currently in progress.
Active	A transaction is in progress and can proceed.
Error	An error has occurred. The transaction must be rolled back to a savepoint to continue, or rolled back entirely to terminate.

Understand Twiddle Lists for Query and Command parameters
main
Twiddle lists are tuples built incrementally using the *: operator. In Skunk, they are used as type arguments for Query and Command to represent parameter encoders and row decoders.
Scala 3 Behavior: *: and EmptyTuple are part of the standard library and behave exactly like n-argument tuples. You can use standard tuple syntax or the twiddle syntax.
Scala 2 Behavior: Skunk uses the Typelevel Twiddles library, which polyfills *: and EmptyTuple as aliases for Shapeless HLists. Twiddles provides implicit conversions between twiddle lists and regular tuples.
```
// Example of a Query using Twiddle Lists for parameter types
val q: Query[Short *: String *: String *: Int *: EmptyTuple] =
  sql"""
    SELECT name, age
    FROM   person
    WHERE  age < $int2
    AND    status = $
  """
  .query(varchar *: int4)
```
https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/reference/TwiddleLists.md
Handle nullable columns in Skunk queries
main
When working with columns that can contain NULL values in PostgreSQL, you must use the .opt modifier on your encoders and decoders.
- For standard decoders/encoders: Use int4.opt instead of int4.
- For interpolated SQL (using sql"..."): Use the syntax ${int4.opt} within the query string.
https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/tutorial/Query.md
Control transactions with the advanced usage pattern
main
For more granular control, use the transaction reference xa provided by the use block. This allows you to manage savepoints, explicit commits, and rollbacks.
Available actions on xa:
- xa.status: Yields the current TransactionStatus (as an fs2 Signal).
- xa.commit: Commits the transaction explicitly.
- xa.rollback: Rolls back the entire transaction.
- xa.savepoint: Creates a new xa.Savepoint.
- xa.rollback(sp): Rolls back to a specific xa.Savepoint, allowing the transaction to continue after an error.
Transaction Finalization Logic
When using the advanced pattern, Skunk determines whether to commit or rollback based on the session's status and how the block exited:
Status Normal Exit Cancellation Error Raised
Idle — — re-raise
Active commit roll back roll back, re-raise
Error roll back roll back roll back, re-raise
Note: An Idle status on normal exit means the user terminated the transaction explicitly inside the block.
https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/tutorial/Transactions.md

Status	Normal Exit	Cancellation	Error Raised
Idle	—	—	re-raise
Active	commit	roll back	roll back, re-raise
Error	roll back	roll back	roll back, re-raise

Use AppliedFragments for dynamic query construction

main

An AppliedFragment allows you to bind a fragment to a set of arguments at runtime. This is useful for building complex queries with optional filters on the fly. AppliedFragment forms a monoid, allowing you to combine them using |+| or foldSmash.

To execute an AppliedFragment, you must extract its underlying Query and its argument (the bound values).

Example: Dynamic Query Construction

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
}

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

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
}

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

Choosing between Simple and Extended query protocols
main
Skunk supports two Postgres protocols. Choosing the right one depends on your use case:
Use the Simple Query Protocol (Session#execute) if:
- Your query has no parameters.
- You are querying for a small number of rows.
- You will use the query only once per session.
Use the Extended Query Protocol (Session#prepare) if:
- Your query has parameters.
- You are querying for a large or unknown number of rows.
- You intend to stream the results.
- You intend to use the query multiple times per session (prepared statements can be reused).
https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/tutorial/Query.md
Compose multiple values with Composite Encoders
main
You can combine two encoders a: Encoder[A] and b: Encoder[B] into a composite encoder a ~ b of type Encoder[(A, B)]. This composite encoder expands to a sequence of placeholders in the resulting SQL.
Composite structures are flattened in the final SQL statement, providing convenience on the Scala side while maintaining correct placeholder sequencing.
```
// Constructing a composite encoder from two base encoders
sql"INSERT INTO person (name, age) VALUES (${varchar ~ int4})"

// Flattening a composite structure
val enc = varchar ~ int4 ~ float4
sql"INSERT INTO person (comment, name, age, weight, comment) VALUES ($text, $enc)"
```
https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/reference/Encoders.md
Use Postgres Arrays with skunk.data.Arr
main
Postgres arrays are represented in Skunk by skunk.data.Arr. Note that Skunk arrays are rectangular (unlike Scala Arrays, which are ragged).
Warning: Skunk does not yet support arrays containing nullable elements. Attempting to decode such a value will cause a runtime failure. The Arr API is not finalized and may change.
Numeric Array Types
Postgres Type Scala Type
_int2 Arr[Short]
_int4 Arr[Int]
_int8 Arr[Long]
_numeric Arr[BigDecimal]
_float4 Arr[Float]
_float8 Arr[Double]
Character Array Types
Postgres Type Scala Type Notes
_varchar Arr[String] Length argument not yet supported
_bpchar Arr[String] Length argument not yet supported
_text Arr[String]
Note: For _int8 and character types, precision and length arguments are not yet supported.
```
| Postgres Type   | Scala Type        |
|-----------------|------------------|
| `_int2`         | `Arr[Short]`      |
| `_int4`         | `Arr[Int]`        |
| `_int8`         | `Arr[Long]`       |
| `_numeric`      | `Arr[BigDecimal]` |
| `_float4`       | `Arr[Float]`     |
| `_float8`       | `Arr[Double]`    |
```
https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/reference/SchemaTypes.md
Understand Skunk metrics
main
Skunk uses OpenTelemetry via the otel4s implementation. It provides the db.client.operation.duration histogram, which records the duration of all operations interacting with the PostgreSQL server.
https://github.com/typelevel/skunk/blob/main/modules/docs/src/main/laika/tutorial/Telemetry.md

Skunk Documentation

What's inside Skunk

Overview of Skunk

What is a Command in Skunk?

Choosing between Simple and Extended Command Protocols

Understand PostgreSQL transaction statuses in Skunk

Understand Twiddle Lists for Query and Command parameters

Handle nullable columns in Skunk queries

Control transactions with the advanced usage pattern

Available actions on `xa`:

Transaction Finalization Logic

Use AppliedFragments for dynamic query construction

Example: Dynamic Query Construction

Choosing between Simple and Extended query protocols

Use the Simple Query Protocol (`Session#execute`) if:

Use the Extended Query Protocol (`Session#prepare`) if:

Compose multiple values with Composite Encoders

Use Postgres Arrays with skunk.data.Arr

Numeric Array Types

Character Array Types

Understand Skunk metrics

Postgres Type	Scala Type
`_int2`	`Arr[Short]`
`_int4`	`Arr[Int]`
`_int8`	`Arr[Long]`
`_numeric`	`Arr[BigDecimal]`
`_float4`	`Arr[Float]`
`_float8`	`Arr[Double]`

Postgres Type	Scala Type	Notes
`_varchar`	`Arr[String]`	Length argument not yet supported
`_bpchar`	`Arr[String]`	Length argument not yet supported
`_text`	`Arr[String]`

Skunk Documentation

What's inside Skunk

Available actions on xa:

Transaction Finalization Logic

Example: Dynamic Query Construction

Use the Simple Query Protocol (Session#execute) if:

Use the Extended Query Protocol (Session#prepare) if:

Numeric Array Types

Character Array Types

Available actions on `xa`:

Use the Simple Query Protocol (`Session#execute`) if:

Use the Extended Query Protocol (`Session#prepare`) if: