Rust structs <-> SQL Tables

  1. I am hoping for an answer more instructive than "use an ORM" :

  2. Is there any good resource / tutorial / book on how to design Rust structs with "eventually I need to store this in a SQL table" in mind?

  3. If all we have are "simple structs" where each field is a u16, u32, u64, or String, everything is relatively simple.

  4. However, once we have things like: Rc, HashSet, HashMap, Vec as field members, or start using Enum's in addition to Structs, things get quite messy quite fast.

  5. It seems most of the time, when we design structs, it is with the belief that it is "backed" by "flat memory"; whereas I am curious about rules of thumb for designing structs are are "backed" by "SQL tables".

This is extremely difficult to do rigorously because the model is so different. A SQL query can return any arbitrary collection of columns, and they can come from several backing tables. In fact, the backing table can be different for various rows in the same query.

Rust’s type system is just barely able to manipulate data in the same way SQL does, but it’s fiendishly complicated to pull off— It’s the primary topic of my Master’s thesis, and I only expect to have a solid proof-of-concept by the end of it.

In the absence of a general solution, the best you can do is have structs designed to represent the output of particular SQL queries: one field per column, and maybe some associated functions to add some interesting filters into the query.

I’d probably look at writing variants of From and To traits that generalize loading your structs from and saving them to a set of named columns, which then maps reasonably to how databases think about things. Any query that produces results with the requisite columns can then be used to priduce an iterator of your custom objects.

At this point you’re duplicating part of your database design in Rust, and it’s going to be a lot of trouble keeping the two in sync as your requirements change going forward. It’s better to pick one place for your data model to be defined, and use the other as a dumb cache; either have relationships and queries performed by the DB engine with temporary view objects in Rust, or keep all the logic in Rust and treat the database like a fancy file format.

1 Like

Is this based on or something else ? It seems like one could in theory do a trait for eaach column name that would ever appear in the database, but that would mean no dynamically named columns.

Thanks for that link; I hadn’t run across that before, but it looks quite similar; I guess I’ve got some reading to do. I’ve been mostly working from relational algebra, the theoretical framework that SQL is derived from.

This gets you a long way, but it’s not quite enough to support some standard operations. join is probably the easiest to demonstrate the problems— When you join two records A and B, for each column trait Col:

  • If A: Col and B: Col, assert that the values are equal
  • If A: Col and B:!Col, delegate to A’s implementation
  • If A:!Col and B: Col, delegate to B’s implementation
  • If A:!Col and B:!Col, do not implement Col

Because Rust doesn’t support checking for !Col, this requires some additional information stored somewhere else in the type system. This kind of subtle problem shows up all over this domain, and each one needs a few tricks to get around.

In practice, for most applications, the best way around the issues is to manually (or via macro) specify the record types you’ll be using and the outcome of joining them together. Another option is to work with Option fields and panic at runtime if the programmer made a mistake.

My thesis work is figuring out how the type system can statically check these operations for correctness, so that incorrect code produces a compile error instead.


This topic was automatically closed 90 days after the last reply. We invite you to open a new topic if you have further questions or comments.