ActiveRecord vs. Ecto – Phần 2: Làm chủ Database Queries

Đây là phần hai của series "ActiveRecord vs. Ecto", nơi Batman và Batgirl fight over querying databases và chúng ta compare apples and oranges.

Sau khi nhìn vào database schemas và migrations trong ActiveRecord vs. Ecto part one, post này cover cách cả ActiveRecord và Ecto enable developers query database, và cách cả hai compare khi deal với same requirements. Dọc theo đường, chúng ta cũng sẽ find out Batgirl's 1989-2011 identity.

Seed data

Hãy bắt đầu! Dựa trên database structure định nghĩa trong bài đầu series này, assume users và invoices tables có data sau stored trong chúng:

users

* ActiveRecord's created_at field tên inserted_at trong Ecto by default.

invoices

* ActiveRecord's created_at field tên inserted_at trong Ecto by default.

Queries performed through this post assume data above stored in database, so keep this information in mind while reading it.

Find item using its primary key

Hãy bắt đầu với getting record từ database dùng primary key.

ActiveRecord

irb(main):001:0> User.find(1) User Load (0.4ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2 [["id", 1], ["LIMIT", 1]] => #<User id: 1, full_name: "Bette Kane", email: "bette@kane.test", created_at: "2018-01-01 10:01:00", updated_at: "2018-01-01 10:01:00">

Ecto

iex(3)> Repo.get(User, 1) [debug] QUERY OK source="users" db=5.2ms decode=2.5ms queue=0.1ms SELECT u0."id", u0."full_name", u0."email", u0."inserted_at", u0."updated_at" FROM "users" AS u0 WHERE (u0."id" = $1) [1] %Financex.Accounts.User{ __meta__: #Ecto.Schema.Metadata<:loaded, "users">, email: "bette@kane.test", full_name: "Bette Kane", id: 1, inserted_at: ~N[2018-01-01 10:01:00.000000], invoices: #Ecto.Association.NotLoaded<association :invoices is not loaded>, updated_at: ~N[2018-01-01 10:01:00.000000] }

Comparison

Cả hai cases khá similar. ActiveRecord rely on find class method của User model class. Nghĩa là mọi ActiveRecord child class có own find method trong nó.

Ecto dùng approach khác, rely on Repository concept as mediator giữa mapping layer và domain. Khi dùng Ecto, module User không có knowledge về cách find itself. Responsibility đó present trong module Repo, able to map nó to underneath datastore, trong case của chúng ta là Postgres.

Khi compare SQL query itself, chúng ta spot vài differences:

• ActiveRecord load all fields (users.*), trong khi Ecto load only fields listed in schema definition.
• ActiveRecord include LIMIT 1 vào query, trong khi Ecto không.

Fetching all items

Hãy đi xa hơn và load all users từ database.

ActiveRecord

irb(main):001:0> User.all User Load (0.5ms) SELECT "users".* FROM "users" LIMIT $1 [["LIMIT", 11]] => #<ActiveRecord::Relation [#<User id: 1, full_name: "Bette Kane", email: "bette@kane.test", created_at: "2018-01-01 10:01:00", updated_at: "2018-01-01 10:01:00">, #<User id: 2, full_name: "Barbara Gordon", email: "barbara@gordon.test", created_at: "2018-01-02 10:02:00", updated_at: "2018-01-02 10:02:00">, #<User id: 3, full_name: "Cassandra Cain", email: "cassandra@cain.test", created_at: "2018-01-03 10:03:00", updated_at: "2018-01-03 10:03:00">, #<User id: 4, full_name: "Stephanie Brown", email: "stephanie@brown.test", created_at: "2018-01-04 10:04:00", updated_at: "2018-01-04 10:04:00">]>

Ecto

iex(4)> Repo.all(User) [debug] QUERY OK source="users" db=2.8ms decode=0.2ms queue=0.2ms SELECT u0."id", u0."full_name", u0."email", u0."inserted_at", u0."updated_at" FROM "users" AS u0 [] [ %Financex.Accounts.User{ __meta__: #Ecto.Schema.Metadata<:loaded, "users">, email: "bette@kane.test", full_name: "Bette Kane", id: 1, inserted_at: ~N[2018-01-01 10:01:00.000000], invoices: #Ecto.Association.NotLoaded<association :invoices is not loaded>, updated_at: ~N[2018-01-01 10:01:00.000000] }, %Financex.Accounts.User{ __meta__: #Ecto.Schema.Metadata<:loaded, "users">, email: "barbara@gordon.test", full_name: "Barbara Gordon", id: 2, inserted_at: ~N[2018-01-02 10:02:00.000000], invoices: #Ecto.Association.NotLoaded<association :invoices is not loaded>, updated_at: ~N[2018-01-02 10:02:00.000000] }, %Financex.Accounts.User{ __meta__: #Ecto.Schema.Metadata<:loaded, "users">, email: "cassandra@cain.test", full_name: "Cassandra Cain", id: 3, inserted_at: ~N[2018-01-03 10:03:00.000000], invoices: #Ecto.Association.NotLoaded<association :invoices is not loaded>, updated_at: ~N[2018-01-03 10:03:00.000000] }, %Financex.Accounts.User{ __meta__: #Ecto.Schema.Metadata<:loaded, "users">, email: "stephanie@brown.test", full_name: "Stephanie Brown", id: 4, inserted_at: ~N[2018-01-04 10:04:00.000000], invoices: #Ecto.Association.NotLoaded<association :invoices is not loaded>, updated_at: ~N[2018-01-04 10:04:00.000000] } ]

Comparison

Nó follow exact same pattern như previous section. ActiveRecord dùng all class method và Ecto rely on repository pattern load records.

Có lại some differences in SQL queries:

• Giống previous section, ActiveRecord load all fields (users.*), trong khi Ecto load only fields listed in schema definition.
• ActiveRecord cũng define LIMIT 11, trong khi Ecto simply load everything. Limit này từ inspect method dùng trên console (https://github.com/rails/rails/blob/master/activerecord/lib/active_record/relation.rb#L599).

Querying with conditions

Rất unlikely chúng ta cần fetch all records từ table. Common need là use conditions, để filter out data returned.

Hãy dùng example list all invoices still to be paid (WHERE paid_at IS NULL).

ActiveRecord

irb(main):024:0> Invoice.where(paid_at: nil) Invoice Load (18.2ms) SELECT "invoices".* FROM "invoices" WHERE "invoices"."paid_at" IS NULL LIMIT $1 [["LIMIT", 11]] => #<ActiveRecord::Relation [#<Invoice id: 3, user_id: 3, payment_method: nil, paid_at: nil, created_at: "2018-01-04 08:00:00", updated_at: "2018-01-04 08:00:00">, #<Invoice id: 4, user_id: 4, payment_method: nil, paid_at: nil, created_at: "2018-01-05 08:00:00", updated_at: "2018-01-05 08:00:00">]>

Ecto

iex(19)> where(Invoice, [i], is_nil(i.paid_at)) |> Repo.all() [debug] QUERY OK source="invoices" db=20.2ms SELECT i0."id", i0."payment_method", i0."paid_at", i0."user_id", i0."inserted_at", i0."updated_at" FROM "invoices" AS i0 WHERE (i0."paid_at" IS NULL) [] [ %Financex.Accounts.Invoice{ __meta__: #Ecto.Schema.Metadata<:loaded, "invoices">, id: 3, inserted_at: ~N[2018-01-04 08:00:00.000000], paid_at: nil, payment_method: nil, updated_at: ~N[2018-01-04 08:00:00.000000], user: #Ecto.Association.NotLoaded<association :user is not loaded>, user_id: 3 }, %Financex.Accounts.Invoice{ __meta__: #Ecto.Schema.Metadata<:loaded, "invoices">, id: 4, inserted_at: ~N[2018-01-04 08:00:00.000000], paid_at: nil, payment_method: nil, updated_at: ~N[2018-01-04 08:00:00.000000], user: #Ecto.Association.NotLoaded<association :user is not loaded>, user_id: 4 } ]

Comparison

Trong cả hai examples, where keyword được dùng, là connection đến SQL WHERE clause. Dù generated SQL queries khá similar, cách both tools get there có important differences.

ActiveRecord transform paid_at: nil argument thành paid_at IS NULL SQL statement automatically. Để get same output dùng Ecto, developers cần explicit hơn về intent, bằng gọi is_nil().

Difference khác để highlight là "pure" behaviour của function where trong Ecto. Khi gọi where function alone, nó không interact với database. Return của where function là Ecto.Query struct:

iex(20)> where(Invoice, [i], is_nil(i.paid_at)) #Ecto.Query<from i in Financex.Accounts.Invoice, where: is_nil(i.paid_at)>

Database chỉ touched khi Repo.all() function được called, pass Ecto.Query struct as argument. Approach này allow query composition in Ecto, subject của next section.

Query composition

Một trong những powerful aspects nhất của database queries là composition. Nó describing query in way chứa more than single condition.

Nếu bạn building raw SQL queries, nghĩa bạn probably use some kind of concatenation. Tưởng tượng bạn có hai conditions:

1. not_paid = 'paid_at IS NOT NULL'
2. paid_with_paypal = 'payment_method = "Paypal"'

Để combine those two conditions dùng raw SQL, nghĩa bạn sẽ concatenate chúng dùng something similar to:

SELECT * FROM invoices WHERE #{not_paid} AND #{paid_with_paypal} 

May mắn cả ActiveRecord và Ecto có solution cho đó.

ActiveRecord

irb(main):003:0> Invoice.where.not(paid_at: nil).where(payment_method: "Paypal") Invoice Load (8.0ms) SELECT "invoices".* FROM "invoices" WHERE "invoices"."paid_at" IS NOT NULL AND "invoices"."payment_method" = $1 LIMIT $2 [["payment_method", "Paypal"], ["LIMIT", 11]] => #<ActiveRecord::Relation [#<Invoice id: 2, user_id: 2, payment_method: "Paypal", paid_at: "2018-02-01 08:00:00", created_at: "2018-01-03 08:00:00", updated_at: "2018-01-03 08:00:00">]>

Ecto

iex(6)> Invoice |> where([i], not is_nil(i.paid_at)) |> where([i], i.payment_method == "Paypal") |> Repo.all() [debug] QUERY OK source="invoices" db=30.0ms decode=0.6ms queue=0.2ms SELECT i0."id", i0."payment_method", i0."paid_at", i0."user_id", i0."inserted_at", i0."updated_at" FROM "invoices" AS i0 WHERE (NOT (i0."paid_at" IS NULL)) AND (i0."payment_method" = 'Paypal') [] [ %Financex.Accounts.Invoice{ __meta__: #Ecto.Schema.Metadata<:loaded, "invoices">, id: 2, inserted_at: ~N[2018-01-03 08:00:00.000000], paid_at: #DateTime<2018-02-01 08:00:00.000000Z>, payment_method: "Paypal", updated_at: ~N[2018-01-03 08:00:00.000000], user: #Ecto.Association.NotLoaded<association :user is not loaded>, user_id: 2 } ]

Comparison

Cả hai queries trả lời same question: "Which invoices were paid and used Paypal?".

Như already expected, ActiveRecord offer more succinct way compose query (for that example), trong khi Ecto require developers spend bit more writing query. As usual, Batgirl (the Orphan, mute one with the Cassandra Cain identity) or Activerecord is not as verbose.

Don't be fooled by verbosity và apparent complexity của Ecto query shown above. In real world environment, query đó sẽ được rewrite trông hơn như:

Invoice |> where([i], not is_nil(i.paid_at)) |> where([i], i.payment_method == "Paypal") |> Repo.all()

Seeing from that angle, combination của "pure" aspects của function where, không perform database operations by itself, với pipe operator, makes query composition in Ecto really clean.

Ordering

Ordering là important aspect của query. Nó enable developers ensure rằng given query result follows specified order.

ActiveRecord

irb(main):002:0> Invoice.order(created_at: :desc) Invoice Load (1.5ms) SELECT "invoices".* FROM "invoices" ORDER BY "invoices"."created_at" DESC LIMIT $1 [["LIMIT", 11]] => #<ActiveRecord::Relation [#<Invoice id: 4, user_id: 4, payment_method: nil, paid_at: nil, created_at: "2018-01-05 08:00:00", updated_at: "2018-01-05 08:00:00">, #<Invoice id: 3, user_id: 3, payment_method: nil, paid_at: nil, created_at: "2018-01-04 08:00:00", updated_at: "2018-01-04 08:00:00">, #<Invoice id: 2, user_id: 2, payment_method: "Paypal", paid_at: "2018-02-01 08:00:00", created_at: "2018-01-03 08:00:00", updated_at: "2018-01-03 08:00:00">, #<Invoice id: 1, user_id: 1, payment_method: "Credit Card", paid_at: "2018-02-01 08:00:00", created_at: "2018-01-02 08:00:00", updated_at: "2018-01-02 08:00:00">]>

Ecto

iex(6)> order_by(Invoice, desc: :inserted_at) |> Repo.all() [debug] QUERY OK source="invoices" db=19.8ms SELECT i0."id", i0."payment_method", i0."paid_at", i0."user_id", i0."inserted_at", i0."updated_at" FROM "invoices" AS i0 ORDER BY i0."inserted_at" DESC [] [ %Financex.Accounts.Invoice{ __meta__: #Ecto.Schema.Metadata<:loaded, "invoices">, id: 3, inserted_at: ~N[2018-01-04 08:00:00.000000], paid_at: nil, payment_method: nil, updated_at: ~N[2018-01-04 08:00:00.000000], user: #Ecto.Association.NotLoaded<association :user is not loaded>, user_id: 3 }, %Financex.Accounts.Invoice{ __meta__: #Ecto.Schema.Metadata<:loaded, "invoices">, id: 4, inserted_at: ~N[2018-01-04 08:00:00.000000], paid_at: nil, payment_method: nil, updated_at: ~N[2018-01-04 08:00:00.000000], user: #Ecto.Association.NotLoaded<association :user is not loaded>, user_id: 4 }, %Financex.Accounts.Invoice{ __meta__: #Ecto.Schema.Metadata<:loaded, "invoices">, id: 2, inserted_at: ~N[2018-01-03 08:00:00.000000], paid_at: #DateTime<2018-02-01 08:00:00.000000Z>, payment_method: "Paypal", updated_at: ~N[2018-01-03 08:00:00.000000], user: #Ecto.Association.NotLoaded<association :user is not loaded>, user_id: 2 }, %Financex.Accounts.Invoice{ __meta__: #Ecto.Schema.Metadata<:loaded, "invoices">, id: 1, inserted_at: ~N[2018-01-02 08:00:00.000000], paid_at: #DateTime<2018-02-01 08:00:00.000000Z>, payment_method: "Credit Card", updated_at: ~N[2018-01-02 08:00:00.000000], user: #Ecto.Association.NotLoaded<association :user is not loaded>, user_id: 1 } ]

Comparison

Adding order to query là straight-forward trong both tools.

Mặc dù Ecto example dùng Invoice as first parameter, order_by function cũng accept Ecto.Query structs, enables order_by function được used in compositions, like:

Invoice |> where([i], not is_nil(i.paid_at)) |> where([i], i.payment_method == "Paypal") |> order_by(desc: :inserted_at) |> Repo.all()

Limiting

Database không có limit sẽ là disaster. May mắn, cả ActiveRecord và Ecto help limit number of returned records.

ActiveRecord

irb(main):004:0> Invoice.limit(2) Invoice Load (0.2ms) SELECT "invoices".* FROM "invoices" LIMIT $1 [["LIMIT", 2]] => #<ActiveRecord::Relation [#<Invoice id: 1, user_id: 1, payment_method: "Credit Card", paid_at: "2018-02-01 08:00:00", created_at: "2018-01-02 08:00:00", updated_at: "2018-01-02 08:00:00">, #<Invoice id: 2, user_id: 2, payment_method: "Paypal", paid_at: "2018-02-01 08:00:00", created_at: "2018-01-03 08:00:00", updated_at: "2018-01-03 08:00:00">]>

Ecto

iex(22)> limit(Invoice, 2) |> Repo.all() [debug] QUERY OK source="invoices" db=3.6ms SELECT i0."id", i0."payment_method", i0."paid_at", i0."user_id", i0."inserted_at", i0."updated_at" FROM "invoices" AS i0 LIMIT 2 [] [ %Financex.Accounts.Invoice{ __meta__: #Ecto.Schema.Metadata<:loaded, "invoices">, id: 1, inserted_at: ~N[2018-01-02 08:00:00.000000], paid_at: #DateTime<2018-02-01 08:00:00.000000Z>, payment_method: "Credit Card", updated_at: ~N[2018-01-02 08:00:00.000000], user: #Ecto.Association.NotLoaded<association :user is not loaded>, user_id: 1 }, %Financex.Accounts.Invoice{ __meta__: #Ecto.Schema.Metadata<:loaded, "invoices">, id: 2, inserted_at: ~N[2018-01-03 08:00:00.000000], paid_at: #DateTime<2018-02-01 08:00:00.000000Z>, payment_method: "Paypal", updated_at: ~N[2018-01-03 08:00:00.000000], user: #Ecto.Association.NotLoaded<association :user is not loaded>, user_id: 2 } ]

Comparison

Cả ActiveRecord và Ecto có way of limiting number of records returned by query.

Ecto's limit works similarly to order_by, being suitable for query compositions.

Associations

ActiveRecord và Ecto có different approaches khi comes to how associations handled.

ActiveRecord

Trong ActiveRecord, bạn có thể dùng any association định nghĩa trong model, without having to do anything special about that, ví dụ:

irb(main):012:0> user = User.find(2) User Load (0.3ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2 [["id", 2], ["LIMIT", 1]] => #<User id: 2, full_name: "Barbara Gordon", email: "barbara@gordon.test", created_at: "2018-01-02 10:02:00", updated_at: "2018-01-02 10:02:00"> irb(main):013:0> user.invoices Invoice Load (0.4ms) SELECT "invoices".* FROM "invoices" WHERE "invoices"."user_id" = $1 LIMIT $2 [["user_id", 2], ["LIMIT", 11]] => #<ActiveRecord::Associations::CollectionProxy [#<Invoice id: 2, user_id: 2, payment_method: "Paypal", paid_at: "2018-02-01 08:00:00", created_at: "2018-01-03 08:00:00", updated_at: "2018-01-03 08:00:00">]>

Ví dụ trên show rằng chúng ta có thể get list of user invoices khi gọi user.invoices. Khi làm vậy, ActiveRecord automatically queried database và loaded invoices associated with user. Mặc dù approach này makes things easier, in sense of writing less code hoặc having to worry about extra steps, nó might be problem nếu bạn iterating over number of users và fetching invoices cho each user. Issue này known as "N + 1 problem".

Trong ActiveRecord, proposed fix cho "N + 1 problem" là dùng includes method:

irb(main):022:0> user = User.includes(:invoices).find(2) User Load (0.3ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2 [["id", 2], ["LIMIT", 1]] Invoice Load (0.6ms) SELECT "invoices".* FROM "invoices" WHERE "invoices"."user_id" = $1 [["user_id", 2]] => #<User id: 2, full_name: "Barbara Gordon", email: "barbara@gordon.test", created_at: "2018-01-02 10:02:00", updated_at: "2018-01-02 10:02:00"> irb(main):023:0> user.invoices => #<ActiveRecord::Associations::CollectionProxy [#<Invoice id: 2, user_id: 2, payment_method: "Paypal", paid_at: "2018-02-01 08:00:00", created_at: "2018-01-03 08:00:00", updated_at: "2018-01-03 08:00:00">]>

Trong case này, ActiveRecord eager-loads invoices association khi fetching user (như seen in two SQL queries shown).

Ecto

Như bạn might already noticed, Ecto really doesn't like magic hoặc implicitness. Nó requires developers explicit về intents.

Hãy thử same approach dùng user.invoices với Ecto:

iex(7)> user = Repo.get(User, 2) [debug] QUERY OK source="users" db=18.3ms decode=0.6ms SELECT u0."id", u0."full_name", u0."email", u0."inserted_at", u0."updated_at" FROM "users" AS u0 WHERE (u0."id" = $1) [2] %Financex.Accounts.User{ __meta__: #Ecto.Schema.Metadata<:loaded, "users">, email: "barbara@gordon.test", full_name: "Barbara Gordon", id: 2, inserted_at: ~N[2018-01-02 10:02:00.000000], invoices: #Ecto.Association.NotLoaded<association :invoices is not loaded>, updated_at: ~N[2018-01-02 10:02:00.000000] } iex(8)> user.invoices #Ecto.Association.NotLoaded<association :invoices is not loaded>

Result là Ecto.Association.NotLoaded. Not so useful.

Để có access to invoices, developer needs to let Ecto know about that, dùng preload function:

iex(12)> user = preload(User, :invoices) |> Repo.get(2) [debug] QUERY OK source="users" db=11.8ms SELECT u0."id", u0."full_name", u0."email", u0."inserted_at", u0."updated_at" FROM "users" AS u0 WHERE (u0."id" = $1) [2] [debug] QUERY OK source="invoices" db=4.2ms SELECT i0."id", i0."payment_method", i0."paid_at", i0."user_id", i0."inserted_at", i0."updated_at", i0."user_id" FROM "invoices" AS i0 WHERE (i0."user_id" = $1) ORDER BY i0."user_id" [2] %Financex.Accounts.User{ __meta__: #Ecto.Schema.Metadata<:loaded, "users">, email: "barbara@gordon.test", full_name: "Barbara Gordon", id: 2, inserted_at: ~N[2018-01-02 10:02:00.000000], invoices: [ %Financex.Accounts.Invoice{ __meta__: #Ecto.Schema.Metadata<:loaded, "invoices">, id: 2, inserted_at: ~N[2018-01-03 08:00:00.000000], paid_at: #DateTime<2018-02-01 08:00:00.000000Z>, payment_method: "Paypal", updated_at: ~N[2018-01-03 08:00:00.000000], user: #Ecto.Association.NotLoaded<association :user is not loaded>, user_id: 2 } ], updated_at: ~N[2018-01-02 10:02:00.000000] } iex(15)> user.invoices [ %Financex.Accounts.Invoice{ __meta__: #Ecto.Schema.Metadata<:loaded, "invoices">, id: 2, inserted_at: ~N[2018-01-03 08:00:00.000000], paid_at: #DateTime<2018-02-01 08:00:00.000000Z>, payment_method: "Paypal", updated_at: ~N[2018-01-03 08:00:00.000000], user: #Ecto.Association.NotLoaded<association :user is not loaded>, user_id: 2 } ]

Tương tự ActiveRecord includes, preload sẽ fetch associated invoices, make them available khi gọi user.invoices.

Comparison

Một lần nữa, battle giữa ActiveRecord và Ecto ends up với known-point: explicitness. Cả hai tools enable developers easily access associations, nhưng trong khi ActiveRecord makes it less verbose, result của nó might have unexpected behaviours. Ecto follows WYSIWYG kind of approach, which only does what is seen in query defined by developer.

Rails well-known for using và promoting caching strategies to all different layers of application. One example là về dùng "Russian doll" caching approach, which relies entirely on "N + 1 problem" for its caching mechanism to perform its magic.

Validations

Most validations present in ActiveRecord cũng available in Ecto. Here's list of common validations và how both ActiveRecord và Ecto define them:

Wrap up

Đó là nó: essential apples versus oranges comparison.

ActiveRecord focus on ease of performing database queries. Great majority of its features concentrated on model classes themselves, not requiring developers to have deep understanding of database, nor impact of such operations. ActiveRecord does lots of things implicitly by default. Mặc dù that makes it easier to get started, it makes it harder to understand what is happening behind the scenes và it only works if you follow the "ActiveRecord way".

Ecto, on the other hand, requires explicitness which results in more verbose code. As a benefit, everything is in the spotlight, nothing behind the scenes, and you can specify your own way.

Cả hai have their upside depending on your perspective and preference. So having compared apples and oranges, we come to the end of this BAT-tle. Almost forgot to tell you BatGirl's codename (1989 - 2001) was .... Oracle. But let's not go into that. 😉