brioche/sql

Bun implements a fast, native PostgreSQL Client, built in the runtime. Bun does not provides any ORM or whatever on top of Postgres: Bun provides a Postgres client speaking SQL, and nothing else.

To simplify usage of brioche/sql, and to help interoperability with the rest of the Gleam ecosystem, brioche/sql does not try to reinvent its own API and tries to stick with what pog does! If you know how to write SQL queries with pog, you’ll feel right at home! Be careful though, because of the nature of the JavaScript runtime, and because of some limitations, brioche/sql does not implement exactly the same API that pog, especially with dates & timestamps. Every dates and timestamps are managed as gleam/time/timestamp.Timestamp and some options could differ. However, your daily experience should be really close!

Thanks a lot to Louis Pilfold & all other pog contributors for their awesome work!

Types

Config

</>

pub type Config {
  Config(
    host: option.Option(String),
    port: option.Option(Int),
    user: option.Option(String),
    password: option.Option(String),
    database: option.Option(String),
    idle_timeout: option.Option(Int),
    connection_timeout: option.Option(Int),
    max_lifetime: option.Option(Int),
    ssl: option.Option(Ssl),
    onconnect: option.Option(fn(Connection) -> Nil),
    onclose: option.Option(fn(Connection) -> Nil),
    max: option.Option(Int),
    bigint: option.Option(Bool),
    prepare: option.Option(Bool),
    default_format: Format,
  )
}

Constructors

```
Config(
  host: option.Option(String),
  port: option.Option(Int),
  user: option.Option(String),
  password: option.Option(String),
  database: option.Option(String),
  idle_timeout: option.Option(Int),
  connection_timeout: option.Option(Int),
  max_lifetime: option.Option(Int),
  ssl: option.Option(Ssl),
  onconnect: option.Option(fn(Connection) -> Nil),
  onclose: option.Option(fn(Connection) -> Nil),
  max: option.Option(Int),
  bigint: option.Option(Bool),
  prepare: option.Option(Bool),
  default_format: Format,
)
```
Arguments

host

Database server hostname. Defaults to PGHOST if not defined.

port

Database server port number. Defaults to PGPORT if not defined.

user

Database user for authentication. Defaults to PGUSER, PGUSERNAME, USER or USERNAME if not defined.

password

Database password for authentication. Defaults to PGPASSWORD if not defined.

database

Name of the database to connect to. Defaults to PGDATABASE if not defined.

idle_timeout

Maximum time in seconds to wait for connection to become available.

connection_timeout

Maximum time in seconds to wait when establishing a connection.

max_lifetime

Maximum lifetime in seconds of a connection.

ssl

Whether to use TLS/SSL for the connection.

onconnect

Callback function executed when a connection is established.

onclose

Callback function executed when a connection is closed.

max

Maximum Int of connections in the pool.

bigint

By default values outside i32 range are returned as strings. If this is true, values outside i32 range are returned as BigInts. You can use the bigi package to work with BigInt.

prepare

Automatic creation of prepared statements, defaults to true.

default_format

Default format to use, when returning the results.

Connection

</>

A Connection represents a open connection to Postgres, managing a pool of database connections for you. You can open a database connection with connect.

pub type Connection

Format

</>

Output format for the query results. Map will output the result as a hashmap, while Tuple will output the results as a tuple.

Write your decoders according to the format you desire. The format has a default for every connection, but can also be overriden on a per-request basis.

pub type Format {
  Map
  Tuple
}

Constructors

```
Map
```
```
Tuple
```

Query

opaque </>

Represents an SQL query, ready to be executed. A query is immutable and can be executed an arbitrary number of times.

pub opaque type Query(a)

Returned

</>

pub type Returned(a) {
  Returned(rows: List(a), count: Int)
}

Constructors

```
Returned(rows: List(a), count: Int)
```

SqlError

</>

pub type SqlError {
  ConstraintViolated(
    message: String,
    constraint: String,
    detail: String,
  )
  PostgresqlError(code: String, name: String, message: String)
  UnexpectedResultType(List(decode.DecodeError))
  QueryTimeout
  ConnectionUnavailable
  TransactionRolledBack(String)
}

Constructors

ConstraintViolated(
  message: String,
  constraint: String,
  detail: String,
)

The query failed as a database constraint would have been violated by the change.

