README.md

# A MonetDB driver for Elixir

Warning: Early development.

## Usage

In your mix.exs file, add the project dependency:

```
{:monet, "~> 0.1.5"}
```

You can start a pool by adding `Monet` to your supervisor tree and providing configuration options:

```elixir
opts = [
    pool_size: 10,
    port: 50_000,
    host: "127.0.0.1",
    username: "monetdb",
    password: "monetdb",
    database: "monetdb",
    read_timeout: 10_000,
    send_timeout: 10_000,
    connect_timeout: 10_000,
]
children = [
  ...
  {Monet, opts} 
]
```

You can then use the `Monet.query/1` and `Monet.query/2` functions:

```
{:ok, result} = Monet.query("create table atreides(name text)")
{:ok, result} = Monet.query("insert into attreides (name) values (?)", ["Leto"])
```

You can optionally use the `query!` variant.

### Named Pool
When you create the pool, you have the option of providing a `name` This is useful in the case where you want to connect to multiple instances:

 ```elixir
opts = [
  pool_size: 10,
  ...
  name: :replica
]
```

When a named pool is used, the `query/2` and `query/3` functions must be used:

```elixir
{:ok, result} = Monet.query(:replica, "create table atreides(name text)")
{:ok, result} = Monet.query(:replica, "insert into atreides (name) values (?)", ["Paul"])
```

## Results
On success, a `Monet.Result` structure is returned. The `rows` field exposes a list of list.

`Monet.Result` also implements the Enumerable and Jason.Encoder protocols. By default, these simply enumerate or render `rows` as a list of lists. However, `Monet.as_map/1` can be used to change this behavior to iterate over a list of maps.

```elixir
case Monet.as_map(Monet.query("select id, name from saiyans")) do
  {:ok, result} -> ...
  {:error, err} -> ...
end
```

`as_map/1` is safe to chain with `Monet.query` as it will return any `{:error, _}` structure passed to it as-is.

Note that `result.rows` does not change. It continues to be a listof lists. What does change is the Enumerable and Jason encoding behavior.

Optionally, `columns: :atoms` can be passed to `as_map`.


### Result Helpers
`Monet.rows/1`, `Monet.rows!/1`, `Monet.row/1`, Monet.row!/1`, Monet.scalar/1`, `Monet.scalar!/1`, `Monet.map!/2` and `Monet.maps!/2` are all helpers that can help to turn a Result into more concrete structures.

They're safe to use even if `Monet.query` returns an error (they simply return the error).

```elixir
with {:ok, [a, b]} <- Monet.row(Monet.query("select 1, 2")) do
  ...
end
```

`row`, and `map` return an error if more the result has more than 1 row (the `row!` and `map!` variants raise). They return `nil` if there are no rows.

`scalar` and `scalar!` behaves the same, but also returns/raises if more there is more than 1 column.

`map`, `map!`, `maps` and `maps!` accepts a second optional parameter, `columns: :atoms`.

## Transactions
`Monet.transaction/1` and `Monet.transaction/2` (for named pools) can be used to wrap code in a transaction:

```elixir
Monet.transaction(fn tx ->
  Monet.query!(tx, "insert into table...", [args])
  Monet.query!(tx, "select * from table")
end)
```

The function you provide can return:

* `{:rollback, value}` - to rollback the transaction and return the same 2-value tuple
* `{:commit, value}` - to commit the transaction and return `{:ok, value}`
* `{:ok, value}` - to commit the transaction and return `{:ok, value}`
* `value` - to commit the transaction and return `{:ok, value}`

## Prepared Statements
Any calls to `query` which passes arguments will use a prepared statement.

Special handling of prepared within a transaction is available. Using `Monet.prepare/3`, prepared statements can be registered with a given name and re-used. At the end of the transaction, the prepared statements are automatically deallocated.

```elixir
Monet.transaction(fn tx ->
  Monet.prepare(tx, :test_insert, "insert into test (id) values (?)")
  with {:ok, r1} <- Monet.query(tx, :test_insert, [1]),
       {:ok, r2} <- Monet.query(tx, :test_insert, [2])
  do
    {:ok, [r1, r2]}
  else
    err -> {:rollback, err}
  end
end)
```

Keep in mind that MonetDB automatically deallocates prepared statements on execution error. This is why having automatic management of prepared statements at the transaction level makes sense (since a failure to execute probably means the transaction ends). It's much more complicated at the connection level (especially when you add the indirection of the pool).