Rust crate `derive-sql` v0.11 update

Continuous improvement: reworking a library based on my own usage feedback

Image by Pete Linforth from Pixabay

At the end of last year, I posted about an update to my crate derive-sql to introduce traits. I have been using it on a few projects and I realised some of my approach shortcomings. This story documents these shortcomings and the changes made.

The crate is available on crates.io and the source code is hosted on github.

The views/opinions expressed in this story are my own. This story relates to my personal experience and choices. The story, code extract, and source code are provided in the hope that they will be useful without any warranty.

The crate derive-sql was developed to satisfy my need for interacting with SQL database. I developed it with the intent of using it and used crates.io as a way of making it available to myself. Hence the development process is very much iterative. I identified what is ‘wrong’ in the released version, work on it and release and update that I then work with some more. This process lead me to understand the implication of design choice only after new versions are released…

The end of 2023 update focused on introducing a trait to define ways to interact with a table or tables: create or delete a table, insert elements, etc. When working with it, the implementation had the following shortcomings:

• All table operations are under one trait — even if one only needs to read items for a table, the trait requires an implementation of updating items, etc (even if this implementation is only for returning an error);
• The implementation of the trait using derive macros DeriveSqlite and DeriveMysql consumes the SQL database connection;
• The SQL driver specialisation (ie rusqlite or mysql) was not handled in the right place.

These shortcoming led me to the rework presented here.

Handling SQL driver

The approach chosen in v0.11 is to introduce a Connection trait that defines a subset of functions commonly implemented by SQL drivers — such as execute and query. In addition, two traits are also defined:

• A Param trait for handling execute statement parameters;
• A Row trait for handling responses to query request.

The subset of functionalities of the Connection trait does not yet include transaction.

The derive-sql library functionalities are then implemented against these traits. The library includes implementation of these traits for the SQL drivers for SQLite (namely rusqlite::Connection) and MySQL (any struct implementing mysql::prelude::Queryable). With this approach, one can implement generic functions with signatures compatible with all supported SQL drivers:

Splitting SQL functionalities

Addressing the need of being able to implement different query type (create, delete table, select, insert) independently from each other led to the introduction of the following traits:

• Table to handle table related queries for creation, deletion;
• SelectV2 to handle queries of items;
• Delete to handle items deletion;
• Insert to handle items insertion;
• Update to handle updating items.

Each trait can either be implemented directly, or alternative, can be implemented through implementing an equivalent trait returning the SQL statement (ie implement the TableStatement trait that provide the SQL statement(s) for a Table trait).

In addition, the trait signature no longer use the Selectable trait that was employed to gather filtering and ordering statement. This version uses a more explicit approach whereby filtering and ordering statement parameters are provided in the trait methods signature — where relevant. I realised when using the library that I was confused with the Selectable trait — so I can only guess that other would feel similar.

Derive macro

The code below shows how one can implement the interaction with an SQL database to store and query a struct Person containing 2 fields id and name :

The above is very repetitive and is automated using a derive macro as shown below. The derive macro is independent from the SQL driver used — provided the SQL statement is compatible with the driver.

Conclusions

Whilst I feel that the library is improving and getting easier to use, I do notice quirks and issues that needs to be addressed — lack of transaction and difference in SQL statement for different SQL drivers.

I will aim to develop example of use of the library to allow people to play with it more readily. If you are using it, I would love to hear from you.