```
PostgresqlError(code: String, name: String, message: String)
```
The query failed within the database. https://www.postgresql.org/docs/current/errcodes-appendix.html
```
UnexpectedResultType(List(decode.DecodeError))
```
The rows returned by the database could not be decoded using the supplied dynamic decoder.
```
QueryTimeout
```
The query timed out.
```
ConnectionUnavailable
```
No connection was available to execute the query. This may be due to invalid connection details such as an invalid username or password.
```
TransactionRolledBack(String)
```
Transaction Rolled back

Ssl

</>

pub type Ssl {
  SslCustom(tls: tls.Tls)
  SslEnabled
  SslDisabled
}

Constructors

```
SslCustom(tls: tls.Tls)
```
Enable SSL connection, and check CA certificate. It is the most secured option to use SSL and should be always used by default. Never ignore CA certificate checking unless you know exactly what you are doing.
```
SslEnabled
```
Enable SSL connection, but don’t check CA certificate. SslVerified should always be prioritized upon SslUnverified. As it implies, that option enables SSL, but as it is unverified, the connection can be unsafe. Use this option only if you know what you’re doing. In case pog can not find the proper CA certificate, take a look at the README to get some help to inject the CA certificate in your OS.
```
SslDisabled
```
Disable SSL connection completely. Using this option will let the connection unsecured, and should be avoided in production environment.

Value

</>

Parameter sent in a request. Convert your data to Value to use them as parameters in SQL requests.

pub type Value

Values

array

</>

pub fn array(value: List(a), mapper: fn(a) -> Value) -> Value

Convert a List(a) as its equivalent SQL array. Escape the value if needed.

bigint

</>

pub fn bigint(config: Config, bigint: Bool) -> Config

By default values outside i32 range are returned as strings. If this is true, values outside i32 range are returned as BigInts. You can use the bigi package to work with BigInt.

bool

</>

pub fn bool(bool: Bool) -> Value

Convert a bool as an SQL value. Escape the value if needed.

bytea

</>

pub fn bytea(byte_array: BitArray) -> Value

Convert a binary as an SQL value. Escape the value if needed.

connect

</>

pub fn connect(
  config: Config,
) -> Result(Connection, dynamic.Dynamic)

Connect to the database. Fails with an error when something goes wrong.

connection_timeout

</>

pub fn connection_timeout(
  config: Config,
  connection_timeout: Int,
) -> Config

Maximum time in seconds to wait when establishing a connection.

database

</>

pub fn database(config: Config, database: String) -> Config

Name of the database to connect to. Defaults to PGDATABASE if not defined.

default_config

</>

pub fn default_config() -> Config

Generate a default config. If the config is completely empty, Bun will automatically read, by order of precedence: POSTGRES_URL, DATABASE_URL, PGURL, PG_URL, TLS_POSTGRES_DATABASE_URL & TLS_DATABASE_URL.

default_format

</>

pub fn default_format(
  config: Config,
  default_format: Format,
) -> Config

Default format to use, when returning the results.

disconnect

</>

pub fn disconnect(connection: Connection) -> promise.Promise(Nil)

Disconnect from the database.

error_code_name

</>

pub fn error_code_name(error_code: String) -> Result(String, Nil)

Get the name for a PostgreSQL error code.

sql.error_code_name("01007")
// -> Ok("privilege_not_granted")

PostgreSQL Error Codes

execute

</>

pub fn execute(
  query: Query(a),
  connection: Connection,
) -> promise.Promise(Result(Returned(a), SqlError))

Execute an SQL query against the database.

import brioche/sql
let assert Ok(client) = sql.client(sql.default_config())
sql.query("SELECT $1")
|> sql.parameter(sql.float(1.0))
|> sql.returning(decode.at([0], decode.float))
|> sql.execute(client)

float

</>

pub fn float(float: Float) -> Value

Convert a float as an SQL value. Escape the value if needed.

format

</>

pub fn format(query: Query(a), format: Format) -> Query(a)

Modify the expected output format. Use it to override the default_format in the configuration if needed.

host

</>

pub fn host(config: Config, host: String) -> Config

Database server hostname. Defaults to PGHOST if not defined.

idle_timeout

</>

pub fn idle_timeout(config: Config, idle_timeout: Int) -> Config

Maximum time in seconds to wait for connection to become available.

int

</>

pub fn int(int: Int) -> Value

Convert an int as an SQL value. Escape the value if needed.

max

</>

pub fn max(config: Config, max: Int) -> Config

Maximum Int of connections in the pool.

max_lifetime

</>

pub fn max_lifetime(config: Config, max_lifetime: Int) -> Config

Maximum lifetime in seconds of a connection.

null

</>

pub fn null() -> Value

Create the SQL value NULL.

nullable

</>

