Using Named Parameterized Queries in DataGrip SQL Console

Quick answer: run a query with :name placeholders and fill them in the Parameters dialog

In DataGrip you can write SQL that uses named placeholders (e.g., :emp_id) instead of hard‑coding values. When you execute the statement, DataGrip shows a Parameters toolbar button. Clicking it opens a dialog where you supply a value for each placeholder. The IDE then sends the statement to the database as a java.sql.PreparedStatement, which protects against SQL injection and lets the database reuse the execution plan.

Worked example: PostgreSQL console

1. Create a data source – Open the Data Sources dialog (Ctrl+Alt+Shift+S on Windows/Linux, ⌥⇧⌘S on macOS), add a PostgreSQL connection, and test it. You need read access to the schema you’ll query.
2. Open a SQL console – Right‑click the data source and choose Open Console. The console defaults to the PostgreSQL dialect.
3. Write a query with named placeholders – In the editor type:

   SELECT * FROM employees
   WHERE id = :emp_id
     AND department = :dept;

4. Execute the statement – Press Ctrl+Enter (or click the run icon). DataGrip does not send the query yet; instead it highlights the Parameters button on the toolbar.
5. Fill in the parameters – Click the Parameters button. A small window appears with two fields labeled emp_id and dept. Enter, for example, 42 for emp_id and 'Sales' for dept (note the quotes for string literals). Press OK.
6. Run the query again** – Now press Ctrl+Enter (or click the run icon). DataGrip sends the statement as a PreparedStatement with the supplied values bound to the placeholders.
7. Check the result** – The result grid shows only rows from the employees table where id = 42 and department = 'Sales'. If no rows match, you see an empty grid.
8. Verify a PreparedStatement was used** – With the console still open, click the Explain Plan icon (or press Ctrl+Shift+Enter). In the plan output look for a node labeled PreparedStatement or for the text Parameter: :emp_id, :dept. Its presence confirms that DataGrip did not substitute the placeholders as plain text.

How the mechanism works

When DataGrip detects a colon‑prefixed identifier (:name) in the SQL text, it treats it as a named parameter rather than part of the SQL syntax. The IDE extracts all distinct parameter names, builds a java.sql.PreparedStatement with those names, and then binds the values you entered in the Parameters dialog to the corresponding indices. The driver (e.g., PostgreSQL’s JDBC driver) handles the actual binding; if the driver does not support named parameters, DataGrip falls back to positional binding, which can cause errors if the order of placeholders does not match the order you filled in the dialog.

Limits and version notes

• Driver support – The feature relies on the JDBC driver implementing java.sql.PreparedStatement with named parameter support. Recent PostgreSQL, MySQL 8.0+, and Oracle 12c+ drivers work. Older MySQL 5.x or Oracle 11g drivers may ignore the names and treat the statement as positional, leading to Invalid column index errors if you mix placeholders.
• Dialect sensitivity** – Some dialects (e.g., SQLite) do not recognize the colon syntax as a placeholder; they treat :name as a literal string, causing a syntax error. Switching the console dialect may break the query.
• Mixing placeholder types** – Do not combine :name with positional ? placeholders in the same statement. DataGrip cannot map them correctly and will either bind nothing or throw an error.
• Forgotten Parameters dialog** – If you run the query without opening the Parameters dialog, DataGrip binds NULL to every placeholder, often returning zero rows or causing constraint violations.
• JDBC batch execution** – Enabling Use JDBC batch execution in the data source settings can hide individual parameter values in the console log, making debugging harder. Disable it when you need to inspect each execution.

Common mistakes and how to avoid them

1. Assuming the placeholder works everywhere** – Test the query in a console for the target dialect before reusing it in a script or application.
2. Entering values with wrong quoting** – Remember that the Parameters dialog expects raw values; you do not add SQL quotes around strings. Adding them results in literal quotes being stored (e.g., '"Sales"').
3. Running the statement twice without resetting** – After the first execution, the Parameters dialog retains the last values. If you edit the SQL but forget to update the dialog, you may run the query with stale data. Always verify the dialog before executing.
4. Misinterpreting the Explain Plan** – Some plans show the original SQL with placeholders; look specifically for the PreparedStatement label or the Parameter: line to be sure binding occurred.

Practical verification steps

To confirm that named parameters are working as expected:

1. Run a simple query with a single placeholder, e.g., SELECT :val AS test;.
2. Open the Parameters dialog, set val to 123, and execute.
3. Check that the result grid returns a single column test with value 123.
4. Open Explain Plan and verify the plan mentions a PreparedStatement or shows the bound value.
5. Change the value in the dialog to 999 and run again; the result should update accordingly, proving the statement is re‑used with new bindings.

If any of these steps fail, revisit the driver version, dialect setting, or check for mixed placeholder types.