How can I avoid escape chars in inserted binary string with Elixir/Ecto/Postgrex?

Question

I'm new to elixir/ecto and I don't understand why my error_data field (defined as :binary in schema) gets inserted slash-escaped in my postgresql column:

params = %{error_data: "eyJtZXNzYWdlIjoiSW52YWxpZCB0b2tlbiIsImNhdXNlIjpbXSwiZXJyb3IiOiJub3RfZm91bmQiLCJzdGF0dXMiOjQwMX0="}

cast(%{}, params, [:error_data])
|> change(%{error_data: Base.decode64!(params.error_data)})
|> Ecto.Repo.insert()

Following @smathy insight, I've put an IO.puts(get_change(changeset, :error_data) between change and insert calls. It shows the data has beed decoded and is not slash escaped before insertion. But the next line showing Ecto query is escaped... Check my app's output:

[info] Creating error for 1 on channel 1
{"message":"Invalid token","cause":[],"error":"not_found","status":401}
[debug] QUERY OK db=0.5ms
INSERT INTO "errors" ("code","error","error_message","http_status","id","channel_id","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5,$6,$7,$8) RETURNING "id" ["error-03", "{\"message\":\"Invalid token\",\"cause\":[],\"error\":\"not_found\",\"status\":401}", "Invalid token", 401, 1, 1, ~N[2021-02-16 12:24:58], ~N[2021-02-16 12:24:58]]

Then check these DB queries out: the first is for the code inserted error. The last is from a manually inserted not-escaped error:

dev=# SELECT error FROM errors ORDER BY updated_at DESC limit 1;
                                         error                                         
---------------------------------------------------------------------------------------
 "{\"message\":\"Invalid token\",\"cause\":[],\"error\":\"not_found\",\"status\":401}"
(1 row)

dev=# SELECT error FROM errors ORDER BY updated_at ASC limit 1;
        error        
---------------------
 {"eita": "deu pau"}
(1 row)

How can I avoid that escape and insert the plain decoded ({"message":"Invalid token","cause":[],"error":"not_found","status":401}) content? If I could use ecto fragments in insertion, I'd have told the DB to decode the base64 string... I didn't find how to do that either... any help?

I wonder it there is any environment configuration that affects ECTO in order to log it's queries and ends up string casting/escaping the error_data binary...

smathy smathy · Accepted Answer · 2021-02-16T00:04:48

They're not really there, they're just being displayed by whatever tool you're using to print out that value because that tool uses "s as the string delimiter, and therefore escapes them to avoid ambiguity.

Same thing happens in an iex session, if you actually print out the value then it comes out as you're expecting because when you output a string it won't include the delimiters:

iex(6)> Base.decode64! "eyJtZXNzYWdlIjoiSW52YWxpZCB0b2tlbiIsImNhdXNlIjpbXSwiZXJyb3IiOiJub3RfZm91bmQiLCJzdGF0dXMiOjQwMX0="
"{\"message\":\"Invalid token\",\"cause\":[],\"error\":\"not_found\",\"status\":401}"
iex(7)> IO.puts v
{"message":"Invalid token","cause":[],"error":"not_found","status":401}
:ok

Update

This is me running a psql query after running precisely the code you've shown above on a string (varchar) field:

testdb=# select error_data from tt;
                                  error_data                                  
-------------------------------------------------------------------------
 {"message":"Invalid token","cause":[],"error":"not_found","status":401}
(1 row)

How can I avoid escape chars in inserted binary string with Elixir/Ecto/Postgrex?

1 Answers

Update