Understanding Cypher Prepared Statements in Apache Age
Tags: apacheag, postgres, database, graphdatabase
Published: 2024-01-13 13:03:40

Optimizing your cypher queries with prepared statements can significantly improve performance.When you prepare a statement cypher parses and analyzes the query then stores the execution plan for later use. This means when you execute the prepared statement cypher can skip the parsing and planning phase and go straight to executing the query

This is useful for queries you execute often with different filter values for example the preparation does the hard work  of parsing and optimizing the  cypher query up front  so each execution after that has minimal overhead.

**How to format cypher parameters in prepared statements**
when using cypher prepared statements in Apache Age  formatting your parameters properly is crucial if done incorrectly you will get errors and your queries won't run.
The following are tips one can use  to construct cypher prepared statements.

**_1.Use $ for params_**
cypher parameters in Apache Age always start with a `$` followed by a letter and alphanumeric characters.e.g

`$name`

do not use numbers,symbols or spaces in the name stick to numbers,letters and underscores.

**_2.Place cypher parameters in the cypher query string_**
When writing your cypher query within the prepared statement include the cypher parameters values need to be inserted like this:

```
MATCH (v:Person)
WHERE v.name = $name
RETURN v
```
do not include postgresql style parameters(i.e $1, $2) in the  cypher query string .only use cypher parameters.

**Preparing the statement**
To prepare a statement, use the `PREPARE` clause and include cypher parameters($name) in the query string.Then ,call the cypher function and pass and sql parameters($1) as the third argument.

e.g

```
PREPARE cypher_stored_procedure(agtype) AS
SELECT *
FROM cypher('expr', $$
     MATCH (v:Person)
     WHERE v.name =$name
     RETURN v
$$, $1)
AS (v agtype);
```

**_Executing the prepared statement_**
To execute the prepared statement, call `EXECUTE` and pass an agtype map with the cypher parameters values.Exclude the $ when passing the values.

e.g

```
EXECUTE find_person('{"age":30}');

```
This will execute the prepared find_person query, substituting 30 for the $1 parameter.

**Benefits of Prepared Statements**
There are a few key benefits to using prepared statements 

**- Security:**
 Prepared statements prevent SQl injection attacks.The parameters are bound to the statement so malicious input cannot be executed.

**- Efficiency:**
Prepared statements are compiled once and then executed multiple times.This makes them faster than executing a new query string for each execution.

**- Readability:**
Prepared statement clearly separate the query string from the input parameters.This makes queries easier to read and debug.

**- Reusability**
The same prepared statement can be executed with different parameters,reducing duplication.

**When should  you use prepared statements**
As we now know prepared statements are useful when you want to run the same query multiple times with different parameters.The following reasons may be a determinant on when to use prepared statements and they include:

**_Security_**
Prepared statements can be used in case of security concerns such as sql injections in that prepared statements help prevent SQL injection attacks.The parameters are bound to the statement,so malicious input cannot modify the sql code

**_Performance_**
Prepared statements can come in handy in terms of performance  issues in your database in that through prepared statements are compiled once then executed multiple times with different parameters which in turn avoids the overhead of compiling the statements each time improving performance.


**Conclusion**
Prepared statements allow  you to write clean reusable cypher queries with parameters.By understanding how to  define and execute prepared statements,you can  improve the security,performance and readability of your queries.