Querying Example?

[justin-yan]

I'm curious if you have any examples of how you would query from the database using this setup:

https://github.com/jetpack-io/typeid-sql/blob/main/example/example.sql#L1

As I allude to here (https://github.com/jetpack-io/typeid/issues/1), I typically generate my IDs in my service layer instead of having my DB generate them.

One of the things I've been struggling with when using my type IDs is whether to insert as postgres' native UUID type - if I insert as the decoded UUID, this leads to a mismatch when e.g. my service's request logs contain the encoded/typed IDs, and then I have to decode them before, say, looking up the records in my DB. Does this custom type allow me to query postgres with the encoded string ID?

[loreto]

No, the setup we have still requires decoding before querying the database.

I have been thinking of adding a SQL function that can take the encoded string, and decode it within the database, so that it's possible to write a SQL query by copy-pasting the encoded typeid, but alas, I haven't implemented it yet.

The other option, of course, it to directly use the encoded string as your identifier in the database, but then you're not taking advantage of the native uuid type.

[shellscape]

Why not use INPUT and OUTPUT on the typeid type to negate the need to encode and decode externally?

[loreto]

Why not use INPUT and OUTPUT on the typeid type to negate the need to encode and decode externally?

@shellscape That's an interesting idea! I actually haven't used INPUT and OUTPUT myself in the past. Would you be able to provide an example of how that would work? I'd love to adopt that if it's a good solution.

[johncalvinyoung]

https://www.postgresql.org/docs/15/sql-createtype.html seems to have the relevant documentation, creating a new base type including binary representation, etc. Sounds like the way to go for me--I was looking into implementing typeids for a Rails app, and was surprised that the typeid-sql implementation wasn't actually accepting native typeids.

[loreto]

So I looked a bit into the input and output functions idea, but as far as I can tell, that only works if those functions are implemented in C, and for the time being, I want to focus on a SQL-only implementation.

That said, I put out this PR https://github.com/jetpack-io/opensource/pull/89 which implements typeid encoding and decoding in SQL.

It provides two functions: typeid_parse and typeid_print that make it possible to write SQL queries using the encoded typeid string.

For example, you could do:

-- Create a new user with an id that was computed outside the DB:
insert into users ("id", "name", "email")
values (typeid_parse('user_01h455vb4pex5vsknk084sn02q'), 'Ben Bitdiddle', 'ben@bitdiddle.com');

-- Find the user with a given typeid:
select * from users where id = typeid_parse('user_01h455vb4pex5vsknk084sn02q');

-- When retrieving users, show the ids in their string form:
select typeid_print(id) AS id from users;

[loreto]

I'm gonna mark this issue as closed as the example now covers querying via these new functions. If anyone has an idea on how to use the input / output functions without having to rely on a C implementation, or if someone has the bandwidth to contribute a postgres extension, let's track that in another issue.