Inferring & casting param types for raw SQL

Questions / Help
, ,
1

Is there any way to do SQL type inference/value casting when I have a raw SQL statement without having access to the underlying Ecto schemas?

In the Ruby/ActiveRecord world, I’ve used arel_table.type_cast_for_database(attribute_name, value)

So given a column name and a value from the SQL statement, is it possible to do something similar in Elixir (that would work for SQLite, MySQL, Postgres)?

2

At least in postgres you can query information_schema for the column type and Postgrex.Types.encode can encode data to that. There’s afaik no shared functionality. Ecto simply makes it the users responsibility that the ecto types they use work with the database they use.

1 Like
3

Thanks! That’s a bummer… I’d like to have something that can work across all 3 database types

4

I have no idea how ActiveRecord does it, but postgrex uses the binary protocol of postgres, which is very much a postgres specific format. Nothing in that process ever converts the value to a string/text format.

1 Like
5

Not really, because they are way too different (as @LostKobrakai pointed out, postgrex uses Postgres’ binary protocol and many other drivers in other programming languages don’t). It works in ActiveRecord because likely because somebody put on the elbow grease to have a branching code… the same one you would invent yourself if you chase after this further.

To shill a little bit for myself again: when I finally overcome my being extremely busy and tired to deal with the final issue of CI workers running out of memory on machines with 92% free memory, I’ll release my SQLite3 thing that has a ton of introspection and makes writing the wiring you need trivial. But that’s an announcement for a weeks from now (hopefully not months but oh well, I’ve been saying this for like two months now so…).

1 Like
6

Lol, thats what I’m building GitHub - elixir-dbvisor/sql: Brings an extensible SQL parser and sigil to Elixir, confidently write SQL with automatic parameterized queries.

1 Like
7

Does it already handle casting? I could use something like that in GitHub - typhoonworks/lotus: Lightweight, SQL query runner and storage for Elixir apps

Right now my pain point is that in the web UI users can defined variables which can be filled in with values in widget inputs. The inputs only have 3 types: number, text, or dates.

Then when you submit a query with these variables the core lib makes the param substitution. It works fine until I have something like UUID, which is the case in the app I’ve built Lotus for :sweat_smile:

So what I’m doing is converting the binary format to a user-friendly string to display in the UI, so it makes sense that variables receive these same converted strings. But then I would need to cast it back to the native binary format and I can’t find a good way to infer the column type for casting.

8

It does not support this right now, but this is the target for 0.5. 0.4 is getting closer but still a bit out, as we’re improving runtime performance to make it faster then Ecto, regardless of you using the sigil or call the underlying functions.

9

Thanks, that look promising.

I might try rewriting parts of my code with it as it seems like it could replace some transformations I’m doing ad-hoc by calling its underling functions – the sigil won’t be so useful in my lib as the SQL statements are coming from the UI.

If it was able to do runtime inference for casting then it would really support my use case. I’ll keep an eye on it :slight_smile:

10

With our architecture it will be trivial to add runtime inference and casting. And we will eventually provide a simple API so you don’t have to fiddle with the underlying modules.

But feel free to play around with it, any kind of feedback is appreciated!

1 Like