include-sql

A Yesql inspired macro for using SQL in Rust

View on GitHub

Usage

When you write your own impl_sql, you would need to add include-sql as a dependency:

[dependencies]
include-sql = "0.3"

It might also be prudent to re-export include-sql macro to make the use of your crate more ergonomic:

pub use include_sql::include_sql;

The users of your implementation would then be able to generate API to access database queries as:

use your_crate::{include_sql, impl_sql};

include_sql!("src/queries.sql");

Anatomy of the Included SQL File

Let’s assume that we created the following file and saved it as library.sql:

-- name: get_loaned_books?
--
-- Returns the list of books loaned to a patron
--
-- # Parameters
--
-- param: user_id: &str - user ID
--
SELECT book_title
  FROM library
 WHERE loaned_to = :user_id
 ORDER BY 1
/

-- name: loan_books!
--
-- Updates the book records to reflect loan to a patron
--
-- # Parameters
--
-- param: book_titles: &str - book titles
-- param: user_id: &str - user ID
--
UPDATE library
   SET loaned_to = :user_id
     , loaned_on = current_timestamp
 WHERE book_title IN (:book_titles)
/

An SQL file can include one or more SQL statements. Each statement must have a preceding doc-comment. The latter has a dual purpose - it provides the doc-comment text for the generated method, and it also embeds meta data about the statement that follows it:

Note that include-sql will use the name as-is. If you want to avoid Rust complaining about it, use the appropriate (snake) case for it.

For example, include-postgres-sql and include-sqlite-sql recognize ?, !, and -> tags. For ? they generate methods that process selected rows, for ! - methods that execute all other - non-select - statements, and for -> - methods that read data from RETURNING statements.

include-sql uses param: to gather parameter types and to generate the Rust doc-comment for the parameter. For example, this line in SQL: param: user_id: &str - user ID tells include-sql that the type of user_id is &str. It is then converted into * `user_id` - user ID text line and included into the Rust doc-comment for the generated method.

Note that include-sql expects parameter_type to be a Rust type and will fail if it cannot be parsed as such.

Note that because param: is optional for parameters that are not explicitly described by param: include-sql will create a synthetic parameter description using an inferred type _ for scalar SQL parameters or synthetic generic type [GenericType] for IN list parameters. impl_sql must be prepared to handle cases when a parameter type should be inferred and generate method parameter that is typed generically. See include-postgres-sql or include-sqlite-sql for an example of how it can be done.

Statements should be terminated with a slash - /. However, in some cases it could be optional as the following name: meta comment would also auto-terminate the preceding statement.

Generated impl_sql Call

For the SQL above include-sql would generate:

impl_sql!{ LibrarySql =
  {
    ? get_loaned_books (: user_id (&str))
    "\n Returns the list of books loaned to a patron\n\n # Parameters\n\n * `user_id` - user ID\n"
    $ "SELECT book_title\n  FROM library\n WHERE loaned_to = " : user_id "\n ORDER BY 1"
  },
  {
    ! loan_books (# book_titles (u32) : user_id (&str))
    "\n Updates the book records to reflect loan to a patron\n\n # Parameters\n\n * `book_ids` - book IDs\n * `user_id` - user ID\n"
    $ "UPDATE library\n   SET loaned_to = " : user_id "\n,     loaned_on = current_timestamp\n WHERE book_title IN (" # book_titles ")"
  }
}

Where:

Note that types are passed as parenthesized types. This is done to allow impl_sql match them as token trees. If a parameter type is not defined in SQL, _ will be used in its place (this _ drives the need to match parameter types as token trees) for which impl_sql is expected to generate an appropriate generic type.

Async

When include-sql is built with the async feature, impl_sql macro will be generated with additional lifetimes for reference parameters. For example, the above LibrarySql example will look like this:

impl_sql!{ LibrarySql =
  {
    ? get_loaned_books (: user_id ('user_id &str))
    "\n Returns the list of books loaned to a patron\n\n # Parameters\n\n * `user_id` - user ID\n"
    $ "SELECT book_title FROM library WHERE loaned_to = " :user_id "ORDER BY 1"
  },
  {
    ! loan_books (: user_id ('user_id &str) # book_ids ('book_ids usize))
    "\n Updates the book records to reflect loan to a patron\n\n # Parameters\n\n * `book_ids` - book IDs\n * `user_id` - user ID\n"
    $ "UPDATE library SET loaned_to = " : user_id ", loaned_on = current_timestamp WHERE book_title IN ( " # book_titles " )"
  }
}

Note that for IN list parameters where the list item is a reference itself additional lifetime that covers list items is also generated. For example, for this query:

-- name: get_users_who_loaned_books?
--
-- Returns names patrons to whom the specified books are loaned
--
-- # Parameters
--
-- param: book_titles: &str - book titles
SELECT DISTINCT first_name, last_name
  FROM patrons
  JOIN library ON library.loaned_to = patrons.user_id
 WHERE book_title IN (:book_titles)

include-sql will generate:

impl_sql!{ LibrarySql =
  {
    ? get_users_who_loaned_books (# book_titles ('book_titles 'book_titles_item &str))
    " Returns names patrons to whom the specified books are loaned\n # Parameters\n * `book_titles` - book titles"
    $ "SELECT DISTINCT first_name, last_name FROM patrons JOIN library ON library.loaned_to = patrons.user_id WHERE book_title IN (" # book_titles ")"
  }
}

Implementation Examples

As a picture is worth a thousand words before you start implementing your own impl_sql macro it would be advisable to review existing implementations like include-postgres-sql and include-sqlite-sql, and maybe even use one of them as a starting point.