How to use variable in REMOVE statement

[plus1xp-everyday]

Hi all,

Am trying to play around few query statements in a rust project embedding surrealdb .

While using REMOVE statement with table name as variable, I am encountering below error:

Error: Db(InvalidQuery(RenderedError { text: "Failed to parse query at line 1 column 14 expected an identifier", snippets: [Snippet { source: "REMOVE TABLE $table", truncation: None, location: Location { line: 1, column: 14 }, offset: 13, explain: None }] }))

Following is the function:

use std::collections::BTreeMap;
use surrealdb::sql::{Table, Value};
...

#[tokio::main]
async fn main() -> Result<()> {
    ...

    remove(&db, "cars").await?;
    Ok(())
}
...

async fn remove(db: &Surreal<Db>, table: &str) -> Result<()> {
    let sql = "REMOVE TABLE $table";
    let tbl: BTreeMap<String, Table> = [("table".into(), table.into())].into();
    let removed = db.query(sql).bind(tbl).await?;
    dbg!(removed);
    Ok(())
}

However, when I am not trying to use any variable by hard coding the table name, the query works fine:

async fn remove(db: &Surreal<Db>) -> Result<()> {
   let sql = "REMOVE TABLE cars";
   let removed = db.query(sql).await?;
   dbg!(removed);
   Ok(())
}

Am I missing something here?

Thanks in advance. Pardon any silly error 🙇

[naisofly]

Hey @plus1xp-everyday !

Unfortunately, our parser doesn't currently allow using variable bindings in some statements like DEFINE and REMOVE, as in this case. We need to put that directly in the query itself. So instead of
let sql = "REMOVE TABLE $table";

we can use
let sql = format!("REMOVE TABLE {table}");

So that function can be written as

async fn remove(db: &Surreal<Db>, table: &str) -> Result<()> {
    let sql = format!("REMOVE TABLE {table}");
    db.query(sql).await?.check()?;
    Ok(())
}

Another interesting option for Rust users only is to bypass the parser completely and pass in the remove statement directly:

async fn remove(db: &Surreal<Db>, table: &str) -> Result<()> {
    let statement = RemoveStatement::Table(RemoveTableStatement {
        name: table.into(),
    });
    db.query(statement).await?.check()?;
    Ok(())
}

This should be much faster because it bypasses the parser completely in both the client and the server. This option should be safer too if table is coming from user input. With the first option, you can easily get SQL injected if you don't validate the input first.

Hope this helps!

[plus1xp-everyday]

Hi @naisofly,

Bypassing the parser seems to be a better alternative.. Thanks a ton 😃