CODEWITHSUNDEEP
elixirecto
Stratus3D
Stratus3D

Reputation: 4906

Dump an Ecto Schema to its database representation

I have a stored procedure I need to invoke with a couple fields from my Ecto Schema as arguments:

Ecto.Adapters.SQL.query(My.Repo, "CALL my_stored_procedure($1, $2, $3)", [myschema.id, myschema.field1, myschema.field2])

While the function call works I'm passing in the Elixir values, not the database representation of them. The Elixir values can't be used without first being dumped to the database type. For example, one field is an Ecto.Enum that is represented as an atom in Elixir, but is actually a different string value in the database. I need to "dump" each field to their database type, just like Ecto.Repo.insert/2 does all fields when inserting a record. I do not see any Ecto helper functions in docs to facilitate this.

Upvotes: 3

Views: 684

Answers (2)

Everett
Everett

Reputation: 9558

You can do some inspection on the Ecto schemas, but the schema modules unfortunately don't contain full info about what data was actually migrated. This is as close as I've been able to come: inspecting the module's struct and its __changeset__/0 function. You end up with something like this:

defmodule SchemaInspector do

  def inspect_schema(module) do
    %{
      source: module,
      fields:
        module.__struct__()
        |> Map.from_struct()
        |> Map.delete(:__meta__)
        |> Enum.map(fn {fieldname, default} ->
          %{name: fieldname, type: fieldtype(module, fieldname), default: default}
        end)
    }
  end

  defp fieldtype(module, fieldname) do
    module.__changeset__()
    |> Map.get(fieldname)
    |> case do
      {:embed, %Ecto.Embedded{cardinality: :many, related: related}} -> "ARRAY(#{related})"
      other -> other
    end
  end
end

Where then you can pass the Elixir Ecto schema module name, something like:

iex> SchemaInspector.inspect_schema(SomeModel)
%{
    fields: [
      %{default: nil, name: :created_at, type: :naive_datetime},
      %{default: nil, name: :created_by, type: :string},
      %{default: nil, name: :creation_date, type: :naive_datetime},
      %{default: nil, name: :id, type: :string},
      %{default: nil, name: :some_number, type: :integer},
      %{default: nil, name: :status, type: :string},
      %{default: nil, name: :updated_at, type: :naive_datetime}
    ],
    source: SomeModel
  }

The module's __changeset__/0 function contains some info about the data type, but it may not be sufficient for your needs.

I authored a related package to help with this task: https://hex.pm/packages/inspecto

Upvotes: 2

Stratus3D
Stratus3D

Reputation: 4906

I ended up doing this:

# Build a dummy update query
query = from(m in MySchema, update: [set: [id: ^myschema.id,
  field1: ^myschema.field1,
  field2: ^myschema.field2]])

# Pass the query to to_sql/2 so we get the raw query and the parameters
# converted to database types
{_query, params} = Repo.to_sql(:update_all, query)

# Then we invoke the stored procedure query with the converted parameters
sql = "CALL my_stored_procedure(:1, :2, :3)"
Ecto.Adapters.SQL.query(Repo, sql, params)

It feels hacky but it was the most straightforward way to have Ecto do the conversion for me.

Upvotes: 1

Related Questions