🎂 Cake SQL Query Builder for Gleam

🎂Cake is a library written in Gleam to compose SQL queries targeting SQL dialects 🐘PostgreSQL, 🪶SQLite, 🦭MariaDB and 🐬MySQL.

Installation

gleam add cake@4

Further documentation can be found on cake.hexdocs.pm.

Adapters

• 🐘PostgreSQL on Erlang: hex.pm/packages/cake_pog
• 🪶SQLite on Erlang: hex.pm/packages/cake_sqlight
• 🦭MariaDB and 🐬MySQL on Erlang: hex.pm/packages/cake_shork

Usage

Demos

See examples/README.md.

Code examples (from the demos)

• cake_demo_select_and_decode.gleam
• cake_demo_union_and_decode.gleam
• cake_demo_insert.gleam
• cake_demo_delete.gleam
• cake_demo_update.gleam
• cake_demo_insert_on_conflict_update.gleam
• cake_demo_select_join.gleam
• cake_demo_prepared_fragment.gleam

Unit tests as examples

See Cake’s tests, especially the Setup sections in each test module.

You may also compare the tests with the snapshots results.

Scope

This is an SQL query building library, thus it is not concerned about executing queries or decoding return values from queries, but merely about being a flexible and powerful tool to compose and craft SQL read and write queries.

Goals

• High degree of flexibility to compose queries: While the focus is on building queries there is also support for replacing or removing parts of queries.
• General support on these 4 large RDBMS: 🐘PostgreSQL, 🪶SQLite, 🦭MariaDB and 🐬MySQL.
• Not being tied to any specific dialect or dialect adapter library.
• Documentation should be comprehensive.
• It should be easy to use with existing Gleam dialect adapters such as:
  • 🐘PostgreSQL: pog
  • 🪶SQLite: sqlight
  • 🦭MariaDB & 🐬MySQL: shork
• Allow to define custom SQL fragments while still being safe from SQL injections by using prepared statements.

Non-goals

• Prohibition of invalid SQL queries: You can still craft invalid queries at any time, for example:
  • Omitting certain parts of queries required for them to run, such as not specifying a table name
  • Comparing values incompatible by SQL type
• Backporting many features between different RDMBS. For example, while Cake supports RETURNING on 🐘PostgreSQL and 🪶SQLite, it does not support it on 🦭MariaDB or 🐬MySQL.
• No automagic optimization: This library is not here to replace SQL knowledge, but to allow crafting and combining SQL queries in a flexible and type safe way. It might however work as a gateway to obtain SQL knowledge because the typed builder functions help to some degree in understanding how SQL queries can be crafted.

Tested targets

• Sqlite3 as part of ubuntu:latest (Docker)
• postgres:latest (Docker) for 🐘PostgreSQL
• mariadb:latest (Docker) for 🦭MariaDB
• mysql:latest (Docker) for 🐬MySQL

The tests run on Erlang but are generally target agnostic.

While the primary use case is to run queries on servers, this library runs on any Gleam target and for example in conjunction with following adapters, you may run queries composed with this library in browsers:

• sqlite3 WASM/JS for 🪶SQLite
• PGLite (Postgres WASM) for 🐘PostgreSQL

Development

Run test suite locally

bin/docker/attached
# wait a few seconds until everything is ready
# if you run gleam test too early, it will crash
gleam test

Helper commands

bin/docker/attached
bin/docker/detached
bin/docker/down

bin/test

bin/birdie/interactive-review
bin/birdie/accept-all
bin/birdie/reject-all