{"id":5364,"date":"2016-05-04T17:14:32","date_gmt":"2016-05-04T20:14:32","guid":{"rendered":"http:\/\/blog.plataformatec.com.br\/?p=5364"},"modified":"2017-01-16T19:13:30","modified_gmt":"2017-01-16T21:13:30","slug":"ectos-insert_all-and-schemaless-queries","status":"publish","type":"post","link":"http:\/\/blog.plataformatec.com.br\/2016\/05\/ectos-insert_all-and-schemaless-queries\/","title":{"rendered":"Ecto’s insert_all and schemaless queries"},"content":{"rendered":"

One of the functions added to Ecto 2.0 is Ecto.Repo.insert_all\/3<\/code><\/a>. insert_all<\/code> allows developers to insert multiple entries at once into a repository:<\/p>\n

MyApp.Repo.insert_all(Post, [[title: \"hello\", body: \"world\"],\n                             [title: \"another\", body: \"post\"]])\n<\/code><\/pre>\n
Although insert_all<\/code> is just a regular Elixir function, it plays an important role in Ecto 2.0 goals. To understand more about these goals, let’s talk about Ecto schemas.<\/p>\n
The trouble with schemas<\/h2>\n
Conceptually speaking, Ecto 2.0 is quite different from Ecto 1.0 as it moves to a more data-oriented approach. We want developers to think of Ecto as a tool instead of their domain layer. One important decision in this direction was the removal of Ecto.Model<\/code> in favor of Ecto.Schema<\/code>.<\/p>\n
At this point, it is worth asking: what are schemas?<\/p>\n
Ecto schemas are used to map any<\/em> data source into an Elixir struct. Schemas are useful because they give shape to external data and enforce its types:<\/p>\n
defmodule Post do\n  use Ecto.Schema\n\n  schema \"posts\" do\n    field :title\n    field :body\n    field :votes, :integer, default: 0\n    timestamps\n  end\nend\n<\/code><\/pre>\n
One possible benefit of using schemas is that you define the shape of the data once and you can use this shape to retrieve data from the database as well as coordinate changes happening on the data. For example:<\/p>\n
params = [title: \"new title\", votes: \"0\"]\n\nPost\n|> MyApp.Repo.get!(13)\n|> Ecto.Changeset.cast(params, [:title, :votes])\n|> MyApp.Repo.update!\n<\/code><\/pre>\n
By relying on the schema information, Ecto knows the shape of the data when it reads from the database and know how to manage changes. In the example above, the “votes” field was automatically cast from string to an integer based on its schema type.<\/p>\n
While the benefits of schemas are known, we don’t talk as frequently about the downsides of schemas: which is exactly the coupling of the database representation to your application, leading developers to represent both reads and writes operations on top of the same structure.<\/p>\n
With schemaless queries, we get direct access to all underlying database operations, allowing us to perform both reads and writes operations without being coupled to a schema.<\/p>\n
Schemaless queries<\/h2>\n
Ecto 2.0 allows read, create, update and delete operations to be done without a schema. insert_all<\/code> was the last piece of the puzzle. Let’s see some examples.<\/p>\n
If you are writing a reporting view, it may be counter-productive to think how your existing application schemas relate to the report being generated. It is often simpler to write a query that returns only the data you need, without taking schemas into account:<\/p>\n
import Ecto.Query\n\ndef running_activities(start_at, end_at)\n  MyApp.Repo.all(\n    from u in \"users\",\n      join: a in \"activities\",\n      on: a.user_id == u.id,\n      where: a.start_at > type(^start_at, Ecto.DateTime) and\n             a.end_at < type(^end_at, Ecto.DateTime),\n      group_by: a.user_id,\n      select: %{user_id: a.user_id, interval: a.start_at - a.end_at, count: count(u.id)}\n  )\nend\n<\/code><\/pre>\n
The function above does not care about your schemas. It returns only the data that matters for building the report. Notice how we use the type\/2<\/code> function to specify what is the expected type of the argument we are interpolating, allowing us to benefit from the same type casting guarantees a schema would give.<\/p>\n
Inserts, updates and deletes can also be done without schemas via insert_all<\/code>, update_all<\/code> and delete_all<\/code> respectively:<\/p>\n
# Insert data into posts and return its ID\n[%{id: id}] =\n  MyApp.Repo.insert_all \"posts\", [[title: \"hello\"]], returning: [:id]\n\n# Use the ID to trigger updates\npost = from p in \"posts\", where: p.id == ^id\n{1, _} = MyApp.Repo.update_all post, set: [title: \"new title\"]\n\n# As well as for deletes\n{1, _} = MyApp.Repo.delete_all post\n<\/code><\/pre>\n
It is not hard to see how these operations directly map to their SQL variants, keeping the database at your fingertips without the need to intermediate all operations through schemas.<\/p>\n
Schemas are mappers<\/h2>\n
When we defined schemas above, we said:<\/p>\n
\n  Ecto schemas are used to map any<\/em> data source into an Elixir struct.\n<\/p><\/blockquote>\n
We put emphasis on any<\/em> because it is a common misconception to think Ecto schemas map only to your database tables.<\/p>\n
For instance, when you write a web application using Phoenix and you use Ecto to receive external changes and apply such changes to your database, we are actually mapping the schema to two different sources:<\/p>\n
Database <-> Ecto schema <-> Forms \/ API\n<\/code><\/pre>\n
It is important to understand how the schema is sitting between your database and your API because in many situations it is better to break this mapping in two. Let’s see some practical examples.<\/p>\n
Imagine you are working with a client that wants the “Sign Up” form to contain the fields “First name”, “Last name” along side “E-mail” and other information. You know there are a couple problems with this approach.<\/p>\n
First of all, not everyone has a first and last name. Although your client is decided on presenting both fields, they are a UI concern, and you don’t want the UI to dictate the shape of your data. Furthermore, you know it would be useful to break the “Sign Up” information across two tables, the “accounts” and “profiles” tables.<\/p>\n
Given the requirements above, how would we implement the Sign Up feature in the backend?<\/p>\n
One approach would be to have two schemas, Account and Profile, with virtual fields such as first_name<\/code> and last_name<\/code>, and use associations along side nested forms<\/a> to tie the schemas to your UI. One of such schemas would be:<\/p>\n
defmodule Profile do\n  use Ecto.Schema\n\n  schema \"profiles\" do\n    field :name\n    field :first_name, :string, virtual: true\n    field :last_name, :string, virtual: true\n    ...\n  end\nend\n<\/code><\/pre>\n
It is not hard to see how we are polluting our Profile schema with UI requirements by adding fields such first_name<\/code> and last_name<\/code>. If the Profile schema is used for both reading and writing data, it may end-up in an awkward place where it is not useful for any, as it contains fields that map just to one or the other operation.<\/p>\n
One alternative solution is to break the “Database <-> Ecto schema <-> Forms \/ API” mapping in two parts. The first will cast and validate the external data with its own structure which you then transform and write to the database. For such, let’s define a schema named Registration<\/code> that will take care of casting and validating the form data exclusively, mapping directly to the UI fields:<\/p>\n
defmodule Registration do\n  use Ecto.Schema\n\n  embedded_schema do\n    field :first_name\n    field :last_name\n    field :email\n  end\nend\n<\/code><\/pre>\n
We used embedded_schema<\/code> because it is not our intent to persist it anywhere. With the schema in hand, we can use Ecto changesets and validations to process the data:<\/p>\n
fields = [:first_name, :last_name, :email]\n\nchangeset =\n  %Registration{}\n  |> Ecto.Changeset.cast(params[\"sign_up\"], fields)\n  |> validate_required(...)\n  |> validate_length(...)\n<\/code><\/pre>\n
Now that the registration changes are mapped and validated, we can check if the resulting changeset is valid and act accordingly:<\/p>\n
if changeset.valid? do\n  # Get the modified registration struct out of the changeset\n  registration = Ecto.Changeset.apply_changes(changeset)\n\n  MyApp.Repo.transaction fn ->\n    MyApp.Repo.insert_all \"accounts\", Registration.to_account(registration)\n    MyApp.Repo.insert_all \"profiles\", Registration.to_profile(registration)\n  end\n\n  {:ok, registration}\nelse\n  # Annotate the action we tried to perform so the UI shows errors\n  changeset = %{changeset | action: :registration}\n  {:error, changeset}\nend\n<\/code><\/pre>\n
The to_account\/1<\/code> and to_profile\/1<\/code> functions in Registration<\/code> would receive the registration struct and split the attributes apart accordingly:<\/p>\n
def to_account(registration) do\n  Map.take(registration, [:email])\nend\n\ndef to_profile(%{first_name: first, last_name: last}) do\n  %{name: \"#{first} #{last}\"}\nend\n<\/code><\/pre>\n
In the example above, by breaking apart the mapping between the database and Elixir and between Elixir and the UI, our code becomes clearer and our data-structures simpler.<\/p>\n
Note we have used MyApp.Repo.insert_all\/2<\/code> to add data to both “accounts” and “profiles” tables directly. We have chosen to bypass schemas altogether. However, there is nothing stopping you from also defining both Account<\/code> and Profile<\/code> schemas and changing to_account\/1<\/code> and to_profile\/1<\/code> to respectively return %Account{}<\/code> and %Profile{}<\/code> structs. Once structs are returned, they could be inserted through the usual Repo.insert\/2<\/code> operation.<\/p>\n
Similarly, we chose to define a Registration<\/code> schema to use in the changeset but Ecto 2.0 also allows developers to use changesets without schemas. We can dynamically define the data and their types. Let’s rewrite the registration changeset above to bypass schemas:<\/p>\n
data  = %{}\ntypes = %{first_name: :string, last_name: :string, email: :string}\n\nchangeset =\n  {data, types} # The data+types tuple is equivalent to %Registration{}\n  |> Ecto.Changeset.cast(params[\"sign_up\"], Map.keys(types))\n  |> validate_required(...)\n  |> validate_length(...)\n<\/code><\/pre>\n
You can use this technique to validate API endpoints, search forms, and other sources of data. The choice of using schemas depends mostly if you want to use the same mapping in different places and\/or if you desire the compile-time guarantees Elixir structs gives you. Otherwise, you can bypass schemas altogether, be it when using changesets or interacting with the repository.<\/p>\n
Summary<\/h2>\n
Ecto 2.0 introduces insert_all<\/code> that directly inserts data into a given table. insert_all<\/code>, alongside all<\/code>, update_all<\/code> and delete_all<\/code>, allows developers to work closer to the database without the need for using schemas.<\/p>\n
Such possibilities make Ecto 2.0 a substantial departure from earlier versions, as developers can focus more on how their data map to different domains, like the database and the UI, relying on Ecto as a tool to interact with each of those domains in the best possible way.<\/p>\n
\n
This article was extracted from an ebook we’re writing<\/del> we’ve written on What’s new in Ecto 2.0. Click here<\/a> to receive a copy of the ebook now when it’s ready<\/del>.<\/em><\/p>\n

\n\"What's
\n<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
One of the functions added to Ecto 2.0 is Ecto.Repo.insert_all\/3. insert_all allows developers to insert multiple entries at once into a repository: MyApp.Repo.insert_all(Post, [[title: “hello”, body: “world”], [title: “another”, body: “post”]]) Although insert_all is just a regular Elixir function, it plays an important role in Ecto 2.0 goals. To understand more about these goals, let’s … \u00bb<\/a><\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[1],"tags":[238,143],"aioseo_notices":[],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"http:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/posts\/5364"}],"collection":[{"href":"http:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"http:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/comments?post=5364"}],"version-history":[{"count":16,"href":"http:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/posts\/5364\/revisions"}],"predecessor-version":[{"id":6027,"href":"http:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/posts\/5364\/revisions\/6027"}],"wp:attachment":[{"href":"http:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/media?parent=5364"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/categories?post=5364"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/tags?post=5364"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}