# SQL_Bridge
[](https://github.com/choptastic/sql_bridge/actions/workflows/tests-workflow.yml)
An Erlang SQL Abstraction layer for interfacing with SQL databases.
## Supported Databases
* MySQL
* PostgreSQL
### Planned for support
* Microsoft SQL Server
* SQLite
## What it does
It provides a simple layer on top of existing SQL drivers, abstracting away the
pool management, and providing sensible default behaviors. Further, it
provides a whole slew of methods for returning and updating data: lists,
tuples, proplists, dicts, existence checks, single fields returning and
setting, updating data from proplists, and more.
## Connection Conventions
- A process tracks which database it's connecting to, grabbing any of the
available database pools. No need to identify a specific pool with
requests.
- Connections are made automatically when the first query is attempted
- A process determines which database to connect to via the `sql_bridge`
application variable `lookup`.
- This is used commonly to simplify the process of running a single codebase
which connects to different database for different users or for different
host headers.
## Configuration
### `module_alias`
Probably the most unusual configuration to discuss is the `module_alias`.
This configuration option allows us to compile a module which exports all the
functions of `sql_bridge`, but allows us to use a different module name.
In my own apps, I tend to use a module called `db` which serves as the alias to
`sql_bridge`. While it's easy enough to make your own module and do an
`import`, this configuration parameter allows us to skip that step.
This alias module is generated and loaded during the `sql_bridge:start()`
function.
### `lookup`
The most important configuration variable is the `lookup` variable. This tells
SQL_Bridge which database to use at any given time.
It can take two possible values:
- An atom of the database name. For single-database apps, this is the simple
solution: Whatever value you assign to `lookup` will be the database
SQL_Bridge uses.
- A `{Module, Function}` or `{Module, Function, Args}` tuple. This is for
multi-database apps. The return value of `Module:Function()` or
`erlang:apply(Module, Function, Args)` will be used to determine which database
to connect to. This value will then be cached within the process dictionary so
that that (potentially expensive) function isn't repeatedly called within the
same process.
### `adapter`
As the final important config variable, `adapter` determines which database
driver to use. Currently, SQL_Bridge ships with the following adapters (And the
driver upon which it depends)
- `sql_bridge_epgsql` - [epgsql](http://github.com/epgsql/epgsql) - The
Erlang PostgreSQL driver (also uses poolboy).
- `sql_bridge_mysql_otp` - [mysql-otp](http://github.com/mysql-otp/mysql-otp) -
A New MySQL driver (also uses poolboy).
### Sample Config
There is a sample config file in
[sample.config](https://github.com/choptastic/sigma_sql/blob/master/sample.config),
but here are the configuration settings currently available:
```erlang
[
{sql_bridge, [
%% module_alias creates a module with the specified name, which can be
%% used as an alias to the sigma_sql module.
{module_alias, db},
%% There are three adapters that can be used:
%% + sql_bridge_epgsql: PostgreSQL adapter using epgsql
%% + sql_bridge_mysql_otp: MySQL adapter using mysql-otp
{adapter, sql_bridge_mysql_otp},
%% connection parameters (self explanitory, I hope)
{host, "127.0.0.1"},
{port, 3306},
{user, "user"},
{pass, "userpass"},
%% all connection parameters can also be determined from
%% a module or set from environment variables
%%
%% e.g. by a module function repo:get_host()
%% {host, {mod, repo, get_host }}
%%
%% or e.g. by an environment variable MY_HOST
%% {host, {env, "MY_HOST"}}
%% There are two different ways to determine database
%%
%% 1) All requests go to a single database, called 'database_name':
{lookup, database_name}
%%
%% 2) Before a request is made, run the function
%% `lookup_module:lookup_function()`, for which the return value will
%% be the database name
{lookup, {lookup_module, lookup_function}},
%% Number of connections to establish per pool (which really means
%% number of connections per database).
{connections_per_pool, 10},
%% If a connection pool is saturated, this allows additional "overflow"
%% connections to be established up to the limit specified below.
{overflow_connections_per_pool, 10},
%% If you prefer your string types (varchars, texts, etc) to be
%% returned as erlang lists rather than binaries, set the following
%% to true:
{stringify_binaries, false},
%% Viable option are '$' (epgSQL Style) asnd '?' (mysql_otp style)
{replacement_token_style, '$'},
%% Which module and function will be the fallback key generation
%% function.
{key_generator_module_function, {sql_bridge_random_key_generator, generate}}
]}
].
```
The most complicated us the `lookup` application variable. Lookup can be one of
three different kinds of values:
- Atom: That's the database every request will use.
- {Module, Function}: Call Module:Function() to determine which database to
connect to.
## API
### API Conventions
#### Abbreviations
Due to my obsession with brevity, most all function calls have hyper-terse
versions which are acronyms of something. Learning those conventions will save
you keystrokes, minimize the chance for typos, and shorten your code. The
drawback is that it's not entirely obvious on a cursory glance what a function
returns (for example: `db:fffr` is not exactly obvious that it stands for
"(F)irst (F)ield of (F)irst (R)ecord").
But you'll learn common shortcuts:
- `q` -> query
- `t` -> tuple
- `d` -> dict
- `l` -> list
- `pl` -> proplist
- `ff` -> first field
- `fr` -> first record
- `i` -> insert
- `u` -> update
**Conveniently, however,** There are also simpler, more semantic function
names, like `list`, `maps`, `proplist`, etc, which return exactly what the name
implies. All is documented below.
#### Prepared Statements?
SQL_Bridge currently does not offer prepared statements, but will do safe
variable replacement using a similar convention, either with MySQL's `?`
placeholder, or PostgreSQL's `$1, $2,...$X` placeholder (see
`replacement_token_style` in [Configuration](#configuration))
##### Replacement Placeholders
Which placeholder is used can be modified by the configuration variable
`replacement_token_style`. This value can be the atoms 'mysql' or 'postgres'
or it could also be the shortened version with the atom '?' or '$'
respectively.
Sample MySQL Placeholders:
```erlang
db:q("Select * from login where username=? or email=?", [Loginid, Email])
```
Sample PostgreSQL Placeholders:
```erlang
db:q("Select * from login where username=$1 or email=$2", [Loginid, Email])
```
#### Key Determination
For functions where raw SQL is not provided as an argument, SQL Bridge will
determine the key field by the follwing:
1. Query the schema to determine the primary key of the table.
2. If, the primary ke is not defined, or it's a multi-field key, the key field
will be called `TableName ++ "id"`.
#### Insert or Update Determination
There are some helper functions in SQL_Bridge that will attempt to determine if
we're updating or inserting a new one (e.g. `db:save`). The basic rule is this:
If the primary key field specified has a value of `0` or `undefined` (or
entirely left off), the call will be an `insert` operation, relying on either
the RDBMS's key generation (`auto_increment`, etc), or fall back to SQL
Bridge's [`Key Generation`](#key-generation-070) functionality.
If the provided key for these functions is anything other than `undefined`,
`0`, the operation is treated as an update.
### Functions
#### One more convention before showing each function
Almost all query functions in SQL_Bridge take one or two parameters.
- 1 Argument: the query will be executed as-is. (e.g. `db:q("select * from whatever")`)
- 2 Arguments: Argument two should be a list of arguments that correspond to
and will replace question marks (`?` or `$X` - depending on your
[configuration](#configuration)) within the query itself in order. (e.g.
`db:q("select * from whatever where field1=? or field1=?", [SomeValue, SomeOtherValue])`)
#### Table Structure for our examples
For our example, we're going to have a table called `player`:
```
+----------+-----------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-----------------------------------+------+-----+---------+----------------+
| playerid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(40) | YES | | NULL | |
| race | enum('dwarf','orc','elf') | YES | | NULL | |
| class | enum('wizard','archer','bruiser') | YES | | NULL | |
| level | int(10) unsigned | YES | | 1 | |
| alive | tinyint(1) | NO | | 1 | |
+----------+-----------------------------------+------+-----+---------+----------------+
```
#### Select Queries
##### Multi-record Queries
- `db:lists` or `db:q`: The most basic query. Will return a list of rows
formatted as simple lists.
```erlang
> db:q("select playerid, name from player where race=?", ["elf"]).
[[2,"Evan"],
[3,"Marc"]]
```
- `db:tuples` or `db:tq`: Like `db:q` except returns a list of rows formatted
as tuples.
```erlang
> db:tq("select playerid, name from player where race=?", ["elf"]).
[{2,"Evan"},
{3,"Marc"}]
```
- `db:proplists` or `db:plq`: Like `db:q` except returns a list of proplists,
with the keys of which are atomized versions of the database field names:
```erlang
> db:plq("select name, race, level from player where alive=?",[false]).
[[{name,"Rusty"},
{race,"dwarf"},
{level,35}],
[{name,"Justin"},
{race,"orc"},
{level,15}]]
```
- `db:dicts` or `db:dq`: Like `db:plq`, except returns a list of Erlang `dicts`
with the keys again being atomized versions of the field names.
- `db:maps` or `db:mq`: Like `db:plq`, except returns a list of Erlang `maps`,
with keys atomized versions of field names.
##### Single-record Queries
Single-record queries correspond directly to their multi-record queries, except
they only return a single row. They all start with `fr` for "first record"
- `db:list` or `db:fr`: Like `db:q` (Returns a list)
- `db:tuple` or `db:tfr`: Like `db:tq` (Returns a tuple)
- `db:proplist` or `db:plfr`: Like `db:plq` (Returns a proplist)
- `db:dict` or `db:dfr`: Like `db:dq` (Returns a dict)
- `db:map` or `db:mfr`: List `db:mq` (Returns a map)
##### Other convenience queries
- `db:fffr`: (F)irst (F)ield of (F)irst (R)ecord. Returns the first field of
the first record returned.
```erlang
> db:fffr("select count(*) from player where class=?",[wizard]).
2
```
- `db:ffl`: (F)irst (F)ield (L)ist. Returns a list of the first field from
each row.
```erlang
> db:ffl("select playerid from player where alive=? or class=?",[true,wizard]).
[1,2,3,5]
```
- `db:qexists`: Returns `true` or `false` depending on whether or not the
query returns any records.
```erlang
> db:qexists("select playerid from player where playerid=?",[999]).
false
```
- `db:exists(Table, IDField, IDValue)`: Returns true or false depending on
whether or not a record in `Table` exists where the specified `IDField` has
the value `IDValue`.
- `db:exists(Table, IDValue)`: Shortcut for `db:exists(Table, Table ++ "id", IDValue)`
- `db:field(Table, Field, IDValue)`: Returns the value of the field `Field`
from table `Table`, where the TableID value is `IDValue`.
```erlang
> db:field(player, race, 1).
"dwarf"
```
The above is the equivilant to `db:fffr("select race from player where playerid=1")`
- `db:field(Table, Field, IDField, IDValue)`
Like `db:field/3`, except you get to specify which field you're querying
for instead of assuming `Table ++ "id"` as the ID field.
- `db:fields(Table)`: Returns a list of the names of the fields of the named
`Table`
```erlang
> db:fields(player).
[playerid, name, race, class, level, alive]
```
- `db:field_exists(Table, Field)` : Returns true if the specified `Table`
contains the field called `Field`
##### Insert, Update, Delete Queries
###### Insert
- `db:qi` or `db:qinsert` Runs the specified query and returns the `insert_id`
###### Update
- `db:qu` or `db:qupdate`: Run the specified query and returns the number of
affected rows.
###### Update or Delete from a Proplist, Map, or Record
- `db:save(Table, Keyfield, Data)`: Run an update or insert query on the
Table provided with the specified Data as the row data. `Data` can be
either a proplist, a map, or a record (See _Workering with Records_ below).
If the value in `Data` associated with the Key `Keyfield` is a
zero, or is undefined, then an insert query is performed. Otherwise, an update
query is performed. Regardless of insert or update method, the return value is
the value of the `Keyfield` - if insert, then it returns the new `insert_id`,
and if update, the value associated with the `Keyfield` from `Data`.
- `db:save(Table, Data)`: Like `db:save(Table, Keyfield, Data)` except
`Keyfield` is deduced with `list_to_atom(atom_to_list(Table) ++ "id")`
- `db:update(Table, KeyField, Data)`: Like `save/3` but will always use an
`update` even if the value for the keyfield is `0` or `undefined`.
- `db:update(Table, Data)`: Like `save/2`, but uses an `update` operation.
- `db:insert(Table, Data)`: Like `save/2` but uses an `insert` operation.
**Special Note::** This does not strip out any Keyfields, it inserts the data
exactly as it is, so if the key field has a value of `0`, that's the value that
will be inserted.
###### Backwards Compatibility
For backwards compatibility, the following functions are kept as aliases of the above:
- `db:pl` = `db:save`
- `db:plu` = `db:update`
- `db:pli`= `db:insert`
###### Working with Records
SQL_Bridge can work with records, however, since records are done at compile
time, there are some additional steps that must be performed by you in order to
accomplish this. The simplest is to use the `save_record()` functions:
- `db:save_record(Table, KeyField, Record, FieldList)`: In order to call this
effectively, you must pass the return value of the built-in compile function
`record_info(fields, RECORDNAME)` as the argument for `FieldList`. For example,
if you have a record called `#foo` that is saved into the table `foo_tbl` you
could save it like this:
`db:save_record(foo_tbl, fooid, FooRec, record_info(fields, foo))`
- `db:save_record(Table, Record, FieldList`: Like `db:save/2`, this will
automatically determine the KeyField as `list_to_atom(atom_to_list(Table) ++ "id")`.
###### Using a Record Handler
SQL_Bridge also has an option to intelligently convert records into a format
SQL_Bridge can work with (namely, proplists and maps). You can do this by use a
`record_handler` configuration option. If the `record_handler` configuration
option is specified in the sql_bridge.config file, it will call that specified
function passing the record as an option.
To use this, the value of `record_handler` must be a 2-tuple of the format
`{Module, Function}`, where `Module:Function` is a function of arity 1 and
returns a proplist or map.
The simplest example would be to make a module in your app like:
```erlang
-module(my_record_handler).
-export([handle/1]).
handle(Foo = #foo{}) ->
sql_bridge_utils:record_to_proplist(Foo, record_info(fields, foo));
handle(Bar = #bar{}) ->
sql_bridge_utils:record_to_proplists(Bar, record_info(fields, bar)).
```
Then, in your config, set the `record_handler` value as follows:
```erlang
[{sql_bridge, [
...
{record_handler, {my_record_handler, handle}}
]}].
```
Once this is done, you can pass a record as the `Data` argument in `db:save/2-3`
###### Delete
- `db:delete(Table, ID)`: Delete records from a table.
- `db:delete(Table, Field, ID)`: Delete records from a table.
## Transactions
SQL_Bridge supports transactions through two mechanisms:
1. `db:start_trans()`, `db:commit()`, and `db:rollback()` - Manually initiate
a transaction. Note, if you run something like `db:q("BEGIN")`, SQL_Bridge
is not intelligent enough to determine that you're in a transaction. Please use
`db:start_trans()`.
2. `db:trans(Fun)` - Mnesia-style transactions where the contents of the
function are run within a transaction. Note that `Fun` is of arity 0
(that is, no arguments). If the function completes successfully, the queries
executed will be commited, and the return value of the `Fun()` will be the
return value of `db:trans(Fun)`. If `Fun()` crashes, the transaction will be
automatically rolled back, and the return value will be `{error, Reason}`,
where `Reason` is information about the crash (including a stacktrace).
## Key Generation (0.7.0+)
If, for some reason, you aren't using your RDBMS's built-in key generation
(like MySQL's `auto_increment` or Postgres' `SERIAL`), then you can use SQL
Bridge's key generation system. ?By default, if the primary key field of a
table is not using a built-in key generation, then SQL Bridge can generate a
key for you when saving via `db:save` or its sibling `db:insert` (it does not
apply to `db:update` - hopefully for obvious reasons).
The default implementation here can be found in
[`sql_bridge_random_key_generator.erl`](https://github.com/choptastic/sql_bridge/blob/master/src/sql_bridge_random_key_generator.erl).
## Misc Utilities
- `db:encode(Term)`: Safely escapes a data for database interaction on in a
SQL query, for the backend of your choice.
- `db:encode_list(List)`: Takes a list of terms and safely encodes them for
mysql interaction, separating them with commas.
- `db:encode64(Term)`
- `db:decode64(Term)`: Encodes and decodes any Erlang term to base64.
- `db:limit_clause(PerPage, Page)`: When you're doing a query that needs
pagination, sometimes you just don't want to deal with figuring out the
limit clause's offset and length. In this case, you can build a simple limit
clause for MySQL by passing this function which page you want to show (start at
page 1), and how many items you per page you want to show. It will do the
offset calculation for you and return a limit clause that can be inserted into
the query.
## Some Quirks
There are a number of quirks to get comfortable with when using sql_bridge:
- Dates, times, and timestamps are returned as a string (or binary), like
"2016-12-31", "23:15:46", or "2016-12-31 23:15:46". This is largely for
backwards compatibility with the original implementation which returned
returned dates and times as strings, and since so much of my code depends
on this, it's just how it is.
- Numeric and Decimal types are returned as floats or integers. Again, this
is because of some code I have that depends on it.
## Change Log
See [CHANGELOG.md](https://github.com/choptastic/sql_bridge/blob/master/CHANGELOG.md)
## TODO
- Add determination method for which database server to call for which purposes
(for example, updates to a master server, but queries to a slave server).
- Maybe Experiment with [record-based querys](https://github.com/choptastic/sql_bridge/issues/1)
- Add SQLLite Support
## About
Copyright (c) 2013-2026 [Jesse Gumm](http://jessegumm.com)
[MIT License](https://github.com/choptastic/sql_bridge/blob/master/LICENSE.md)
