README.md


## Goals

Depo is meant to provide lightweight storage and querying
capabilities in Elixir by providing a minimal and polished API
that builds on the unique advantages of SQLite.

The lightweight and dynamic nature of SQLite makes it well-suited
as an option for adding simple transactional disk-backed 
storage and query capabilities to Elixir processes.
SQLite uses very little memory, and enables you to quickly
create, open, or update many databases programmatically. 
Elixir programmers know the advantages of many small things 
working together, and SQLite does too.
For help deciding if SQLite is a good fit for a given situation, 
the authors of SQLite have written a document on the
[appropriate uses for SQLite](https://sqlite.org/whentouse.html),
excerpted below:

> SQLite is not directly comparable to client/server SQL database engines such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to solve a different problem.

> Client/server SQL database engines strive to implement a shared repository of enterprise data. They emphasis scalability, concurrency, centralization, and control. SQLite strives to provide local data storage for individual applications and devices. SQLite emphasizes economy, efficiency, reliability, independence, and simplicity.

> SQLite does not compete with client/server databases. SQLite competes with [fopen()](http://man.he.net/man3/fopen).


## Installation

Depo can be installed by adding `depo` to your list of 
dependencies in `mix.exs`:

```elixir
def deps do
  [{:depo, "~> 1.6"}]
end
```

## Usage Example

```elixir
# Set up the database.
{:ok, db} = Depo.open(:memory)
# {:ok, #PID<0.155.0>}

# Enclose operations within a transaction.
Depo.transact(db, fn ->
  # Write SQL statements to the database.
  Depo.write(db, "CREATE TABLE greetings (phrase)")

  # Teach the database statements to cache them.
  Depo.teach(db, %{
    new_greeting: "INSERT INTO greetings VALUES (?1)",
    # By default, queries will return a map for each row.
    greetings: "SELECT * FROM greetings",
    # You can return a list of row value tuples.
    phrases: {:values, "SELECT phrase FROM greetings"},
    # You can return a single value (from one column in one row).
    first_phrase: {:single, "SELECT phrase FROM greetings LIMIT 1"},
  })
  Enum.each(["hola", "bonjour", "今日は"], fn phrase ->
    Depo.write(db, :new_greeting, phrase)
  end)
end)

Depo.read(db, :greetings)
# [
#   %{phrase: "今日は"},
#   %{phrase: "bonjour"},
#   %{phrase: "hola"}, 
# ]

# Any query can be streamed to a PID. 
stream_id = Depo.stream(db, self(), :phrases)
:timer.sleep(5)

phrases = Enum.reduce(1..3, [], fn _i, phrases ->
  receive do
    {^stream_id, phrase} -> phrases ++ [phrase]
  end
end)
# phrases == [ 
#   {"hola"}, 
#   {"bonjour"},
#   {"今日は"},
# ]

Depo.read(db, :first_phrase)
# "hola"
```

## Comparison With Other Libraries

[Dets](http://erlang.org/doc/man/dets.html), the native Erlang
disk-backed option, has a file size limit of 2GB (without transactions), while SQLite
files have a 140TB limit.
[Mnesia](http://erlang.org/doc/man/mnesia.html) adds transactional
guarantees to Dets, but introduces lots of complexity as well.
SQLite is a great option when you want fast, transactional
flexible disk-backed storage with zero configuration.

Depo uses Erlang NIFs (Native Implemented Functions) provided
by [Esqlite,](https://github.com/mmzeeman/esqlite) which you can
also use directly for a more low-level API.
An alternative library for using SQLite in Elixir is
[Sqlitex.](https://github.com/mmmries/sqlitex)
Depo is not compatible with the [Ecto](https://github.com/elixir-ecto/ecto)
data management library, which has a much broader feature scope.

## Utilizing the Dynamic and Lightweight Nature of SQLite

SQLite is commonly misunderstood as being an inferior client-server 
database (e.g. "a non-production-ready Postgres"), while it really 
just has totally different goals and design trade-offs. 
Those goals were shaped by SQLite's origin as an extension to the 
[Tcl programming language][tcl], which shares many properties with Elixir. 
Tcl is dynamic, functional, extensible, and features persistent 
immutable collections and channels, much like Elixir.
The author of SQLite, Dr. Richard Hipp, has [written a paper][hipp1] and 
[given a presentation][hipp2] about the role Tcl played in its development.
It was a big thing in the 90's and is still 
[a cool language.](https://github.com/msteveb/jimtcl)

SQLite doesn't enforce data types on columns, which can be a 
useful property to take advantage of in the context of a dynamic 
language like Elixir. 
You can handle defining data types and encoding/decoding in your 
application and SQLite will just get out of your way.
SQLite has [comprehensive documentation about its dynamic data typing.](https://sqlite.org/datatype3.html)

SQLite uses very little memory, so it makes it feasible to
use many smaller databases simultaneously instead of just
using a single huge one. 
This helps encourage a more modular database design.

[tcl]: http://www.tcl.tk/about/language.html
[hipp1]: https://www.tcl.tk/community/tcl2004/Papers/D.RichardHipp/drh.pdf
[hipp2]: http://www.tclcommunityassociation.org/wub/proceedings/Proceedings-2009/proceedings/sqlitetcl/tcl2009-sqlite.pdf