Custom storage query in Unity

I’m using the Storage Engine to save objects for my Unity app. Instead of returning a list of all of the paginated objects saved for a user, I would like to make a query to limit the response and pull only a few specific items. In this case, I would only like to return objects created within a certain date range. Using the List Objects API, it seems like there’s a way to customize the cursor based on this example in GO in order to make a custom query. I’m trying to create a similar cursor in C# like so:

string query = "select * from storage where create_time between '2020-06-01' and '2020-06-30';";
var result = await client.ListUsersStorageObjectsAsync(session4, "events", session4.UserId, limit,query);

However, it replies with a “Malformed cursor was used” error. I’ll admit, I’m not familiar with how cursors work. I’m pretty familiar with PostgreSQL so if anybody has the right way to achieve this, or an example of any other query they have gotten working, I’m happy to work with that. Thanks!

I think there’s some confusion here. Cursors are a specific structure the server has defined, and should be considered opaque from the client’s perspective - if the server returns a cursor you can pass it back in follow-up requests to get the next page of results. The client does not know what the cursor contains and cannot construct its own.

Additionally cursors are not SQL queries. What you’ve pointed out is where the final query is constructed but this is not done using any client input directly, if you check where that code is called from it’s after many layers of decoding and validation. Allowing clients to specify raw SQL queries directly is a massive security risk and Nakama does not allow this in any of its built-in features.

To sum it up what you’re describing is impossible to do with the client.ListUsersStorageObjectsAsync function.

What you’ll need to do is create your own RPC function that already contains the correct SQL query (do not allow clients to submit their own) and clients can invoke to return data they need. The query you’ve suggested could be part of the RPC but note that it does not perform a cursor-based operation.

The idea of cursor-based queries is to return a small set of results plus a “cursor” if there are further results available on request. Callers can then submit a new request with the cursor, and get the next set of results and a new cursor. This is a very efficient pagination mechanism that does not suffer from the typical offset+limit query serious performance problems.

As a small pseudo-example, in your case you might "select * from ... limit 10", and encode in your cursor the ID of the 10th item. The next request would then become "select * from ... where id > 'id of 10th item' limit 10" and so on. Your aim should be to give the database engine a way to quickly find the place to resume scanning from, assuming you’ve also given it an appropriate index to rely on. You need both an appropriate query and an appropriate index to make this work as intended.

This topic is a bit beyond standard Nakama usage but I hope this is helpful.

Thanks for the clarification @zyro. I was able to get the paginated approach working, and that seems to be good enough for now, so I might try to avoid getting into that RPC option until I reach some sort or scalability issue that I’m imagining (that will probably never even become an issue).