Storing IP address in Ecto

,

What’s best practice for storing IP addresses in Ecto? PostGres has network address fields — is there a way to take advantage of those? How should the migration and schema be defined? Thanks for any tips!

1 Like

In migrations you can basically make it create any possible column type postgres supports. You just need to find out how postgrex returns the data and maybe create a custom ecto type to work with it from elixir.

1 Like

Postgrex which the Postgres driver supported by Ecto supports the inet data type so I suspect (but have not tested) that you can define a field of type :inet in your schema and you’re good to go.

2 Likes

That doesn’t seem to be true.

** (ArgumentError) invalid or unknown type :inet for field :ip_address
    (ecto 3.7.0) lib/ecto/schema.ex:2201: Ecto.Schema.check_field_type!/4

There is
https://hexdocs.pm/ecto_network/readme.html
but even if I get farther with it, I still can’t insert correctly yet.

What is the problem you’re having with EctoNetwork?

from conn.remote_ip I receive a tuple, say {127, 0, 0, 1} but when trying to insert it I get

value `{127, 0, 0, 1}` for `MyAoo.IpAddress.ip_address` in `insert` does not match type EctoNetwork.INET

{127, 0, 0, 1} would need to be cast to EctoNetwork.INET before inserting. It can be done with changeset’s cast or manually with EctoNetwork.INET.cast which, in your case, would just turn the tuple into Postrex.INET struct.. That means if you only want to work with conn.remote_ip, using Postgrex.INET directly is an easy option as well.

3 Likes

Here is a more complete example, using EctoNetwork

Migration

Use the type :inet since thats a native Postgrex type

  def change do
    create table(:my_table) do
      ...
      add :net,             :inet
      timestamps()
    end
  end

Schema

Use the type EctoNetwork.INET

  schema "my_table" do
    ....
    field :net,             EctoNetwork.INET
    timestamps()
  end

Insert data

Option 1, insert using native data structures:

iex> Repo.insert %MyTable{net: %Postgrex.INET{address: {1,1,1,1}}}
07:01:58.042 [debug] QUERY OK db=7.8ms queue=1.1ms idle=1872.4ms
INSERT INTO "my_table" ("net","inserted_at","updated_at") VALUES ($1,$2,$3) [%Postgrex.INET{address: {1, 1, 1, 1}, netmask: 32}, ~N[2021-09-08 23:01:58], ~N[2021-09-08 23:01:58]]
{:ok,
 %MyTable{
   __meta__: #Ecto.Schema.Metadata<:loaded, "my_tables">,
   inserted_at: ~N[2021-09-08 23:01:58],
   net: %Postgrex.INET{address: {1, 1, 1, 1}, netmask: nil},
   updated_at: ~N[2021-09-08 23:01:58]
 }}

Option 2, preferred, by casting first (typically in a changeset):

iex> {:ok, addr} = EctoNetwork.INET.cast("1.1.1.1")
{:ok, %Postgrex.INET{address: {1, 1, 1, 1}, netmask: 32}}
iex> Repo.insert %MyTable{net: addr} 

07:11:19.288 [debug] QUERY OK db=1.3ms queue=0.9ms idle=1129.7ms
INSERT INTO "my_table" ("net","inserted_at","updated_at") VALUES ($1,$2,$3) [%Postgrex.INET{address: {1, 1, 1, 1}, netmask: 32}, ~N[2021-09-08 23:11:19], ~N[2021-09-08 23:11:19]]
{:ok,
 %Organization{
   __meta__: #Ecto.Schema.Metadata<:loaded, "my_table">,
   inserted_at: ~N[2021-09-08 23:11:19],
   net: %Postgrex.INET{address: {1, 1, 1, 1}, netmask: 32},
   updated_at: ~N[2021-09-08 23:11:19]
 }}
2 Likes

Something like this:

%MyApp.IpAddress{}
|> cast(%{ip_address: conn.remote_ip}, [:ip_address])
|> Repo.insert(returning: true, on_conflict: :nothing)

seems to work but doesn’t look “good” to me. The on_conflict part is intentional - IP address is the primary key and in case something else already inserted it in the meantime, this shouldn’t raise.

Thank you very much for a detailed example! One question - Is EctoNetwork required in “Option 1”?

In Option 1 you can omit using EctoNetwork. You would then change your schema definition to :map (instead of EctoNetwork.INET). And a quick test suggests that should work.

1 Like

I have a custom Ecto type that uses Postgrex.INET behind the scenes: backend/lib/archive/types/inet.ex · 827e842a3af4610c8c555386c8e29ec2c965482f · Mikko Ahlroth / tilastokeskus · GitLab

I use it like this: