Enhancing Rhai with SQL Execution via Rusqlite: Help Needed for Embedding SQL DML and Expression Handling

[shah]

We're looking to integrate SQL functionality into Rhai with minimal changes to the existing language, aiming to embed SQL expressions and DML statements in a more natural way, without requiring function prefixes or additional syntax changes.

Our Desired Enhancements

1. SQL Expression Returning a Matrix of Rows:
   We want to implement an expression similar to JavaScript's string literal, where the following will return a matrix of rows (or zero rows if no results are found):

   // Desired behavior: SQL string literal that returns a matrix of rows
   let x = SQL`...any valid Rusqlite SQL text`;
   

2. SQL Expression Returning a Single Value:
   Similar to the above, but returning a single value or undefined/null if not found:

   // Desired behavior: SQL string literal that returns a single value or null if not found
   let x = SQLv`...any valid Rusqlite SQL text`;
   

3. SQL DML Statements:
   We want to write SQL DML statements like INSERT, UPDATE, and DELETE directly in the Rhai scripts, terminated with a semicolon, and have them execute using Rusqlite. For example:

   // Direct SQL execution within Rhai by finding the pre-registered `INSERT INTO`, `UPDATE`, etc. and passing everything up to ; to Rusqlite
   INSERT INTO my_table VALUES (1, 'data');
   UPDATE my_table SET column1 = 'new_data' WHERE column1 = 'data';
   DELETE FROM my_table WHERE column1 = 'new_data';
   

   Each of these statements would be passed as full SQL text to Rusqlite for execution. We are not trying to make Rhai into a SQL-like language, just wanted to find an easy way to use embedded SQL to pass to Rusqlite when SQL is encountered as part of Rhai.

We understand we can define custom functions like sql_execute and call those in Rhai but we're looking to make it easier on our users who already know SQL.

Current Roadblocks

• We know Rhai supports custom DSL / functions and syntax, but we're struggling with how to make these SQL statements look like native Rhai statements and handle them seamlessly.
• For the SQL and SQLv string literal style expressions, we’d like to understand the best way to implement a custom handler for JavaScript-like backtick syntax, where the SQL text is processed and passed directly to Rusqlite when SQL or SQLv is encountered.

Additional Request: Variable Interpolation

Additionally, we want to know how we can pass Rhai variables into the SQL expressions and DML statements using JavaScript-style ${x} interpolation for parameters. For example:

let id = 1;
let name = "data";

// Desired behavior: Insert with Rhai variable interpolation in the SQL statement
INSERT INTO my_table VALUES (${id}, '${name}');

Is There a Recommended Approach?

1. How can we treat these SQL DML statements (INSERT, UPDATE, DELETE) as native statements that Rhai will process and then execute via Rusqlite?
2. How can we implement SQL and SQLv to handle backtick-delimited SQL queries in Rhai, while maintaining the simplicity and natural flow of the language?
3. How can we implement ${x} style variable interpolation for SQL statements, passing Rhai variables into SQL queries and DML statements?

Any guidance on the best way to implement this within Rhai's framework would be greatly appreciated!

[schungx]

From your requirements, it appears that the best way is to do it via a custom syntax. Custom syntax in Rhai extends Rhai's parser to user code.

Since SQL is basically a stream of whitespace-delimited tokens, and the tokens are not dissimilar to Rhai's, you can simply reassemble the SQL statement from the Rhai tokens received. The only problem I can think of are strings... since SQL needs ' and Rhai uses ".

You can simply make the keywords SELECT, INSERT, DELETE (in all-caps) custom syntax keywords and you can just parse your way as you like. Your ${...} interpolation will "Just Works" because you can handle them directly inside the custom parser.

With a custom syntax, you can simply do:

let id = 1;
let name = "data";

try {
    // Interpolations can hold any arbitrary block of Rhai code!
    let n = INSERT INTO my_table (id, name) VALUES (${id}, ${prettify_string(name)});
    if n < 1 { throw "No rows added!"; }
    print(`${n} row(s) added.`);
} catch (ex) {
    ....
}

let name = SELECT name FROM my_table WHERE id = ${id};

if name != () {
    print("FOUND!");
} else {
    print("Somehow the record is still not there...");
}

The strings issue we need a solution though...

[schungx]

Have you read the advanced part of custom syntax? It is complex because parsing is complex. But the idea is simple: you write a function. Rhai gives you one token at a time. You tell Rhai, based on that and the tokens received so far, what the next token should be expected.

