sqlite_ecto2

sqlite_ecto2 is an Ecto 2.2.x adapter that allows you to create and maintain SQLite3 databases.

Read the tutorial for a detailed example of how to setup and use a SQLite repo with Ecto, or just check-out the CliffsNotes in the sections below if you want to get started quickly.

Ecto Version Compatibility

IMPORTANT: This release will only work with Ecto 2.2.x. If you need compatibility with older versions of Ecto, please see:

Ecto 2.1.x -> sqlite_ecto2 2.0.x series
Ecto 1.x -> sqlite_ecto v1.x series

When to Use `sqlite_ecto2`

(and when not to use it ...)

I strongly recommend reading Appropriate Uses for SQLite on the SQLite site itself. All of the considerations mentioned there apply to this library as well.

I will add one more: If there is any potential that more than one server node will need to write directly to the database at once (as often happens when using Elixir in a clustered environment), do not use sqlite_ecto2. Remember that there is no separate database process in this configuration, so each of your cluster nodes would be writing to its own copy of the database without any synchronization. You probably don't want that. Look for a true client/server database (Postgres, MySQL, or similar) in that case. SQLite's sweet spot is single-machine deployments (embedded, desktop, etc.).

Help Wanted!

I would welcome any assistance in improving sqlite_ecto2. Some specific areas of concern:

Documentation:

Newcomers, especially: I'd like feedback on the getting started content. What works and what is confusing? How can we make adopting this library more intuitive?
I'd like to have at least one public example application.
Add a more formal contribution guide, similar to the one from Ecto.

Code quality:

Look for performance issues and address them. I'm particularly concerned about the temporary triggers used to implement value returns from INSERT, UPDATE, and DELETE queries. Can we avoid using those in some / most cases?
Look for errors or other failures under stress.

This is by no means an exhaustive list. If you have other questions or concerns, please file issues or PRs. I do this in my spare time, so it may take me until I have time on an evening or weekend to reply, but I will appreciate any contribution.

When participating in or contributing to this project, please observe the Elixir Code of Conduct.

A WARNING About OTP 19.0.x

OTP 19.0.x appears to have a bug that causes it to misinterpret certain pattern matches. This causes the unit tests for sqlite_ecto to fail on some platforms when hosted on OTP 19.0.x. This bug did not appear in OTP 18.0 and appears to have been fixed for OTP 19.1. Consequently, I strongly advise you to avoid using OTP 19.0.x when running sqlite_ecto, especially if using decimal value types.

Note that the Travis configuration for this repo specifically excludes OTP 19.0 for this reason.

Dependencies

This library makes use of sqlite3 Since esqlite uses Erlang NIFs to incorporate SQLite, you will need a valid C compiler to build the library.

Example

Here is an example usage:

# In your config/config.exs file
config :my_app, Repo,
  adapter: Sqlite.Ecto2,
  database: "ecto_simple.sqlite3"

# In your application code
defmodule Repo do
  use Ecto.Repo,
    otp_app: :my_app,
    adapter: Sqlite.Ecto2
end

defmodule Weather do
  use Ecto.Schema

  schema "weather" do
    field :city     # Defaults to type :string
    field :temp_lo, :integer
    field :temp_hi, :integer
    field :prcp,    :float, default: 0.0
  end
end

defmodule Simple do
  import Ecto.Query

  def sample_query do
    query = from w in Weather,
          where: w.prcp > 0 or is_nil(w.prcp),
         select: w
    Repo.all(query)
  end
end

Usage

Add sqlite_ecto2 as a dependency in your mix.exs file.

def deps do
  [{:sqlite_ecto2, "~> 2.2"}]
end

To use the adapter in your repo:

defmodule MyApp.Repo do
  use Ecto.Repo,
    otp_app: :my_app,
    adapter: Sqlite.Ecto2
end

Incorrect (Surprising?) Implementation of Boolean Operators

SQLite's implementation of the boolean operator ('AND', 'OR', and 'NOT') return a integer values (0 or 1) since there is no boolean data type in SQLite. Certain Ecto code (and, in particular, some Ecto integration tests) expect actual boolean values to be returned. When sqlite_ecto2 is returning a value directly from a column, it is possible to determine that the expected value is boolean and that mapping will occur. Once any mapping occurs (even as simple as NOT column_value), this mapping is no longer possible and you will get the integer value as presented by SQLite instead.

Incomplete Ecto Constraint Implementation

Several Ecto constraints are not fully implemented in sqlite_ecto2 because SQLite does not provide enough information in its error reporting to implement changeset validation properly in all cases. Specifically, some foreign key and uniqueness constraints are reported by raising Sqlite.Ecto2.Error exceptions instead of returning an Ecto changeset with the error detail.

Silently Ignored Options

There are a few Ecto options which sqlite_ecto2 silently ignores because SQLite does not support them and raising an error on them does not make sense:

Most column options will ignore size, precision, and scale constraints on types because columns in SQLite have no types, and SQLite will not coerce any stored value. Thus, all "strings" are TEXT and "numerics" will have arbitrary precision regardless of the declared column constraints. The lone exception to this rule are Decimal types which accept precision and scale options because these constraints are handled in the driver software, not the SQLite database.
If we are altering a table to add a DATETIME column with a NOT NULL constraint, SQLite will require a default value to be provided. The only default value which would make sense in this situation is CURRENT_TIMESTAMP; however, when adding a column to a table, defaults must be constant values. Therefore, in this situation the NOT NULL constraint will be ignored so that a default value does not need to be provided.
When creating a table or index, the comment attribute is silently ignored. There is no reasonable place to store comments in SQLite schema.
When creating an index, concurrently and using values are silently ignored since they do not apply to SQLite.