# 🎂 Cake SQL Query Builder for Gleam
[](https://hex.pm/packages/cake)
[](https://www.erlang.org/)
[](https://en.wikipedia.org/wiki/JavaScript)
[](https://hexdocs.pm/cake/)
[](https://hex.pm/packages/cake)
[](https://github.com/inoas/gleam-cake/actions/workflows/test.yml)
[](https://github.com/inoas/gleam-cake/blob/main/LICENCE)
[](https://discord.gg/Fm8Pwmy)
<br>
<br>
<p align="center">
<img
src="https://raw.githubusercontent.com/inoas/gleam-cake/main/cake-logo.png"
alt="Cake Logo"
style="max-height: 33vh; width: auto; height: auto"
width="480"
height="480"
/>
</p>
<br>
<p align="center">
<i>
🎂Cake is a library written in Gleam to compose SQL queries
targeting SQL dialects 🐘PostgreSQL, 🪶SQLite, 🦭MariaDB and
🐬MySQL.
</i>
</p>
<br>
<br>
## Installation
```sh
gleam add cake@4
```
Further documentation can be found on
[hexdocs.pm/cake](https://hexdocs.pm/cake).
### Adapters
- 🐘PostgreSQL on Erlang:
[hex.pm/packages/cake_pog](https://hex.pm/packages/cake_pog)
- 🪶SQLite on Erlang:
[hex.pm/packages/cake_sqlight](https://hex.pm/packages/cake_sqlight)
- 🦭MariaDB and 🐬MySQL on Erlang:
[hex.pm/packages/cake_shork](https://hex.pm/packages/cake_shork)
<!--
TODO:
- 🪶SQLite on WASM/JS https://sqlite.org/wasm
- 🐘PostgreSQL WASM via https://pglite.dev/
-->
## Usage
### Demos
See
[examples/README.md](https://github.com/inoas/gleam-cake/blob/main/examples/README.md#available-examples).
#### Code examples (from the demos)
- [cake\_demo\_select\_and\_decode.gleam](
https://github.com/inoas/gleam-cake/blob/main/examples/01_select_and_decode/src/cake_demo_select_and_decode.gleam)
- [cake\_demo\_union\_and\_decode.gleam](
https://github.com/inoas/gleam-cake/blob/main/examples/02_union_and_decode/src/cake_demo_union_and_decode.gleam)
- [cake\_demo\_insert.gleam](
https://github.com/inoas/gleam-cake/blob/main/examples/03_insert/src/cake_demo_insert.gleam)
- [cake\_demo\_delete.gleam](
https://github.com/inoas/gleam-cake/blob/main/examples/04_delete/src/cake_demo_delete.gleam)
- [cake\_demo\_update.gleam](
https://github.com/inoas/gleam-cake/blob/main/examples/05_update/src/cake_demo_update.gleam)
- [cake\_demo\_insert\_on\_conflict\_update.gleam](
https://github.com/inoas/gleam-cake/blob/main/examples/06_insert_on_conflict_update/src/cake_demo_insert_on_conflict_update.gleam)
- [cake\_demo\_select\_join.gleam](
https://github.com/inoas/gleam-cake/blob/main/examples/07_select_join/src/cake_demo_select_join.gleam)
- [cake\_demo\_prepared\_fragment.gleam](
https://github.com/inoas/gleam-cake/blob/main/examples/08_prepared_fragment/src/cake_demo_prepared_fragment.gleam)
### Unit tests as examples
See Cake's
[tests](https://github.com/inoas/gleam-cake/tree/main/test/cake_test),
especially the _Setup_ sections in each test module.
You may also compare the tests with the
[snapshots](https://github.com/inoas/gleam-cake/tree/main/test/birdie_snapshots)
results.
### Intended aliases
Use the following aliases to make the library more ergonomic:
```gleam
import cake/select as s // SELECT statements
import cake/where as w // WHERE clauses
import cake/join as j // JOIN clauses
import cake/update as u // UPDATE statements
import cake/insert as i // INSERT statements
import cake/delete as d // DELETE statements
import cake/combined as c // For combined queries such as UNION
import cake/fragment as f // For arbitrary SQL code including functions
import cake/param as p // Typed params
```
## Library Design
### CakeQuery Type Hierarchy
The following diagram shows the type hierarchy of `CakeQuery` and its
constructors:
```mermaid
graph TD
CakeQuery["CakeQuery(a)"]:::accent0
CakeQuery --> CakeReadQuery["CakeReadQuery(ReadQuery)"]:::accent1
CakeQuery --> CakeWriteQuery["CakeWriteQuery(WriteQuery(a))"]:::accent2
CakeReadQuery --> SelectQuery["SelectQuery(Select)"]:::accent3
CakeReadQuery --> CombinedQuery["CombinedQuery(Combined)"]:::accent3
CakeWriteQuery --> InsertQuery["InsertQuery(Insert(a))"]:::accent4
CakeWriteQuery --> UpdateQuery["UpdateQuery(Update(a))"]:::accent4
CakeWriteQuery --> DeleteQuery["DeleteQuery(Delete(a))"]:::accent4
SelectQuery --> Select["Select<br/>• SelectKind<br/>• Selects<br/>• From<br/>• Joins<br/>• Where<br/>• GroupBy<br/>• Having<br/>• OrderBy<br/>• Limit<br/>• Offset<br/>• Epilog<br/>• Comment"]:::accent5
CombinedQuery --> Combined["Combined<br/>• CombinedQueryKind<br/>• queries: List(Select)<br/>• OrderBy<br/>• Limit<br/>• Offset<br/>• Epilog<br/>• Comment"]:::accent5
InsertQuery --> Insert["Insert(a)<br/>• InsertIntoTable<br/>• InsertColumns<br/>• InsertModifier<br/>• InsertSource(a)<br/>• InsertConflictStrategy(a)<br/>• Returning<br/>• Epilog<br/>• Comment"]:::accent6
UpdateQuery --> Update["Update(a)<br/>• UpdateTable<br/>• UpdateModifier<br/>• UpdateSets<br/>• From<br/>• Joins<br/>• Where<br/>• Returning<br/>• Epilog<br/>• Comment"]:::accent6
DeleteQuery --> Delete["Delete(a)<br/>• DeleteModifier<br/>• DeleteTable<br/>• DeleteUsing<br/>• Joins<br/>• Where<br/>• Returning<br/>• Epilog<br/>• Comment"]:::accent6
```
**Legend:**
- `CakeQuery(a)` is the top-level type with type parameter `a`
- **Read Queries** (`CakeReadQuery`) are for SELECT and combined
operations (UNION, INTERSECT, EXCEPT)
- **Write Queries** (`CakeWriteQuery`) are for INSERT, UPDATE, and DELETE
operations
- Each query type contains structured fields for building SQL statements
### 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](https://hex.pm/packages/pog)
- 🪶SQLite: [sqlight](https://hex.pm/packages/sqlight)
- 🦭MariaDB & 🐬MySQL: [shork](https://hex.pm/packages/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)](https://hub.docker.com/_/ubuntu)
- [postgres:latest (Docker)](https://hub.docker.com/_/postgres) for
🐘PostgreSQL
- [mariadb:latest (Docker)](https://hub.docker.com/_/mariadb) for
🦭MariaDB
- [mysql:latest (Docker)](https://hub.docker.com/_/mysql) 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](https://sqlite.org/wasm) for 🪶SQLite
- [PGLite (Postgres WASM)](https://pglite.dev/) for 🐘PostgreSQL
## Development
### Run test suite locally
```sh
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
```sh
bin/docker/attached
bin/docker/detached
bin/docker/down
bin/test
bin/birdie/interactive-review
bin/birdie/accept-all
bin/birdie/reject-all
```