Local Interpolation

SQL Interpolation

Interpolation is DISABLED by default. Set dat.EnableInterpolation = true to enable.

dat can interpolate locally to inline query arguments. Let's start with a normal SQL statements with arguments

db.Exec(
    "INSERT INTO (a, b, c, d) VALUES ($1, $2, $3, $4)",
    []interface{}[1, 2, 3, 4],
)

When the statement above gets executed:

1. The driver checks if this SQL has been prepared previously on the current connection, using the SQL as the key
2. If not, the driver sends the SQL statement to the database to prepare the statement
3. The prepared statement is assigned to the connection
4. The prepared statement is executed along with arguments
5. Received data is sent back to the caller

In contrast, dat can interpolate the statement locally resulting in a SQL statement with often no arguments. The code above results in this interpolated SQL

"INSERT INTO (a, b, c, d) VALUES (1, 2, 3, 4)"

When the statement above gets executed:

1. The statement is treated as simple exec and sent with args to database, since len(args) == 0
2. Received data is sent back to the caller

Interpolation Safety

As of Postgres 9.1, the database does not process escape sequence by default. See String Constants with C-style Escapes. In short, all backslashes are treated literally.

dat escapes single quotes (apostrophes) on small strings, otherwise it uses Postgres' dollar quotes to escape strings. The dollar quote tag is randomized at init. If a string contains the dollar quote tag, the tag is randomized again and if the string still contains the tag, then single quote escaping is used.

As an added safety measure, dat checks the Postgres database standard_conforming_strings setting value on a new connection when dat.EnableInterpolation == true. If standard_conforming_strings != "on" then set set it to "on" or disable interpolation. dat will panic if it the setting is incompatible.

Why is Interpolation Faster?

Here is a comment from lib/pq connection source, which was prompted by me asking why was Python's psycopg2 so much faster in my benchmarks a year or so back:

// Check to see if we can use the "simpleExec" interface, which is
// *much* faster than going through prepare/exec
if len(args) == 0 {
    // ignore commandTag, our caller doesn't care
    r, _, err := cn.simpleExec(query)
    return r, err
}

That snippet bypasses the prepare/exec roundtrip to the database.

Keep in mind that prepared statements are only valid for the current session and unless the same query is be executed MANY times in the same session there is little benefit in using prepared statements other than protecting against SQL injections. See Interpolation Safety section above.

More Reasons to Use Interpolation

• Performance improvement
• Debugging is simpler with interpolated SQL
• Use SQL constants like NOW and DEFAULT
• Expand placeholders with expanded slice values $1 => (1, 2, 3)

[]byte, []*byte and any unhandled values are passed through to the driver when interpolating.