Prepared statements versus dynamic queries

[frankvollebregt]

In my app, the same queries run pretty often. As a simple example, let's say I have a table cars which has 20 columns, and it needs to be queried fairly often (say, every time the user opens a specific screen).

Now as it turns out, not all users need all columns. Maybe a user salesman only needs the licenseplate, price and color, whereas user mechanic needs licenseplate, last_serviced and noise_level.

In my current application, I have queries defined in .drift files, which just get all the columns. In Dart, I then use only the values relevant for the current user. E.g.

SELECT licenseplate, price, color, last_serviced, noise_level FROM cars;

Moreover, I do this multiple times: aside from cars, there is also a table airplanes and a table motorcycles, all with certain columns relevant for certain users. What I do now is I query each of the tables once (in reality there are many more columns, and these queries already have some joins etc. present), getting all columns, and filtering for the specific user once in Dart code.

Now for my question: would it be sensible to build the SQL query dynamically (at least per user session) in Dart code instead and run the query to retrieve exactly the values relevant for each user? This way, it would be possible to get a query like:

SELECT
  name,
  LIST(SELECT licenseplate, price, color FROM cars) AS cars,
  LIST(SELECT registration, price, num_engines FROM airplanes) AS airplanes,
  LIST(SELECT licenseplate, price, allows_passenger FROM motorcycles) AS motorcycles
FROM users
WHERE users.id = :id;

Which is nice because it only retrieves the values that are actually required for the current user. Of course this is a bit error prone and might unexpectedly break when I update my data model.

I am wondering, though, if there are other (dis)advantages to this approach. For the same user (at least during a given session) the same query will be called each time (though possibly with different parameters), so instinctively I'd expect it to be possble for them to be cached or remembered as prepared statements (which I think Drift is using for my 'normal' queries defined in .drift files), but is this possible?

[simolus3]

Statements are not currently cached by drift unless you enable the cachePreparedStatements parameter on NativeDatabase. That option will affect all statements though, regardless of whether they are coming from a drift file or not.

I'm not sure I fully understand the question though - is the idea to define the basic shape of a query in .drift files and then perhaps refine them in Dart to only select certain columns? I think we can technically do that, but without much type safety for the result rows.
Or do you want to do everything in Dart (LIST is a drift macro and not supported for Dart statements, so I don't really understand the second snippet then)

so instinctively I'd expect it to be possble for them to be cached or remembered as prepared statements

Yeah they would benefit from caches if the option is enabled. I'm not sure if the overhead of resolving additional columns is that bad with sqlite3 though? If a network roundtrip is involved it's obvious that shaving off unnecessary columns is beneficial, but here you'll probably only save a few allocations. That can make a difference, but it's unlikely that it matters too much.

[frankvollebregt]

Thank you for the quick reply. When I said in Dart, what I actually meant was that I wanted to build the query as a String and run it directly (so, with 0 safety). But you're correct in that I won't be able to use LIST of course... I somehow didn't think of that yet :p

Currently, in some cases, around 10-15 queries run and collect a bunch of data each time the user enters a certain screen, returning up to around 30 columns each, while I only use a handful of them. Instinctively I feel like it'd be better to have 1 big query (albeit with a few more joins) that retrieves all values, but only those that are required for this user.

While explaining it though, it's starting to sound increasingly more convoluted and impractical...

[frankvollebregt]

The remark sparks the idea in my mind of starting of with a .drift file query, and somehow specifying which columns (tables?, CTEs?) to actually query for this user, which could be interesting but for the pretty niche use case I suspect it's not likely to be worthwhile to integrate it into Drift directly.

[simolus3]

If you're not using much drift-specific syntax in the query, you could also define the common queries as views and then select the required columns from that view in Dart.

[frankvollebregt]

Oh that's a pretty clever idea! Thank you for the suggestions