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 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 resulting datasets are put into structs.
5. The structs are sent to the client.

The Toql derive produces high level functions, so that common operations can be done with a single function call. For edge cases all the low level functions are available for the programmer, too.

Here is an excerpt of 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 Toql fields to struct fields and database columns or expressions.
• Creating field methods for the query builder.
• 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 {
       #[toql(delup_key)]
       id: u32,
       name: Option<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.
4. Toql paths are lowerCamelCase, separated with an underscore.

To adjust the default naming to an existing database scheme use the attributes tables and columns for a renaming scheme or table and column for explicit name.

Supported renaming schemes 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

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 mapping attribute them 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 through a path: user_phoneId.
• Fields on a joined struct are always selected: #[toql(select_always).
• Fields on a joined struct are not Option<>: 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 Toql query id, mobilePhone_id for the same structs 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 struct field's 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.

Selected columns from a join cannot always be converted into a struct. A LEFT JOIN is likely to produce null values. In case the database results cannot be put into a joined struct, then:

• Option<> fields value will be None.
• Non Option<> fields will raise an error.

A struct can also contain a collection of other structs. Because 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.

Because merging is done by Rust, the merge fields must refer to the struct fields.

#[toql(merge(self="rust_field_name_in_this_struct", other="rust_field_name_in_other_struct"))]

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. Here a list of all available attributes: