Elixir Language, Phoenix Framework and Me.

Ecto vs Active Record

| Comments

This post is about comparing the feature and performance between Elixir's Ecto library - database wrapper and languge integrated query for Elixir and Ruby on Rails Active Record.

Round 1: Database Migration

Active Record

In Ruby on Rails Active Record, we can generate a migration for a model by following these steps:

Step 1: Generate model file and migration file
1

$ rails g model book title:string description:text author:string publisher:string

And the content of the migration file is as following

1
2
3
4
5
6
7
8
9
10
11
12

class CreateBooks < ActiveRecord::Migration
  def change
    create_table :books do |t|
      t.string :name
      t.text :description
      t.string :author
      t.string :publisher

      t.timestamps
    end
  end
end
Step 2: Create the database
1

bundle exec rake db:create
Step 3: Run the migration
1

$ bundle exec rake db:migrate

That's all for Active Record

Ecto

Assuming that you have already put in ecto and postgrex dependencies and already run mix deps.get, here are the steps:

Step 1: Create Repo file by manually adding web/models/repo.ex file witht the following content
1
2
3
4
5
6
7
8
9
10
11

defmodule BookStoreElixir.Repo do
  use Ecto.Repo, adapter: Ecto.Adapters.Postgres

  def conf do
    parse_url "ecto://postgresuser:password@localhost/book_store_elixir"
  end

  def priv do
    app_dir(:book_store_elixir, "priv/repo")
  end
end
Step 2: Create the Postgres Database
1

$ createdb book_store_elixir --encoding='utf-8' --locale=en_US.UTF-8 --template=template0
Step 3: Create model file web/models/book.ex with the following code:
1
2
3
4
5
6
7
8
9
10

defmodule BookStoreElixir.Book do
  use Ecto.Model

  schema "books" do
    field :title, :string
    field :description, :string
    field :author, :string
    field :publisher, :string
  end
end
Step 4: Generate a migration file from terminal
1

$ mix ecto.gen.migration BookStoreElixir.Repo create_book
Step 5: Edit the newly generated migration file

There is a need a type the SQL to create the table here:

1

$ vim priv/repo/migrations/20141011143244_create_book.exs

1
2
3
4
5
6
7
8
9
10
11
12

defmodule BookStoreElixir.Repo.Migrations.CreateBook do
  use Ecto.Migration

  def up do
    "CREATE TABLE books(id serial primary key, title varchar(125), 
                  description text, author varchar(255), publisher varchar(255))"
  end

  def down do
    "DROP TABLE books"
  end
end
Step 6: Run the migration file
1

$ mix ecto.migrate BookStoreElixir.Repo
Step 7: Add BookStoreElixir.Repo as a worker in line 11 of lib/book_store_elixir.ex
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

defmodule BookStoreElixir 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 = [
      # Define workers and child supervisors to be supervised
      worker(BookStoreElixir.Repo, [])
    ]

    opts = [strategy: :one_for_one, name: BookStoreElixir.Supervisor]
    Supervisor.start_link(children, opts)
  end
end
Conclusion for Database Migration:

Active Record wins as there are fewer number of steps (3 steps in Active Record vs 7 steps in Ecto). Ecto will need to have more mix tasks in order to be more on par with Active Record.

Round 2: Database CRUD (Create, Read, Update, Delete) operations Syntax

1 - Create

Active Record

To create a new record in Active Record, we will use create command on Book model 

1
2
3
4
5
6

irb> Book.create({title: "Programming Ruby 1.9 & 2.0", \
          author: "Dave Thomas, with Chad Fowler and Andy Hunt", \
          publisher: "The Pragmatic Bokshelf", \
          description: "Ruby is the fastest growing and most exciting dynamic language out there. \
                        If you need to get working programs delivered fast, \
                        you should add Ruby to your toolbox."})
Ecto

To create a new record in Ecto, we will use insert command on Book Repo

1
2
3
4
5
6
7
8

iex> alias BookStoreElixir.Repo
iex> alias BookStoreElixir.Book
iex> BookStoreElixir.Repo.insert(%Book{title: "Programming Ruby 1.9 & 2.0", \
          author: "Dave Thomas, with Chad Fowler and Andy Hunt", \
          publisher: "The Pragmatic Bokshelf",
          description: "Ruby is the fastest growing and most exciting dynamic language out there. \
                        If you need to get working programs delivered fast, \
                        you should add Ruby to your toolbox."})

