Basic Sqlite.Ecto Tutorial

Introduction

Sqlite.Ecto is an Ecto adapter which helps you to interact with SQLite databases.

This very brief tutorial will walk you through the basics of configuring and using Ecto with SQLite. We are going to setup a very basic schema that one might need for a blog. The following assumes you already have some familiarity with Elixir development.

PLEASE NOTE that the following schema and configuration is in no way secure or robust and should not be used for a production database. It is only being used to demonstrate some features of Ecto.

Configuring Ecto

Let's create our new Elixir code with mix: mix new blog. Change into the new directory and update the mix.exs file to use Ecto and SQLite:

def application do
  [applications: [:logger, :sqlite_ecto, :ecto]]
end

defp deps do
  [{:sqlite_ecto, "~> 0.0.2"}]
end

Now make sure you can download your dependencies, compile, and setup your Ecto repository:

$ mix deps.get
$ mix ecto.gen.repo Blog.Repo

Edit the Blog.Repo module in lib/blog/repo.ex to use the Sqlite.Ecto adapter:

defmodule Blog.Repo do
  use Ecto.Repo, otp_app: :blog, adapter: Sqlite.Ecto
end

And change the default PostgreSQL configuration in config/config.exs to the following:

config :blog, Blog.Repo,
  adapter: Sqlite.Ecto,
  database: "blog.sqlite3",
  # pool options:
  size: 1,
  max_overflow: 0

In this example blog.sqlite3 is the SQLite file that will store our blog's database. The file will be created in the top-level directory. You can change it to any file path you like. Note the last two options define the number of worker processes that will connect to our database file. These options are recommended for testing purposes. While SQLite supports concurrent access to database files, not enough testing has been done with Sqlite.Ecto at this time to verify that concurrent access will not corrupt the database file.

Fill in lib/blog.ex to start the Ecto repo when the application starts:

defmodule Blog do
  use Application

  # See http://elixir-lang.org/docs/stable/elixir/Application.html
  # for more information on OTP Applications
  def start(_type, _args) do
    import Supervisor.Spec, warn: false

    children = [
      worker(Blog.Repo, [])
    ]

    # See http://elixir-lang.org/docs/stable/elixir/Supervisor.html
    # for other strategies and supported options
    opts = [strategy: :one_for_one, name: Blog.Supervisor]
    Supervisor.start_link(children, opts)
  end
end

Run mix ecto.create. Verify that the SQLite database has been created at blog.sqlite3 or wherever you have configured your database to be written.

Ecto Models

Now that we have our database configured and created, we can create tables to hold our data. Let's start by creating a "users" database table. Run mix ecto.gen.migration create_users. This will create a file at priv/repo/migrations/TIMESTAMP_create_users.exs where TIMESTAMP is the particular date and time you ran the migration command. Edit this file to create the new table:

defmodule Blog.Repo.Migrations.CreateUsers do
  use Ecto.Migration

  def change do
    create table(:users) do
      add :name, :string
      add :email, :string
      timestamps
    end
  end
end

This migration will generate a users table with columns for the user's name and email address. The timestamps statement will create datetime timestamps for the date and time that entries are inserted and updated.

Run mix ecto.migrate to create the new table. You can verify the migration with the following:

$ sqlite3 blog.sqlite3 .schema
CREATE TABLE "schema_migrations" ("version" BIGINT PRIMARY KEY, "inserted_at" datetime);
CREATE TABLE "users" ("id" INTEGER PRIMARY KEY AUTOINCREMENT, "name" TEXT, "email" TEXT, "inserted_at" datetime NOT NULL, "updated_at" datetime NOT NULL);

Before we can use the table. We have to write an Ecto model to encapsulate it. Edit lib/blog/user.ex to define the model:

defmodule Blog.User do
  use Ecto.Model

  schema "users" do
    field :name, :string
    field :email, :string
    timestamps
  end
end

Notice how it resembles the migration we just wrote. Let's quickly make sure the model is working with iex:

$ iex -S mix
Erlang/OTP 17 [erts-6.4.1] [source] [64-bit] [smp:4:4] [async-threads:10] [hipe] [kernel-poll:false]

Interactive Elixir (1.0.4) - press Ctrl+C to exit (type h() ENTER for help)
iex(1)> Blog.start(nil, nil)
{:ok, #PID<0.129.0>}
iex(2)> Blog.Repo.insert(%Blog.User{name: "jazzyb", email: "[email protected]"})

14:55:11.865 [debug] BEGIN [] (31.7ms)

14:55:11.948 [debug] INSERT INTO "users" ("email","inserted_at","name","updated_at") VALUES (?1,?2,?3,?4) ;--RETURNING ON INSERT "users","id" ["[email protected]", {{2015, 5, 20}, {18, 55, 11, 0}}, "jazzyb", {{2015, 5, 20}, {18, 55, 11, 0}}] (23.1ms)

14:55:11.950 [debug] COMMIT [] (1.8ms)
%Blog.User{__meta__: %Ecto.Schema.Metadata{source: "users", state: :loaded},
 email: "[email protected]", id: 1,
 inserted_at: %Ecto.DateTime{day: 20, hour: 18, min: 55, month: 5, sec: 11,
  usec: 0, year: 2015}, name: "jazzyb",
 updated_at: %Ecto.DateTime{day: 20, hour: 18, min: 55, month: 5, sec: 11,
  usec: 0, year: 2015}}
iex(3)> import Ecto.Query
nil
iex(4)> Blog.Repo.all(Blog.User |> select([user], user.name))

14:56:38.686 [debug] SELECT u0."name" FROM "users" AS u0 [] (0.8ms)
["jazzyb"]
iex(5)>

In the above output, we start the Blog.Repo (1), create a new user jazzyb (2), and then verify that we can query that user from the database (4).

Associations

Now that we have some basic understanding of models, let's complicate the schema a little bit. If we want to create a blog, we have to have some posts that users can write. Let's create a new migration to generate the posts table with mix ecto.gen.migration create_posts. Edit the resulting file:

defmodule Blog.Repo.Migrations.CreatePosts do
  use Ecto.Migration

  def change do
    create table(:posts) do
      add :title, :string
      add :body, :string
      add :user_id, references(:users)
      timestamps
    end
  end
end

And run mix ecto.migrate to create the posts table. We write the Post model to lib/blog/post.ex like so:

defmodule Blog.Post do
  use Ecto.Model
  alias Blog.User

  schema "posts" do
    belongs_to :user, User
    field :title, :string
    field :body, :string
    timestamps
  end
end

Notice that in both the migration and the model, we define an association that "posts belong to users". We also need to define a reverse association that says "users have multiple posts". Edit the User model at lib/blog/user.ex to add the association with the Post model:

defmodule Blog.User do
  use Ecto.Model
  alias Blog.Post

  schema "users" do
    has_many :posts, Post
    field :name, :string
    field :email, :string
    timestamps
  end
end