Quick-Oxibooks-SQL: SQL Integration for QuickBooks Online API in Rust

quick-oxibooks-sql is a Rust library that extends the functionality of the quick-oxibooks crate by providing SQL-like capabilities for interacting with QuickBooks Online (QBO) data. It allows developers to perform SQL-style queries and operations on QuickBooks entities, making it easier to manage and manipulate accounting data programmatically.

Features

• SQL-Like Querying: Perform SQL-style queries on QuickBooks entities.
• Type-Safe Operations: Leverages Rust's type system for safe data handling.
• Integration with quick-oxibooks: Seamlessly works with the quick-oxibooks crate for API interactions.

Usage

This crate provides a macro for building QuickBooks Online queries: qb_sql!.

Building a Query Object with qb_sql!

The qb_sql! macro parses a SQL-like query at compile time and generates a Query struct. This allows you to inspect the query components before generating the final string. You can use Rust variables directly within the query.

use quick_oxibooks_sql::qb_sql;
use quickbooks_types::Customer;

let min_balance = 1000.0;
let ids = vec!["1", "2", "3"];

// You can pass in an iterator or a slice for the `in` clause, or a tuple of literals.
// (notice that a singular value is only allowed for iterators, not for literals)
let query = qb_sql!(
    select display_name, balance from Customer
    where balance >= min_balance
    and id in (ids)
    order by display_name asc
    limit 10
);

// The above is equivalent to:
// let query = qb_sql!(
//     select display_name, balance from Customer
//     where balance >= min_balance
//     and id in (1, 2, 3)
//     order by display_name asc
//     limit 10
// );

// The `query` variable is now a `Query<Customer>` struct.
// You can generate the final query string to be sent to the QBO API.
let query_string = query.query_string();

assert_eq!(
    query_string,
    "select DisplayName, Balance from Customer where Balance >= '1000' and Id IN ('1', '2', '3') order by DisplayName ASC LIMIT 10"
);

Using a Query object with quick-oxibooks

You can use the generated Query object with a QBContext to execute the query against the QuickBooks Online API.

use quick_oxibooks::{Environment, QBContext};
use quick_oxibooks_sql::qb_sql;
use quickbooks_types::Customer;
use ureq::Agent;

let client = Agent::new_with_defaults();
let qb = QBContext::new(
    Environment::SANDBOX,
    "your_company_id".to_string(),
    "your_access_token".to_string(),
    &client,
)?;
let query = qb_sql!(
    select display_name, balance from Customer
    where balance >= 1000.0
    order by display_name asc
    limit 10
);
let results = query.execute(&qb, &client)?;

Manual Query Construction

If you prefer to construct queries manually without using the macro, you can disable the macros feature and create a Query object directly. However, using the qb_sql! macro is recommended for ease of use, as it handles parsing and validation for you. The functions related to the Query struct are available through unsafe blocks to indicate the potential for runtime errors if not used correctly.

use quick_oxibooks_sql::{Query, TypedWhereClause, Operator};
use quickbooks_types::Customer;

let query: Query<Customer> = unsafe {
  Query::new().condition(
      WhereClause {
        field: "DisplayName",
        operator: Operator::Like,
        values: vec!["John%".into()],
      }
  )
  .limit(10)
  .order_by("DisplayName", true)
};

Supported SQL Syntax

The macros support a subset of SQL syntax relevant to the QuickBooks Online API:

• SELECT: Select all fields (*) or a comma-separated list of specific fields (e.g., select display_name, balance).
• FROM: Specify the QuickBooks entity (e.g., from Customer). The entity must implement the QBItem trait from quickbooks-types.
• WHERE: Filter results using one or more conditions joined by and.
  • Operators: =, like, >, <, >=, <=, in.
  • The in operator accepts a tuple of literals or a type that implements IntoIterator for Display types (e.g., id in (1, 2, 3) or id in (my_ids)).
• ORDER BY: Sort results by one or more fields, with asc or desc direction (e.g., order by display_name asc, balance desc).
• LIMIT: Restrict the number of records returned.
• OFFSET: Start the result set at a specific offset, for pagination.

more information about the syntax can be found in the QuickBooks Online API documentation.