There have been instances where I required data persistence with an Elixir application although I don’t want to start a PostgreSQL instance. For small applications or tasks, SQLite3 sounds like a good alternative, especially if you are running the app on a small machine.

In this post, I’m going to show how to use Elixir and Ecto with SQLite3. This is strongly based on an article from although that article is oriented to Phoenix, I’m going to focus on creating a small Elixir project. The project will have a GenServer that writes a random value every second into the SQLite database.

I will be using Elixir version 1.15.2 and Erlang version 26.0.2.

First, create the project’s directory with a supervisor.

$ mix new data_collector --sup

Add ecto_sqlite3 to mix.exs, you can find more details in its documentation.

   defp deps do
  {:ecto_sqlite3, "~> 0.10"}

Fetch the dependencies

$ mix deps.get

Create a lib/data_collector/ecto.ex with

defmodule DataCollector.Repo do
    use Ecto.Repo, otp_app: :data_collector, adapter: Ecto.Adapters.SQLite3

Create a config/config.exs with

import Config

config :data_collector,
  ecto_repos: [DataCollector.Repo]

config :data_collector, DataCollector.Repo,
  database: Path.expand("../data_collector_#{Mix.env()}.db", Path.dirname(__ENV__.file)),
  pool_size: 5

Create a schema lib/data_collector/instrument.ex

defmodule DataCollector.Instrument do
  use Ecto.Schema
  import Ecto.Changeset

  schema "instruments" do
    field(:name, :string)
    field(:measurement, :integer)


  def changeset(instrument, params \\ %{}) do
    |> cast(params, [:name, :measurement])
    |> validate_required([:name, :measurement])

And a migration

$ mix ecto.gen.migration create_instruments

# priv/repo/migrations/2023MMDD_create_instruments.exs
defmodule DataCollector.Repo.Migrations.CreateInstruments do
  use Ecto.Migration

  def change do
    create table(:instruments) do
      add(:name, :string)
      add(:measurement, :integer)


Create the database and apply the migrations

$ mix ecto.create
$ mix ecto.migrate

After that, you should see a file called data_collector_dev.db, If you open it with sqlite3, you should see the table instruments

$ sqlite3 data_collector_dev.db
sqlite> .tables
instruments         schema_migrations
sqlite> .schema instruments
CREATE TABLE IF NOT EXISTS "instruments" ("id" INTEGER PRIMARY KEY AUTOINCREMENT, "name" TEXT, "measurement" INTEGER, "inserted_at" TEXT NOT NULL, "updated_at" TEXT NOT NULL);

Create a GenServer to insert data every second into the database

# lib/data_collector/worker.ex
defmodule DataCollector.Worker do
  use GenServer

  alias DataCollector.{Instrument, Repo}

  def start_link(_opts) do
    GenServer.start_link(__MODULE__, nil, name: __MODULE__)

  def init(_opts) do
    Process.send_after(self(), :work, 1000)
    {:ok, nil}

  def handle_info(:work, state) do
    data = %{
      name: "instrument_#{Enum.random(1..1000)}",
      measurement: Enum.random(1..100)

    |> Instrument.changeset(data)
    |> Repo.insert()

    Process.send_after(self(), :work, 1000)
    {:noreply, state}

Add Repo and GenServer to lib/data_collector/application.ex

children = [
  {DataCollector.Repo, []},

Start the processes, in the logs you should see the inserts into the database

$ mix run --no-halt

If you check the database, you should see the values inserted by the GenServer.

$ sqlite3 data_collector_dev.db
sqlite> select * from instruments limit 2;

At this point, the tutorial could be complete although I will add how to create the release, if you are not interested in how to create a release you can stop here.

Now, let’s modify the content of config/config.exs to the following.

# config/config.exs
import Config

# Configures Elixir's Logger
config :logger, :console,
  format: "$time $metadata[$level] $message\n",
  metadata: [:request_id]

config :data_collector,
  ecto_repos: [DataCollector.Repo]

import_config "#{config_env()}.exs"

And add these three new config files:

# config/dev.exs
import Config

config :data_collector, DataCollector.Repo,
  database: Path.expand("../data_collector_dev.db", Path.dirname(__ENV__.file)),
  pool_size: 5

# config/prod.exs
import Config

# Do not print debug messages in production
config :logger, level: :info

# config/runtime.exs
import Config

if config_env() == :prod do
  database_path =
    System.get_env("DATABASE_PATH") ||
      raise """
      DATABASE_PATH environment variable is missing.
      For example: /data/name/name.db

  config :data_collector, DataCollector.Repo,
    database: database_path,
    pool_size: String.to_integer(System.get_env("POOL_SIZE") || "10")

Create a release file lib/data_collector/release.ex with functions related to the migration, you can find more details in this post.

defmodule DataCollector.Release do
  @app :data_collector

  def migrate do
    for repo <- repos() do
      {:ok, _, _} =
          &, :up, all: true)

  def rollback(repo, version) do
    {:ok, _, _} =
        &, :down, to: version)

  defp repos do
    Application.fetch_env!(@app, :ecto_repos)

Now you can create the release, apply the migration over it, and start it.

$ MIX_ENV=prod mix release
$ DATABASE_PATH=/tmp/data_collector_prod.db _build/prod/rel/data_collector/bin/data_collector eval "DataCollector.Release.migrate"

$ DATABASE_PATH=/tmp/data_collector_prod.db _build/prod/rel/data_collector/bin/data_collector start

If you check the release’s database, you should see the values inserted by the GenServer.

$ sqlite3 /tmp/data_collector_prod.db
sqlite> select * from instruments limit 1;

That concludes the tutorial.

I hope this post can be useful for you. If you have any suggestions or feedback on the post, please feel free to reach out to me.