This guide will explain you how to use Toql to query and modify data from a database.

Toql is free and open source software, distributed under a dual license of MIT and Apache. The code is available on Github. Check out the API for technical details.

This book is split into several sections, with this introduction being the first. The others are:

• Concept - The overall concept of Toql.
• Query Language - How queries look like.
• Toql Derive - Let the derive do all the work!

Toql Transfer Object Query Language is a library that turns a query string into SQL to retrieve data records. It is useful for web clients to get database records from a REST interface.

Toql

• can query, insert, update and delete single and multiple database records.
• handles dependencies in queries through SQL joins and merges. Cool!
• is fast, beause the mapper is only created once and than reused.
• has high level functions for speed and low level functions for edge cases.

I developped Toql about 10 years ago for a web project. I have refined it since then and it can be seen in action on my other website www.schoolsheet.com. I started the Toql project to learn Rust.

Toql is a set of crates that aim to simplify web development:

1. A web client sends a Toql query to the REST Server.
2. The server uses Toql to parse the query and create SQL.
3. SQL is send to the Database.
4. The database results are sent to the client.

While all the low level functions are available for the programmer, the Toql derive produces also high level functions, so that the whole can be done with a single function call.

Here is part of the code that uses Rocket to serve users from a database.

    #[derive(Toql)]
    #[toql(skip_indelup)] // No insert / delete / update functionality
    struct Country {
        id: String,
        name: Option<String>
    }

    #[derive(Toql)]
    #[toql(skip_indelup)]
    struct User {
        id: u32,
        name: Option<String>,
        #[toql(sql_join(self="country_id", other="id"))]
        country: Option<Country>
    }
    
    #[query("/?<toql..>")]
    fn query(toql: Form<ToqlQuery>,  conn: ExampleDbConnection, 
        mappers: State<SqlMapperCache>) -> Result<Counted<Json<User>>> {
        let ExampleDbConnection(mut c) = conn;

        let r = toql::rocket::load_many(toql, mappers, &mut c)?;
        Ok(Counted(Json(r.0), r.1))
    }

    #[database("example_db")]
    pub struct ExampleDbConnection(mysql::Conn);

    fn main() {
        let mut mappers = SqlMapperCache::new();
        SqlMapper::insert_new_mapper::<User>(&mut mappers);

        rocket::ignite().mount("/query", routes![query]).launch();
    }

If you have a MySQL Server running, try the full CRUD example.

