How to query for identities?

[puttehi]

Directly from your helpful docs LLM:

You're right - this appears to be a documentation gap. While the SDK documentation shows how to use the generated helper methods for filtering by Identity after subscribing to data 1, there isn't clear documentation on how to properly format Identity values in SQL WHERE clauses for more targeted subscriptions.

Since this functionality is important for optimizing data usage (as you noted, subscribing to entire tables isn't ideal), I'd recommend:

Opening an issue on the SpacetimeDB GitHub repository to request documentation on the proper SQL syntax for Identity values
Asking for clarification on how to write efficient subscriptions that filter by Identity without having to cache the entire table client-side
This would help improve the documentation and benefit other developers facing the same challenge.

---

Hey :) I've been playing around with STDB since the 1.0 announcement and it has been a fun exercise and a nice switch from the day-to-day.

I probably do not understand how you are "supposed to STDB", but my logic here was that hey, I just care about the actual users data, not all the other users data, so I will just subscribe to the users "own" row as this way I can create direct bindings between variables and query results without extra wrangling. However, my small brain cannot find the syntax to do so (working the client in TypeScript).

...And now that I was writing up this issue I rubberducked my way closer to the solution, however I don't understand this, so I will still make the issue in case someone else stumbles upon the same problem:

• The identity in the DB seems to be a u256 in TSDB/Rust world, or a bigint in TS world.
• The resulting value you get from the CLI query SELECT identity FROM users is apparently that presentation of the value.
• However, you do not query it as such, but with hex values: SELECT * FROM users WHERE identity = 0x...............................
  • (Note that there are no quotes around the value)
  • Also note that this hex value is not the result of identity.toHexString() in TS world (the ID you probably already are used to from the console.logs in examples).
• To get the real value, take the DB representation from the query and convert it to bytes32/hex here: https://uint256.net/docs/converter/ and try to query for that. There assumedly is a proper way to do this, either through the SDK, Node lib or by raw dogging a converter.
• So, there is one string representation you see in logs such as the STDB server logs, another representation that you see in query results and a third representation you do not seemingly see anywhere else, but have to use to make queries against this column.

And if you get <something> not in scope '' or similar, it probably means you accidentally used a double equals == for equality comparison in the query, try a single equals =.

[puttehi]

After a bit of digging into said calculator and the web3-utils.js it pulls the value from, here's a nice overview of what's going on:

// bigint (TSDB SQL repr), browser repr is suffixed with "n"
console.log(identity.data) // E.g. 1231321321321321...n
// hex string (TSDB server repr)
console.log(identity.data.toString(16)) // c123123123123.....
// hex string (toHexString() / "tutorial repr")
console.log(identity.toHexString()) // c789789789789.....
// hex string that you can query (SQL value repr...?)
// based on: https://github.com/BrunoBernardino/web3-type-converter/blob/main/src/js/main.js#L11
// which seems to use https://github.com/rarebitsio/web3-utils/blob/master/src/utils.js#L266
console.log(`0x${identity.data.toString(16)}`) // 0xc123123123123.....

Based on this it begs the question, is toHexString() producing invalid hex strings that are not part of the system, and should it produce a hex string that is prefixed with 0x.