pub fn nullable(
  value: option.Option(a),
  mapper: fn(a) -> Value,
) -> Value

Convert an Option(a), whether as NULL or as the data. Escape the value if needed.

onclose

</>

pub fn onclose(
  config: Config,
  onclose: fn(Connection) -> Nil,
) -> Config

Callback function executed when a connection is closed.

onconnect

</>

pub fn onconnect(
  config: Config,
  onconnect: fn(Connection) -> Nil,
) -> Config

Callback function executed when a connection is established.

parameter

</>

pub fn parameter(query: Query(a), value: Value) -> Query(a)

Add a parameter to the request. First parameter will be matched with $1, second with $2 and so on.

password

</>

pub fn password(
  config: Config,
  password: option.Option(String),
) -> Config

Database password for authentication. Defaults to PGPASSWORD if not defined.

port

</>

pub fn port(config: Config, port: Int) -> Config

Database server port number. Defaults to PGPORT if not defined.

prepare

</>

pub fn prepare(config: Config, prepare: Bool) -> Config

Automatic creation of prepared statements, defaults to true.

query

</>

pub fn query(sql: String) -> Query(dynamic.Dynamic)

Create a query from an SQL string. You can use positional parameters in the query, and chain the query call to add parameters or decoder.

import brioche/sql
let assert Ok(client) = sql.client(sql.default_config())
sql.query("SELECT $1")
|> sql.parameter(sql.float(1.0))
|> sql.returning(decode.at([0], decode.float))
|> sql.execute(client)

returning

</>

pub fn returning(
  query: Query(a),
  decoder: decode.Decoder(b),
) -> Query(b)

Modify the expecting return type of the query. Every data row of the response will be run against the decoder to get a correct data type as a response.

savepoint

</>

pub fn savepoint(
  connection: Connection,
  handler: fn(Connection) -> promise.Promise(a),
) -> promise.Promise(a)

Create a savepoint during a transaction. When something goes wrong in a transaction, the transaction will rollback until the savepoint.

import brioche/sql
let assert Ok(client) = sql.client(sql.default_config())
sql.transaction(client, fn (client) {
  use id1 <- promise.try_await({
    sql.query("SELECT $1")
    |> sql.parameter(sql.float(1.0))
    |> sql.returning(decode.at([0], decode.float))
    |> sql.execute(client)
  })
  // Define a savepoint, to rollback easily to that point.
  use client <- sql.savepoint(client)
  use id2 <- promise.try_await({
    sql.query("SELECT $1")
    |> sql.parameter(sql.float(1.0))
    |> sql.returning(decode.at([0], decode.float))
    |> sql.execute(client)
  })
  promise.resolve(Ok(#(id1, id2)))
})

ssl

</>

pub fn ssl(config: Config, tls: tls.Tls) -> Config

Whether to use TLS/SSL for the connection.

text

</>

pub fn text(text: String) -> Value

Convert a string as an SQL value. Escape the value if needed.

timestamp

</>

pub fn timestamp(value: timestamp.Timestamp) -> Value

Convert a timestamp as an SQL value. Escape the value if needed.

timestamp_decoder

</>

pub fn timestamp_decoder() -> decode.Decoder(timestamp.Timestamp)

Decode a timestamp coming from the database.

transaction

</>

pub fn transaction(
  connection: Connection,
  handler: fn(Connection) -> promise.Promise(Result(a, SqlError)),
) -> promise.Promise(Result(a, SqlError))

Create a transaction, allowing you to group queries together to rollback all queries when one goes wrong.

import brioche/sql
let assert Ok(client) = sql.client(sql.default_config())
sql.transaction(client, fn (client) {
  use id1 <- promise.try_await({
    sql.query("SELECT $1")
    |> sql.parameter(sql.float(1.0))
    |> sql.returning(decode.at([0], decode.float))
    |> sql.execute(client)
  })
  use id2 <- promise.try_await({
    sql.query("SELECT $1")
    |> sql.parameter(sql.float(1.0))
    |> sql.returning(decode.at([0], decode.float))
    |> sql.execute(client)
  })
  promise.resolve(Ok(#(id1, id2)))
})

url_config

</>

pub fn url_config(database_url: String) -> Result(Config, Nil)

Read a URL config, and convert it as a Config data. Use this when you don’t want Bun to automatically read the environment variable of URL config, or when you need to add some additional configuration.

user

</>

pub fn user(config: Config, user: String) -> Config

Database user for authentication. Defaults to PGUSER, PGUSERNAME, USER or USERNAME if not defined.

Constructors

Arguments

Constructors

Constructors

Constructors

Constructors