This method is quite powerful. For example, you don't even need literal to always return strings. Since you control the evaluation of the expressions, it can be any value type, and your function turns it into a SQL literal based on the value type.

Or you can prepare parametrized SQL with variables in place of interpolation sites. Then your interpolated values can simply be passed in as parameters, no need to even render it as string.

In other words:

let name = SELECT name FROM my_table WHERE id = ${id} and another_id = ${alt};

Can have a prepared SQL:

SELECT name FROM my_table WHERE id = ? and another_id = ?

Then you evaluate the two expressions, convert to SQL types, and pass in as parameters.

[shah]

Yes @schungx I tried to follow the custom syntax but had a little trouble. Your overall explanation makes sense, and we'll give it a shot on our own and see how far we get.

If, however, you felt that it might be useful to others perhaps you could lay out an example here and we can collaborate on the shared code, test it for you, and then submit it to the Rhai team as either documentation or a working example? This "little language" for Rusqlite might be quite powerful and worth sharing with the community as another "Practical Example" section in on this Custom Syntax Docs page. :-)

[schungx]

Yes @schungx I tried to follow the custom syntax but had a little trouble. Your overall explanation makes sense, and we'll give it a shot on our own and see how far we get.

Actually this is what you need: https://rhai.rs/book/engine/custom-syntax-parsers.html

If, however, you felt that it might be useful to others perhaps you could lay out an example here and we can collaborate on the shared code, test it for you, and then submit it to the Rhai team as either documentation or a working example? This "little language" for Rusqlite might be quite powerful and worth sharing with the community as another "Practical Example" section in on this Custom Syntax Docs page. :-)

I certainly won't mind collaboration. Let me try to whip up something.

[shah]

Excellent, thanks @schungx! I've started some pseudo-code here (below), perhaps you can make corrections directly or guide in that thread: #911 (comment).

[shah]

Hi @schungx thanks for offer to whip something up, we're looking forward to seeing your magic. :-)

[shah]

Here's some quick pseudo-code, is this directionally correct? I know it doesn't handle the string interpolation to bind parameters translation since I couldn't figure that out.

I wasn't sure if I should be using $string$ or $block$.

use rhai::{Engine, Dynamic, EvalAltResult, Scope};
use rusqlite::{params, Connection};

// Execute SELECT SQL queries
fn execute_sql_select(start_token: &str, sql: &str, conn: &Connection) -> Result<Dynamic, Box<EvalAltResult>> {
    let sql_query = format!("{} {}", start_token, sql);
    let mut stmt = conn.prepare(&sql_query).map_err(|e| e.to_string())?;
    let mut rows = stmt.query(params![]).map_err(|e| e.to_string())?;
    let mut result = Vec::new();

    while let Some(row) = rows.next().map_err(|e| e.to_string())? {
        let value: String = row.get(0).map_err(|e| e.to_string())?;
        result.push(value);
    }

    Ok(result.into())
}

// Execute INSERT, UPDATE, DELETE SQL queries
fn execute_sql_dml(start_token: &str, sql: &str, conn: &Connection) -> Result<Dynamic, Box<EvalAltResult>> {
    let sql_query = format!("{} {}", start_token, sql);
    let mut stmt = conn.prepare(&sql_query).map_err(|e| e.to_string())?;
    let affected_rows = stmt.execute(params![]).map_err(|e| e.to_string())?;
    Ok(affected_rows.into())
}

// Register custom SQL syntax
fn register_sql_syntax(engine: &mut Engine, conn: &Connection) {
    engine.register_custom_syntax(
        [ "SELECT", "$string$", ";" ], true,
        move |_, inputs| {
            let sql = inputs[0].get_string_value().unwrap();
            execute_sql_select("SELECT", &sql, conn)
        }
    );

    engine.register_custom_syntax(
        [ "INSERT INTO", "$string$", ";" ], true,
        move |_, inputs| {
            let sql = inputs[0].get_string_value().unwrap();
            execute_sql_dml("INSERT INTO", &sql, conn)
        }
    );

    engine.register_custom_syntax(
        [ "UPDATE", "$string$", ";" ], true,
        move |_, inputs| {
            let sql = inputs[0].get_string_value().unwrap();
            execute_sql_dml("UPDATE", &sql, conn)
        }
    );

    engine.register_custom_syntax(
        [ "DELETE", "$string$", ";" ], true,
        move |_, inputs| {
            let sql = inputs[0].get_string_value().unwrap();
            execute_sql_dml("DELETE", &sql, conn)
        }
    );
}