Syntax wise, they are quite similar. Although Active Record code seems to be a bit neater but it can be considered a draw for creation syntax.

2 - Read

Active Record

In Active Record, you can perform quite an entensive list of queries by using where, order, limit, offset, group, join, having, select, pluck, includes. For instance

1
2
3
4
5
6
7
8

irb> Book.where("title LIKE ?", "%Programming%") \
         .order(id: :desc) \
         .limit(1) \
         .offset(0) \
         .group("id") \
         .having("books.id >= 1") \
         .select(:id) \
         .pluck(:id)

Pretty amazing that Active Record can do chaining in combine into 1 single query.

Ecto

There are 2 types of writing Query in Ecto

  • First Way: Using query syntax provide by Ecto
1
2
3
4
5
6
7
8
9
10
11

iex> alias BookStoreElixir.Book
iex> alias BookStoreElixir.Repo
iex> import Ecto.Query
iex> from book in BookStoreElixir.Book, select: book.id, \
                                        where: like(book.title, "%Programming%"), \
                                        order_by: [desc: book.id], \
                                        limit: 1, \
                                        offset: 0, \
                                        group_by: book.id, \
                                        having: book.id >= 1
iex> Repo.all(query)
  • Second Way: Using chaining by utilising Elixir Pipe will make query just like Active Record:
1
2
3
4
5
6
7
8
9
10
11

iex> alias BookStoreElixir.Book
iex> alias BookStoreElixir.Repo
iex> import Ecto.Query
iex> query = Book |> select([book], book.id) \
                  |> where([book], like(book.title, "%Programming%")) \
                  |> order_by([book], desc: book.id) \
                  |> limit(1) \
                  |> offset(0) \
                  |> group_by([book], book.id) \
                  |> having([book], book.id >= 1)
iex> Repo.all(query)

Syntax wise, both Ecto and Active Record have shown that they are quite expressive in showing the data transformation through a series of query functions. However, Active Record syntax is still considered better in this Round as it is showing an easier approach. Although, the second way of query that we use for Elixir Ecto is pretty closed to Active Record syntax, it still exhibits a more wordy syntax than Active Record. Albeit, Elixir Ecto is using a nice approach by letting programmer to have access to a "so called" object instance in the query - having[book, book.id >= 1) vs "having("books.id >= 1")

3 - Update

Active Record

To update a record, we will use update_attributes on Book instance 

1
2

irb> book = Book.first
irb> book.update_attributes({title: "Programming Ruby9 &ss 2.0 (4th edition)"})

Ecto