ROCKET_DATABASES={example_db={url=mysql://USER:PASSWORD@localhost:3306/example_db}} cargo +nightly run --example crud_rocket_mysql

The toql query language is a string that defines which fields should be selected from a database table.

Fields can be filtered and ordered, they are separated by comma or semicolon to express AND or OR concatenation.

Fields preceded by a path refer to a depended table.

id, +name, age gt 18

is translated into

SELECT id, name, age WHERE age > 18 ORDER BY name ASC

id , .age eq 12; .age eq 15

is translated into

SELECT id WHERE age = 12 OR age = 15

Fields are selected if they are mentioned in the query.

• Names without underscore represent typically columns or expressions from the table the query is run against. id, name, fullName, emailAddress

• Fields with underscores are called fields with a path. They are mapped to a joined or a merged dependency. For a join relationship, the join will be added to the SQL statement if the field is selected. For a merge relationship a second SQL query must be run to query and merge the dependency. book_id, book_title, book_createdBy_id

• To use a field only for filtering, but not for selection hide it with a dot. .age, .book_id

id, book_id, .age eq 50

is translated into (SQL Mapper must be told how to join)

SELECT a.id, b.id, null FROM User a JOIN Book b ON (a.book_id = b.id) WHERE a.age > 50

There are two wildcards to select multiple fields. They can neither be filtered nor ordered.

• ** selects all mapped fields (top level and dependencies). Useful for development.

• * selects all fields from the top level.

• path_* selects all fields from path.

Fields can be excluded from the wildcard by setting them to ignore wildcard.

id, age, book_id, .age eq 50

can be expressed as

*, book_*, .age eq 50

can be expressed as

**, .age eq 50

and is translation into

SELECT id, book_id, age FROM FROM User a JOIN Book b ON (a.book_id = b.id) WHERE a.age > 50

Note that the age field is selected with **.

Fields can require roles from the query. This is the permission system from Toql. An error is raised, if a query selects a field that it's not allowed to. However if the query selects with a wildcard, the field will just be ignored.

Fields can be ordered in ascending + or descending - way.

+id, -title

is translated into

--snip-- ORDER BY id ASC, title DESC

Use numbers to express ordering priority.

• Lower numbers have higher priority.
• If two fields have the same number the first field in the query has more importance.

-2id, -1title, -2age

is translated into

--snip-- ORDER BY title DESC, id DESC, age DESC

Fields can be filtered by adding a filter to the field name.

• Filters are case insensitiv.
• Arguments are separated by whitespace.
• Strings and enum arguments are enclosed with single quotes.
• Boolean arguments are expressed with numbers 0 and 1.

Custom functions are applied through the FN filter. They must be handled by a Field Handler. See API for details.

The recommended way to use Toql in your project is to use the Toql derive.

The derive builds a lot of code. This includes

• Mapping of struct fields to Toql fields and database.
• Creating query builder functions.
• Handling relationships through joins and merges.
• Creating high level functions to load, insert, update and delete structs.

With this simple code

# #![allow(unused_variables)]
#fn main() {
   #[derive(Toql)]
   struct User {
       id: u32,
       name: String,
}
#}

we can now do the following

# #![allow(unused_variables)]
#fn main() {
use toql::mysql::load_one; // Load function from derive
use toql::mysql::update_one; // Update function from derive

let conn = --snip--
let cache = SqlMapperCache::new();
SqlMapper::insert_new_mapper::<User>(&mut cache); // Mapper function from derive

let q = Query::wildcard().and(User::fields.id().eq(5)); // Builder fields from derive
let user = load_one<User>(&q, &cache, &mut conn); 

user.age = Some(16);
update_one(&user); 
#}

Struct fields are mapped to Toql and database by default in a predictable way:

1. Table names are UpperCamelCase.
2. Column names are snake_case.
3. Toql fields are lowerCamelCase, dependend structs are separated with an underscore.

To adjust the default naming to an existing database scheme use the toql attributes tables and columns on the struct. Possible values are

• CamelCase
• snake_case
• SHOUTY_SNAKE_CASE
• mixedCase

# #![allow(unused_variables)]
#fn main() {
#[derive(Toql)]
#[toql(tables="SHOUTY_SNAKE_CASE", columns="UpperCase")]
  struct UserRef {
        user_id: u32
        full_name: String,
}
#}

is translated into

SELECT UserId, FullName FROM USER_REF;

Or use table an the struct and column on the fields.

# #![allow(unused_variables)]
#fn main() {
#[derive(Toql)]
#[toql(table="User")]
  struct UserRef {
    #[toql(column="id")]
        user_id: u32
        full_name: String,
}
#}

is translated into

SELECT id, full_name FROM User

Toql fields on a struct are always mixed case, while dependencies are separated with an unserscore.

# #![allow(unused_variables)]
#fn main() {
#[derive(Toql)]
#[toql(table="User")]
  struct UserRef {
    #[toql(column="id")]
        id: u32
        full_name: String,
        #[toql(self="counry_id", other="id")]
        county: Country
}
#}

is referred to as

id, fullName, country_id

To exclude fields from the query annotate it with skip.

Each field in a Toql query can individually be selected. However fields must be Option<> for this, otherwise they will always be selected in the SQL statement regardless of the query.

# #![allow(unused_variables)]
#fn main() {
    struct User {
        id: u32,				// Always selected in SQL
        name: Option<String>			// Optional field
        middlename: Option<Option<String>>	// Optional field of nullable column
        #[toql(select_always)]
        middlename: Option<String>  		// Nullable column, always selected in SQL
  }
#}

A struct can refer to another struct. This is done with a SQL join.

Joins are automatically added to the SQL statement in these situations:

• Fields in the Toql query refer to another struct. Example user_phoneId
• Fields on a joined struct are always selected. #[toql(select_always)
• Fields on a joined struct are not Option<>. Example id: u64

The Toql query id translates this

# #![allow(unused_variables)]
#fn main() {
struct User {
     id: u32,	
     name: Option<String>
     #[toql(sql_join(self="mobile_id" other="id"))]  
     mobile_phone : Option<Phone>

     #[toql(sql_join(self="country_id" other="id"))]  
     country : Country
}

struct Country {
    id: String // Always selected
}

struct Phone {
    id : Option<u64>, 
}
#}

into

SELECT user.id, null, null, country.id FROM User user 
INNER JOIN Country country ON (user.country_id = country.id)

While the same structs with the Toql query id, mobilePhone_id translates into

SELECT user.id, null, mobile_phone.id, country.id FROM User user 
LEFT JOIN Phone mobile_phone ON (user.mobile_id = mobile_phone.id)
INNER JOIN Country country ON (user.country_id = country.id)

The default table names can be changed with table, the alias with alias.

The Toql query id for this struct

# #![allow(unused_variables)]
#fn main() {
#[toql table="Users", alias="u"]
struct User {
     id: u32,	
     name: Option<String>
     #[toql(sql_join(self="mobil_id", other="id"), table="Phones", alias="p")]  
     mobile_phone : Option<Phone>
}
#}

now translates into

SELECT u.id, null, p.id FROM Users u LEFT JOIN Phones p ON (u.mobile_id = p.id)

SQL joins can be defined with

• self, the column on the referencing table. If omitted the field name is taken.
• other, the column of the joined tabled.
• on, an additional SQL predicate. Must include the table alias.

For composite keys use multiple sql_join attributes.

# #![allow(unused_variables)]
#fn main() {
    #[toql(sql_join(self="country_id", other="id"), sql_join(self="language_id", other="language_id", on="country.language_id = 'en'") ]  
    country : Option<Country>
#}

Joining on an Option field will issue a LEFT JOIN rather than an INNER JOIN.

If the selected columns cannot be converted into a struct

• then this will result in a field value of None for an Option<> type
• or will raise an error for non Option<> types

A struct can also contain a collection of other structs. Since this cannot be done directly in SQL Toql will execute multiple queries and merge the results afterwards.

# #![allow(unused_variables)]
#fn main() {
struct User {
     id: u32,
     name: Option<String>
     #[toql(merge(self="id", other="user_id"))]  // Struct fields for Rust comparison
     mobile_phones : Vec<Phone>
}

struct Phone {
    number: Option<String>
    user_id : Option<u32>
}
#}

Selecting all fields from above with ** will run 2 SELECT statements and merge the resulting Vec<Phone> into Vec<User> by the common value of user.id and phone.user_id.

To merge on composite fields use the attribute multiple times #[toql(merge(..), merge(..)).

Structs for toql queries include typically a lot of Option<> fields. The Toql derive can build proper insert, update and delete functions.

To make this work you need to provide additional information about keys.

struct User {
  #[toql(delup_key, skip_inup)] // Key for delete / update, never insert / update
    id: u64

    name: Option<String>
}

For composite keys mark multiple columns with the delup_key.

Join, merge and SQL fields are excluded. To skip other fields from insert or update functions use the skip_inup annotation. Useful for auto incremented primary keys or trigger generated values.

# #![allow(unused_variables)]
#fn main() {
#[derive(Toql)]
struct User {
    #[toql(delup_key, skip_inup)]
     id: u32,
     name: Option<String>
}

--snip--
use toql::mysql::insert_one;
use toql::mysql::udate_one;
use toql::mysql::delete_one;

let mut conn = --snip--

let u = User{id:0, name: Some("Susane")};
let x = insert_one(&u, &mut conn); // returns key
u.id = x;
u.name= Some("Peter");
update_one(&u, &mut conn);

delete_one(&u, &mut conn);
#}

The update function will update fields only if they contains some value. Look at this struct:

# #![allow(unused_variables)]
#fn main() {
struct User {
    id: u64
    username: String,			// Always updated
    realname: Option<String>, 		// Updated conditionally
    address: Option<Option<<String>>, 	// Optional nullable column, updated conditionally
    #[toql(select_always)]
    info: Option<String> 		// Nullable column, always updated


}
#}

Collections or dependend structs are not affected by insert, delete or update. You must do this manually (for safety reasons).

However functions for collections are provided.

# #![allow(unused_variables)]
#fn main() {
#[derive(Toql)]

struct Phone {
    #[toql(delup_key, skip_inup)]
    id: u64
}

struct User {
    #[toql(delup_key, skip_inup)]
     id: u32,
     phones: vec<Phone>
}

--snip--
use toql::mysql::insert_one;
use toql::mysql::insert_many;

use toql::mysql::delete_one;
use toql::mysql::delete_many;

// TODO



#}

The derive provides struct level attributes and field level attributes.