fn main() {
    let conn = Connection::open_in_memory().unwrap();

    // Create a demo table
    conn.execute("CREATE TABLE my_table (id INTEGER, name TEXT)", params![]).unwrap();

    let mut engine = Engine::new();
    let mut scope = Scope::new();

    // Register SQL syntax
    register_sql_syntax(&mut engine, &conn);

    let script = r#"
        let id = 1;
        let name = "data";

        // Insert statement
        INSERT INTO my_table (id, name) VALUES (${id}, ${name});

        // Select statement
        let result = SELECT name FROM my_table WHERE id = ${id};
        if result != () {
            print("FOUND!");
        } else {
            print("Somehow the record is still not there...");
        }
    "#;

    let ast = engine.compile(&script).unwrap();
    engine.eval_ast_with_scope(&mut scope, &ast).unwrap();
}

[schungx]

$string$ accepts any literal string, but it doesn't evaluate any expression. So "hello world" is OK but foo + bar is not OK.

$expr$ accepts a single expression, which can evaluate to anything, not just a string.

$block$ accepts a block wrapped in { ... }. It can evaluate to anything, not just a string.

[schungx]

I would not suggest to use register_custom_syntax because it is too limited. SQL is free-form, so you really need the advanced, open-ended API.

I suggest you use register_custom_syntax_with_state_raw .

https://rhai.rs/book/engine/custom-syntax-parsers.html

Basically, you'd want to look at each token, store the SQL statement up to that point in a string inside the custom state. The SQL statement is simply the text representation of each token, translated into SQL lingo, concat'ed and delimited by spaces.

Then for the next token, see if it is $. If it is, then return $block$ to parse interpolation. In your SQL, put ?. Keep that ExpressionTree in the block around.

If the next token is ;, then you're done.

For example, you see the following tokens:

SELECT MAX ( hello ) FROM table WHERE id = $ { record.id };

You string the token texts up to $, so the state would be SELECT MAX ( hello ) FROM table WHERE id = ;

Then you return $block$ there and Rhai would come back with the next token which is a block containing { record.id }.

Push ? into the SQL, now you have: SELECT MAX ( hello ) FROM table WHERE id = ?;

Once you reach ;, it is done. And you have one ExpressionTree for the one parameter.

Beware that, this way, it is impossible to treat the SQL statement as an expression. It can only be a statement.

You cannot do:

let x = SELECT MAX(hello) FROM table + 1;

But you can do:

let x = SELECT MAX(hello) FROM table;
x += 1;

That is because I cannot see a way, short of writing an SQL parser, to determine whether the SQL statement is already complete. For example, there will be this ambiguity:

let x = SELECT MAX(hello) FROM table WHERE count > ${limit} + 10;

Does the + 10 belong to the SQL or the Rhai?

[shah]

Thanks for the great information @schungx - any chance you have time to modify the pseudocode I wrote last week? (see above). I'm hoping to hand this off to one of our Rust engineers to take over and wasn't sure if it was headed in the right direction to have him take over.

Also, this makes complete sense and not at all a problem using statements:

[schungx]

I'll try to write up an example today.

[schungx]

Try this:

