DEV Community

Namsi Lydia
Namsi Lydia

Posted on

Understanding Cypher Prepared Statements in Apache Age

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


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 = $name
Enter fullscreen mode Exit fullscreen mode

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.


PREPARE cypher_stored_procedure(agtype) AS
FROM cypher('expr', $$
     MATCH (v:Person)
     WHERE =$name
     RETURN v
$$, $1)
AS (v agtype);
Enter fullscreen mode Exit fullscreen mode

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.


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

Enter fullscreen mode Exit fullscreen mode

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:

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

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.

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.

Top comments (0)