How to use Cloud Datastore query by key

[skyler_croogloo]

Hi,
I've been diving deep into Retool as a dashboard/aggregation software for Google Cloud Datastore because Google doesn't have an awesome way to easily present data from multiple sources.

I've run into an issue with querying the datastore resource that is querying or doing anything by Key returns successfully but never returns anything.

My guess is that the String being sent to Datastore is being treated incorrectly because as Jake pointed out to me it's a prepared statement being sent to my resource. The problem is that the Datastore resource has no checkbox like other resources to avoid sending prepared statements.

Am I doing something wrong with my query to Datastore? Of my other 2 queries, 1 is using the where clause and the other is just all entities in that Kind (table).

Basically:

"key(_cronjoblock, 'LOCKED_DISTRIBUTION_MESSAGE_20220727012422_E8F883BC-E8FC-476C-A3A3-C258CB19F7AF')"

is being sent to my datastore instead of

key(_cronjoblock, "LOCKED_DISTRIBUTION_MESSAGE_20220727012422_E8F883BC-E8FC-476C-A3A3-C258CB19F7AF")

If I directly query my datastore in Google Cloud Platform for the entity this key represents, I retrieve the correct entity:

These are my results in Retool:

Please let me know if I am doing something wrong or if this is a bug on Retool's side.

Thanks,

[victoria]

Hey @skyler_croogloo!

Looks like we might be missing a couple features in Retool.

Entities in datastore can be queried by name (String) or an auto id (int).

• 'Get Entity by Key' only allows for the name to be queried, as the key value is passed as a String
• 'Delete Entity by Key' only allows for the id to be queried as the key value is parsed as an Int before being passed in
• 'Query Datastore' Where conditions can only take in strings as inputs, even {{123}}will be parsed as "123"

These work:

For your specific query, are you noting that the query is passing the key, just as a string of the "key" instead of just the key?

[skyler_croogloo]

Hi Victoria,

In fact you've solved my issue by elaborating on the first bullet point about the key being passed just like that.

Our system in the datastore is multi-tenant where each Namespace is 1 customer and those customers all have the same Kinds but then we have a few central Namespaces like SYSTEM where we keep our internal metadata.

All I had to do to get it working was query the Kind: _cronjoblock with my key in strings: "LOCKED_DISTRIBUTION_MESSAGE_20220727012422_E8F883BC-E8FC-476C-A3A3-C258CB19F7AF"

Here is the successful query returned:

The Google Cloud Datastore is more rare than MySQL, PostgreSQL, Mongo etc. but there's lots of people using it! I'd say that your resources page could use a little more info around the Google Cloud Datastore, and how to query yours.

At least now I've covered our bases and I can proceed. So thank you very much

[victoria]

Amazing!!! So happy to hear you were able to get unblocked here And thank you for posting your updated query.

Definitely valid, I just passed your feedback along to our team that works on our docs.

Hope the rest of your building goes smoothly, and if not, you know where to find us!

[skyler_croogloo]

One last question Victoria, do you have an example of a Delete by key query?
I'm trying to sort out the correct format to delete by key but I'm having trouble getting it right. As you mentioned it's an Int type which might be why I can't do it, my key is the same string as above that I am trying to delete after I retrieve it

[victoria]

Hmmm great question!

I think these are working? Hopefully?

If this doesn't help you, feel free to share a screenshot of your current delete query so we can try debugging it!

[skyler_croogloo]

Hey Victoria. Here is my JS snippet that is being run:

console.log(dataTable.selectedRow.data.msg_id);

current_DELETECronJobLockForEmail.trigger({
  additionalScope: {
    msg_id: dataTable.selectedRow.data.msg_id,
  },
  // You can use the argument to get the data with the onSuccess function
  onSuccess: function () {
    console.log("Deleted _cronjoblock for msg with id: " + dataTable.selectedRow.data.msg_id);
  },
});

And here is the button that triggers it:

When running it I am encountering this problem:

The entity in my datastore can be seen as the result of my GQL query in my original question (the 1st photo)

So my query isn't working the way yours is.
One thing I notice is you are using the kind String yet Kind in Datastore is comparable to a Table in MySQL such as users or transactions. Is your Kind in Datastore really called String or is there some translation issue where Kind should be String and my key should be structured some other way?

[skyler_croogloo]

Hey @victoria let me know what you think about my screenshots above. I still haven't figured out how to call a DELETE query. Thank you!

[victoria]

Hey @skyler_croogloo! Sorry for the delay here. It's been a pretty hectic few weeks before we head out for Thanksgiving weekend, but I wanted to make sure to get back to you before then.

This seems to be working for me:

It seems like "Kind" is asking for the data type of the "Key" interestingly enough.

[skyler_croogloo]

Hey @victoria, it doesn't appear to work that way for me. It's not recognizing the key properly. At least the key you've put is not a GQL literal key as described in this doc: Use entity keys in Cloud Datastore queries - Google Cloud Platform Console Help

Anyhow I don't need this particular functionality anymore but I should mention that the word "Kind" in Google Datastore/Firestore represents a table in SQL, not kind as in the type of data stored, something for the engineers to be aware of because I was also confused by this when I began working with Datastore.

Anyways thank you for your help! Have a great day.