## Introduction
`sqlite_ecto2` 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, configuration, and associated tests are in no way secure or robust and should not be used for a production database. They are only being used to demonstrate some of the 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:
```elixir
def application do
[applications: [:logger, :sqlite_ecto2, :ecto]]
end
defp deps do
[{:sqlite_ecto2, "~> 2.2"}]
end
```
Now make sure you can download your dependencies, compile, and setup your Ecto repository:
```
$ mix deps.get
$ mix ecto.gen.repo -r Blog.Repo
```
Edit the Blog.Repo module in `lib/blog/repo.ex` to use the `sqlite_ecto2` adapter:
```elixir
defmodule Blog.Repo do
use Ecto.Repo, otp_app: :blog, adapter: Sqlite.Ecto2
end
```
And change the default PostgreSQL configuration in `config/config.exs` to the following:
```elixir
config :blog, Blog.Repo,
adapter: Sqlite.Ecto2,
database: "blog.sqlite3"
config :blog, ecto_repos: [Blog.Repo]
```
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. Adding the `:ecto_repos` key with `[Blog.Repo]` tells
Ecto's `mix` tasks about the `Blog.Repo` database.
Fill in `lib/blog.ex` to start the Ecto repo when the application starts:
```elixir
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 = [
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
```
If `Elixir < 1.5.0`:
```elixir
...
children = [
worker(Blog.Repo, [])
]
...
```
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:
```elixir
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 timestamps to mark when entries have been inserted or 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" NAIVE_DATETIME);
CREATE TABLE "users" ("id" INTEGER PRIMARY KEY AUTOINCREMENT, "name" TEXT, "email" TEXT, "inserted_at" NAIVE_DATETIME NOT NULL, "updated_at" NAIVE_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:
```elixir
defmodule Blog.User do
use Ecto.Schema
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 19 [erts-8.3] [source] [64-bit] [smp:4:4] [async-threads:10] [hipe] [kernel-poll:false] [dtrace]
Compiling 1 file (.ex)
Generated blog app
Interactive Elixir (1.4.1) - press Ctrl+C to exit (type h() ENTER for help)
iex(1)> Blog.start(nil, nil)
{:ok, #PID}
iex(2)> Blog.Repo.insert(%Blog.User{name: "jazzyb", email: "anonymous@example.com"})
18:05:16.119 [debug] QUERY OK db=14.7ms
INSERT INTO "users" ("email","name","inserted_at","updated_at") VALUES (?1,?2,?3,?4) ;--RETURNING ON INSERT "users","id" ["anonymous@example.com", "jazzyb", {{2017, 3, 31}, {8, 5, 16, 78423}}, {{2017, 3, 31}, {8, 5, 16, 86372}}]
{:ok,
%Blog.User{__meta__: #Ecto.Schema.Metadata,
email: "anonymous@example.com", id: 1,
inserted_at: ~N[2017-03-31 08:05:16.078423], name: "jazzyb",
updated_at: ~N[2017-03-31 08:05:16.086372]}}
iex(3)> import Ecto.Query
Ecto.Query
iex(4)> Blog.Repo.all(Blog.User |> select([user], user.name))
18:07:32.472 [debug] QUERY OK source="users" db=0.8ms queue=0.1ms
SELECT u0."name" FROM "users" AS u0 []
["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:
```elixir
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, then write the Post model to `lib/blog/post.ex` like so:
```elixir
defmodule Blog.Post do
use Ecto.Schema
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:
```elixir
defmodule Blog.User do
use Ecto.Schema
alias Blog.Post
schema "users" do
has_many :posts, Post
field :name, :string
field :email, :string
timestamps()
end
end
```
The models are getting more complicated, so let's write a test to make sure we can add entries to our repo and make sure everything is working as it should. Edit `test/blog_test.exs` and add the following:
```elixir
defmodule BlogTest do
use ExUnit.Case
alias Blog.Repo
alias Blog.User
alias Blog.Post
import Ecto.Query
setup_all do
{:ok, pid} = Blog.start(nil, nil)
{:ok, [pid: pid]}
end
setup do
on_exit fn ->
Repo.delete_all(Post)
Repo.delete_all(User)
end
end
test "that everything works as it should" do
# assert we can insert and query a user
{:ok, author} = %User{name: "ludwig_wittgenstein", email: "sharp_witt@example.de"} |> Repo.insert
["ludwig_wittgenstein"] = User |> select([user], user.name) |> Repo.all
# assert we can insert posts
Repo.insert(%Post{user_id: author.id, title: "Tractatus", body: "Nothing to say."})
Repo.insert(%Post{user_id: author.id, title: "Tractatus", body: "Nothing else to say."})
Repo.insert(%Post{user_id: author.id, title: "Tractatus", body: "Nothing more to say."})
assert List.duplicate("Tractatus", 3) == Post
|> select([post], post.title)
|> where([post], post.user_id == ^author.id)
|> Repo.all
# ... and one more post and user for good measure
{:ok, user} = %User{name: "john_cusack", email: "cusack66@example.com"} |> Repo.insert
Repo.insert(%Post{user_id: user.id, title: "Trashy 80's Romance", body: "Say anything."})
assert ["Trashy 80's Romance"] == Post
|> select([post], post.title)
|> where([post], post.user_id == ^user.id)
|> Repo.all
end
end
```
This test shows how to insert entries into your database and query it for information. Run it with `mix test` to see that everything is working alright. **NOTE** You may need to re-migrate the database if you run into errors involving entries you've added in the past. If this is the case, just do the following to delete the old database and create a new pristine one:
```
$ mix ecto.drop
$ mix ecto.create
$ mix ecto.migrate
```
Now that we know everything works as it should, let's see what we can do with the associations we defined between User and Post. Write a new assertion at the end of the test which queries for all the posts of a particular user:
```elixir
# preload user posts
query = from u in User, where: u.id == ^author.id, preload: [:posts]
titles = query
|> Repo.all
|> Enum.map(fn user -> user.posts end)
|> List.flatten
|> Enum.map(fn post -> post.title end)
assert List.duplicate("Tractatus", 3) == titles
```
In the example above, the `preload` command in the query fills in the `posts` value when we query for a user. Then the assertion does some overly complicated code to get the titles of all of user `author`'s posts. The associations we defined earlier are what give us access to the posts through user.
## Making Changes to the Database
What if we want to add values for our users, like, requiring passwords to access the blog? We can add columns to tables using migrations. Create a new migration with `mix ecto.gen.migration user_passwords` and edit the result:
```elixir
defmodule Blog.Repo.Migrations.UserPasswords do
use Ecto.Migration
def change do
alter table(:users) do
add :password, :string, [default: "CHANGE_ME", null: false]
end
end
end
```
Run `mix ecto.migrate` and verify the new column has been added to the table. **NOTE** `sqlite_ecto2` can only add columns to tables -- it cannot remove or modify columns once they have been created.
Before we can use the new table we need to update our model. The User model at `lib/blog/user.ex` should now look like the following:
```elixir
defmodule Blog.User do
use Ecto.Schema
alias Blog.Post
schema "users" do
has_many :posts, Post
field :name, :string
field :email, :string
field :password, :string, [default: "CHANGE_ME", null: false]
timestamps()
end
end
```
Let's add a new assertion to our test case to verify we can update our users' passwords:
```elixir
# update user password
passwordChange = Ecto.Changeset.change(%User{id: author.id}, password: "leopoldine")
Repo.update(passwordChange)
assert ["leopoldine"] == User
|> select([user], user.password)
|> where([user], user.id == ^author.id)
|> Repo.all
```
We have another problem as our schema stands: There is nothing preventing users from sharing the same username. That could get very confusing. We can fix that in Ecto by creating a unique index. Run `mix ecto.gen.migration distinct_usernames` and edit the resulting file:
```elixir
defmodule Blog.Repo.Migrations.DistinctUsernames do
use Ecto.Migration
def change do
create index(:users, [:name], unique: true)
end
end
```
Run `mix ecto.migrate` to apply this migration. This creates a unique index on `users.name` that prevents two users from having the same username. We can write another assertion to test this. After `"ludwig_wittgenstein"` is defined in our test case, verify that we can't create another user with the same name:
```elixir
# prevent usernames from overlapping
assert_raise Sqlite.DbConnection.Error, "constraint: UNIQUE constraint failed: users.name", fn ->
%User{name: "ludwig_wittgenstein", password: "NOT_THE_REAL_USER"} |> Repo.insert
end
```