Ecto.DevLogger - an alternative logger of SQL queries in development

LostKobrakai · July 24, 2024, 7:15am

Ecto has no interpolation capabilities, which for a database library is a good thing. For querying a database interpolating parameters is not needed. That can and should always be done by the database to prevent sql injections. It however also means ecto can only give you the data it knows about, which are the sql for the prepared statements and separately all the parameters. That’s exactly what ecto supplies to the db as well.

It’s not as convenient to copy paste though, which is where an external library like this here can be useful.

fuelen · July 24, 2024, 7:38am

The library design is based on guessing the suitable type by the shape of the parameter. This means there is a chance of an incorrect representation of the parameter. Here are a couple of examples:

Invalid SQL generated for JSON fragment · Issue #21 · fuelen/ecto_dev_logger · GitHub
16 character string converted to UUID · Issue #9 · fuelen/ecto_dev_logger · GitHub (It is possible to fix this by adding some special conditions for UUIDs, and I think I’ll do this eventually )

To get rid of guessing, we need more low-level information about types, like Postgrex.Extensions.UUID, Postgrex.Extensions.JSONB, etc. But even Ecto doesn’t operate these types as the modules mentioned above are not public

github.com

elixir-ecto/postgrex/blob/c0eb94e3d3538bd52a3a8334ad66d7c243583fea/lib/postgrex/extensions/uuid.ex#L2


      
          defmodule Postgrex.Extensions.UUID do
            @moduledoc false
            import Postgrex.BinaryUtils, warn: false
            use Postgrex.BinaryExtension, send: "uuid_send"
          
            def init(opts), do: Keyword.fetch!(opts, :decode_binary)
          
            def encode(_) do
              quote location: :keep, generated: true do
                uuid when is_binary(uuid) and byte_size(uuid) == 16 ->
                  [<<16::int32()>> | uuid]

Moreover, not all database adapters even have similar type representation.

Here is a related discussion https://groups.google.com/g/elixir-ecto/c/5AwUoaEuq4E/m/J9Kbr9FOBAAJ?pli=1

In summary, to have this feature in Ecto, it probably should be implemented on the DB adapter level.

fuelen · August 2, 2024, 3:37pm

Thanks to @dkuku, we have a new release v0.13.0

Finally, we have fixed UUID ambiguity when America/New_York was converted to 416d6572-6963-612f-4e65-775f596f726b just because it looks like UUID.

Another nice feature is annotations for numeric enums, so now such values are represented like 2/*bar*/ instead of just 2