#[test]
fn test_custom_syntax_raw_sql() {
    let mut engine = Engine::new();

    engine.register_custom_syntax_with_state_raw(
        "SELECT",
        |symbols, lookahead, state| {
            // Build a SQL statement as the state
            let mut sql: String = if state.is_unit() { Default::default() } else { state.take().cast::<ImmutableString>().into() };

            // At every iteration, the last symbol is the new one
            let r = match symbols.last().unwrap().as_str() {
                // Terminate parsing when we see `;`
                ";" => None,
                // Variable substitution -- parse the following as a block
                "$" => Some("$block$".into()),
                // Block parsed, replace it with `?` as SQL parameter
                "$block$" => {
                    if !sql.is_empty() {
                        sql.push(' ');
                    }
                    sql.push('?');
                    Some(lookahead.into()) // Always accept the next token
                }
                // Otherwise simply concat the tokens
                _ => {
                    if !sql.is_empty() {
                        sql.push(' ');
                    }
                    sql.push_str(symbols.last().unwrap().as_str());
                    Some(lookahead.into()) // Always accept the next token
                }
            };

            // SQL statement done!
            *state = sql.into();

            match lookahead {
                // End of script?
                "{EOF}" => Ok(None),
                _ => Ok(r),
            }
        },
        false,
        |context, inputs, state| {
            // Our SQL statement
            let sql = state.as_immutable_string_ref().unwrap();
            let mut output = sql.to_string();

            // Inputs will be parameters
            for input in inputs {
                let value = context.eval_expression_tree(input).unwrap();
                output.push('\n');
                output.push_str(&value.to_string());
            }

            Ok(output.into())
        },
    );

    let mut scope = Scope::new();
    scope.push("id", 123 as INT);

    assert_eq!(
        engine.eval_with_scope::<String>(&mut scope, "SELECT * FROM table WHERE id = ${id}").unwrap(),
        "SELECT * FROM table WHERE id = ?\n123"
    );
}

Sorry for taking time.

[shah]

Do you think this use case is good enough to put into the standard documentation?

[schungx]

Certainly. I think so. At least the minimalist example I wrote is already in one of the tests.

After you guys get it done, I'd appreciate a simplified example that I can just lift and drop into the docs.

[schungx]

In effect you guys are re-creating something similar to PL/SQL or T-SQL. A scripting language with embedded SQL statements.

[shah]

You're right @schungx that's exactly what we're doing -- a scripting language with embedded SQL statements that allows SQLite to be scripted in the same way as PostgreSQL and SQL*Server are. All our work will eventually be open source so yes, we're happy to share once we've made some progress and can show working examples in isolation of our core code. Stay tuned!

Meanwhile, you mentioned "At least the minimalist example I wrote is already in one of the tests". Would you mind pointing me to that URL so our dev can take a peek?

Thanks again for the excellent project!

[schungx]

Would you mind pointing me to that URL so our dev can take a peek?

https://github.com/rhaiscript/rhai/blob/main/tests/custom_syntax.rs#L436-L499

[schungx]

Some suggestions on flexible syntax using a new lead character e.g. @:

// If `table_expr` is a string, then it is the table name.  Return data is an array of object maps
let x = SELECT * FROM @{table_expr} WHERE id = ${id};

// Return data is a single value, or () if NULL
let x = SELECT MAX(timestamp) FROM table;

// If `fields` is an array of strings, it is the fields list.  Return data is an array of object maps
// If `fields` is a single string, it is the target field.  Return data is an array of values
let x = SELECT @{fields} FROM table WHERE id = ${id};

// If `field` is a string, then it is a field name. Return data is an array of values
let x = SELECT name FROM table WHERE @{field} = ${criteria};

// If `criteria` is an object map, it is a list of `key` = `value` WHERE clauses AND'ed together. Return data is an array of values
let x = SELECT name FROM table WHERE @{criteria};

// If `keys` is an array of strings, it is the list of fields.  If `keys` is an object map, the keys are the fields list.
// Return data is an array of object maps
let x = SELECT @{keys}, SUM(value) FROM table GROUP BY @{keys} ORDER BY @{keys}

In order to do this, you'll have to insert markers into the prepared SQL statement, and those markers will later be replaced by the actual field names to form the actual prepared SQL statement.

So

SELECT @{fields} FROM table WHERE id = ${id}

may become

SELECT @ FROM table WHERE id = ?

And the ExpressionTree values will be fields and id. The state can store the positions of the markers (you cannot just simply do a text-replace, otherwise you'll replace things in string literals also). Short of putting in a full SQL parser, the only thing you can do is to store the positions of those markers. For example, @ is stored as 1:8, indexing to expression value zero. Then it is a simple matter of replacing all marker positions (starting from the back to the front so as not to muck up the offsets) with texts from the expressions.

Finally, the expression values that correspond to markers will be removed, leaving only parameters that can be passed directly to the now-complete SQL statement.

[shah]

Great ideas @schungx! Our developer is running a bit behind due to other assignments but should be getting onto this work later this week. Thanks for keeping the conversation going!

[schungx]

You may also increase flexibility by allowing something like this:

// ... indicates the SQL is not complete and should not be run right away
let x = SELECT * FROM table ...;

if id != () {
    x += WHERE id = ${id};
}

x.ecec();

[shah]

That's a great idea!