Depo

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, 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().

Installation

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

def deps do
  [{:depo, "~> 1.7"}]
end

Usage Example

# 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, the native Erlang disk-backed option, has a file size limit of 2GB (without transactions), while SQLite files have a 140TB limit. Mnesia 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, which you can also use directly for a more low-level API. An alternative library for using SQLite in Elixir is Sqlitex. Depo is not compatible with the 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, 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 and given a presentation about the role Tcl played in its development. It was a big thing in the 90’s and is still a cool language.

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.

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.