To update a record, we will use `update on BookStoreElixir.Repo

1
2
3
4

iex> alias BookStoreElixir.Book
iex> alias BookStoreElixir.Repo
iex> book = Repo.one(Book)
iex> Repo.update(%{book | title: "Programming Ruby9 &ss 2.0 (4th edition)"})

Both Ecto and Active Record have exhibited almost the same level of code when updating a record. Hence, it is a draw.

4 - Delete

Active Record

To delete a record, we will use destroy on a Book instance

1
2

irb> book = Book.first
irb> book.destroy

Ecto

To delete a record, we will use delete on BookStoreElixir.repo

1
2
3
4

iex> alias BookStoreElixir.Book
iex> alias BookStoreElixir.Repo
iex> book = Repo.one(Book)
iex> Repo.delete(book)

Both Ecto and Active Record have exhibited almost the same level of code when delete a record. Hence, it is a draw.

So for this Round 2, Active Record is the winner (1 vs 0).

Round 3: Speed of Creation

Note that this is tested on my local machine. The result here is just for your reference, and should not be treated as an official benchmark. I am using Ruby 2.1.2, running on Rails 4.1.4 and Elixir 1.0.0. Both Rails and Elixir application are running with Postgres 9.3.5. For Rails, environment in Rails 4.1.4 has been preloaded with Spring, hence there is a not a need for system to spend time to load the Rails environment. Further more, the Rails code was run with the log mode the same as on production - i.e. no query log was printed out.

Active Record

Here is the code inside db/seeds.rb

1
2
3
4
5
6
7
8

# first round 100, second round 10,000, third round 1,000,000
number_of_records = 100
for i in 1..number_of_records
  Book.create({title: "Book Title #{i}", \
               description: "Description #{i}", \
               author: "Author #{i}", \
               publisher: "Publisher #{i}"})
end

Here is how I run it:

1

$ time bundle exec rake db:seed

Here are the results of 3 running rounds (with creating 100, 10,000 records and 1 million records). Each time database drop, creation and migration were performed before hand.

1
2
3
4
5
6
7
8
9
10
11

real0m1.706s
user0m1.401s
sys0m0.229s

real  0m12.328s
user  0m7.820s
sys 0m1.041s

real  17m52.272s
user  10m41.534s
sys 1m20.044s

Active Record took 1.706 seconds, 12.328 seconds and a whooping 17 minutes 52.272 seconds to complete 100, 10,000 and 1 million record creation run.

Ecto

I create a file seeds.ex inside the elixir project folder with the following content

1
2
3
4
5
6
7
8
9
10
11
12
13

alias BookStoreElixir.Book
alias BookStoreElixir.Repo

# First round 100, second round 10,000, third round 1,000,000
number_of_records = 100
range = 1..100
for i <- range do
  book = %Book{title: "Book Title #{i}", \
               description: "Description #{i}", \
               author: "Author #{i}", \
               publisher: "Publisher #{i}"}
  Repo.insert(book)
end

Then run this seeds

1

$ time mix run seeds.ex

And here is the reusult of 3 runs:

1
2
3
4
5
6
7
8
9
10
11

real  0m0.624s
user  0m0.513s
sys 0m0.187s

real  0m5.109s
user  0m2.459s
sys 0m1.002s

real  6m15.161s
user  3m19.126s
sys 2m53.117s

Ecto took merely 0.624 seconds, 5.109 seconds and 6 minutes 15.161 seconds to complete the same 3 runs of creation 100, 10,000 and 1 million records.

Here is the chart that shows the performance of Ecto vs Active Record through 3 runs:

images

Ecto is approximately 62% faster than Active Record. Hence, Ecto is the winner in this round.

Round 4: Speed of Updating

We will now run updating on 100, 10,000 and 1 million records in each database each. We will update the records, one by one to have a new book title. As usual, let's start with Active Record first

Active Record

Now in my db/seeds.rb file, I will change it like following:

1
2
3

Book.all.each do |book|
  book.update_attributes({title: "New Book Title #{book.id}")
end

And run this file:

1

$ time bundle exec rake db:seed

1
2
3
4
5
6
7
8
9
10
11

real  0m1.725s
user  0m1.423s
sys 0m0.243s

real  0m12.414s
user  0m6.936s
sys 0m1.109s

real  23m30.364s
user  15m22.126s
sys 1m26.698s

Active Record took 1.725 seconds, 12.414 seconds and 23 minutes, 30.364 seconds to update all 100, 10,000 and 1 million records respectively.

Ecto

We will change the seeds.ex file to following

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

alias BookStoreElixir.Book
alias BookStoreElixir.Repo

# range = 1..10000
# for i <- range do
#  book = %Book{title: "Book Title #{i}", \
                description: "Description #{i}", \
                author: "Author #{i}", \
                publisher: "Publisher #{i}"}
#  Repo.insert(book)
# end

for book <- Repo.all(Book) do
  book = %{book | title: "New Book Title #{book.id}"}
  Repo.update(book)
end

Before you can run this file, you will need to do a bit modification on postgrex library as this library is throwing a time out if a query spends more than 5 seconds to complete. Repo.all(Book) will definitely take up more than 5 seconds to load up the entire collection of books into the memory

From the project folder, what I did was edit file deps/ecto/lib/ecto/adapters/postgres.ex

1

$ vim deps/ecto/lib/ecto/adapters/postgres.ex

Then search for @timeout 5000 and replace by @timeout :infinity

Now you can run and time the running time of this file

1

$ time mix run seeds.ex

Here are the result of 3 runs:

1
2
3
4
5
6
7
8
9
10
11

real  0m0.606s
user  0m0.509s
sys 0m0.184s

real  0m5.495s
user  0m2.515s
sys 0m1.071s

real  7m12.016s
user  3m49.446s
sys 3m7.004s

Ecto took merely 0.606 seconds, 5.495 seconds and 7 minutes, 12 seconds to update 100, 10,000 records and 1 million records respectively. Apparently, Ecto is the winner.

Here is the chart showing the above elapsed time:

images

Conclusion

With the fact that Active Record and Ecto each has 2 wons, it is concluded to be a draw between Active Record and Ecto. Ecto seems to be very promising library, which provides a very interesting way to write a database query. The steps to setup and the syntax of Ecto will definitely improve in the future.

Comments