Databases with Groovy SQL

This content was generated with the assistance of AI and should be verified against the official documentation before being relied on in production.

The reference material consulted while preparing these pages is listed in the section bibliography.

This page is written against Apache Groovy 6.0 (6.0.0, 2026-09-22) and every example was run with Groovy 6.0.0 on JDK 26 (checked on 2026-10-14) against H2 2.3.232, an embedded database that runs inside the script’s JVM and keeps its data in memory, so nothing is installed and nothing is written to disk. groovy.sql.Sql is a thin layer over JDBC: it opens the connection, prepares the statements, binds the parameters, walks the ResultSet and closes everything, and in return you write SQL and closures. It lives in the groovy-sql module, which the standard Groovy distribution already contains. The module is a convenience for scripts and small tools, not an object-relational mapper: there is no entity model and no session. The greenhouse domain (plants and their sensor readings) is the same as on the other data pages, and the same query ideas applied to collections instead of tables are on GINQ: SQL-like Collection Queries. The SQL language itself is on Querying Data (SELECT) and Transaction Control, and the closures used throughout are on Closures.

Getting a driver onto the classpath

A JDBC driver is an ordinary jar, and a script can fetch it with Grape (see Grape Dependency Management). Drivers have one catch: java.sql.DriverManager only accepts a driver that is visible from the class loader of the code that asks for the connection, and a @Grab`bed jar normally lands in a child class loader that `DriverManager cannot see. The usual remedy is @GrabConfig(systemClassLoader = true), which loads the grabbed jars into the system class loader. This is what happens without it (a script with only the @Grab, the same Sql.newInstance call as below):

java.sql.SQLException
No suitable driver found for jdbc:h2:mem:x

A DataSource object is created by your own code and bypasses DriverManager, so it works without the annotation (shown below). In a project built with Gradle or Maven the driver is simply a dependency and none of this applies.

Source: Groovy documentation: Connecting using Grab

Connecting

Sql.newInstance takes a JDBC URL, user, password and driver class name. H2 creates an in-memory database named in the URL the first time anyone connects, and its default administrator is the user sa with an empty password, which is what the examples use. The in-memory database disappears when the last connection to it closes.

@GrabConfig(systemClassLoader = true)
@Grab('com.h2database:h2:2.3.232')
import groovy.sql.Sql
import org.h2.jdbcx.JdbcDataSource

def sql = Sql.newInstance('jdbc:h2:mem:greenhouse', 'sa', '', 'org.h2.Driver')
println sql.connection.metaData.databaseProductName
println sql.connection.metaData.databaseProductVersion

Sql.withInstance('jdbc:h2:mem:scratch', 'sa', '', 'org.h2.Driver') { Sql db ->
    println db.firstRow('select 1 as one').one
}
Sql.withInstance(url: 'jdbc:h2:mem:scratch2', driver: 'org.h2.Driver') { db ->
    println db.firstRow('select 2 as two').two
}

def ds = new JdbcDataSource(url: 'jdbc:h2:mem:pooled;DB_CLOSE_DELAY=-1', user: 'sa')
def viaDs = new Sql(ds)
viaDs.execute 'create table t (x int)'
viaDs.close()
println new Sql(ds).firstRow('select count(*) as n from t')

H2
2.3.232 (2024-08-11)
1
2
[N:0]

Three ways to get an Sql are shown. newInstance returns an object you must close() yourself. withInstance passes the Sql to a closure and closes it in a finally, which is the form to prefer. A Map of named arguments (url, driver, and user together with password) does the same without positional arguments. Finally new Sql(dataSource) takes any javax.sql.DataSource, so a connection pool (HikariCP or the pool of a container) slots in. With a DataSource each statement borrows a connection and returns it, which is why the in-memory table above survives the close() only because the URL carries DB_CLOSE_DELAY=-1 (keep the database alive when no connection is open). Sql also implements AutoCloseable, so try (def s = Sql.newInstance(...)) { ... } works too. The rest of this page assumes sql is the first connection.

Source: Groovy documentation: Connecting to the database, connecting with a DataSource

Creating tables

DDL goes through execute, which accepts a plain string or a GString and returns true when the statement produced a result set. Triple-quoted strings keep multi-line SQL readable.

sql.execute '''
    create table plant (
        id int auto_increment primary key,
        name varchar(40) not null,
        zone varchar(10) not null,
        min_moisture int not null
    )'''
sql.execute '''
    create table reading (
        id int auto_increment primary key,
        plant_id int not null references plant(id),
        taken_at int not null,
        celsius double not null,
        moisture int not null
    )'''
println sql.rows('select count(*) as n from plant')

[[N:0]]

The column is called taken_at and not hour because HOUR is a reserved word in H2 and the create table fails with a syntax error, a reminder that identifiers that look innocent can collide with the dialect of the database in use.

Source: Groovy documentation: Creating tables

Inserting, updating and deleting

executeInsert runs an insert and returns the generated keys as a list of rows, executeUpdate returns the update count, and execute is the all-purpose variant. Parameters can be supplied three ways, all of which become JDBC bind variables:

as $name interpolations inside a GString,
as ? placeholders with a list of values, or
as :name placeholders with a map.

def name = 'tomato'
def zone = 'south'
def keys = sql.executeInsert "insert into plant (name, zone, min_moisture) values ($name, $zone, ${40})"
println keys
println keys[0][0]
sql.executeInsert 'insert into plant (name, zone, min_moisture) values (:name, :zone, :min)',
        [name: 'basil', zone: 'north', min: 35]
sql.executeInsert 'insert into plant (name, zone, min_moisture) values (?, ?, ?)', ['fern', 'east', 60]
println sql.executeUpdate("update plant set min_moisture = ${45} where name = $name")

[[1]]
1
1

Why GString parameters are safe

The syntax above looks like string interpolation, which in most languages is how SQL injection happens. It is not interpolation here. When a GString reaches Sql, the method receives the literal parts and the values separately, builds the statement text with a ? for every $value, and passes the values to a PreparedStatement as bound parameters. The database never parses the value as SQL.

The proof is a value written to be an attack. Bound as a parameter it is just a name that no plant has:

def wanted = "x' or '1'='1"
println sql.rows("select name from plant where name = $wanted").size()

The rule that follows is simple: put the $value where a value goes, and never surround it with quotes. A quoted '$value' is the one form that defeats the mechanism, and the last section shows how Groovy 6.0 handles it. Values bind only where a SQL value is allowed. A table or column name cannot be a bind variable, which is the job of Sql.expand (see below).

Source: Groovy documentation: Basic CRUD operations

Reading rows

rows returns a list of GroovyRowResult, eachRow calls a closure per row without building the list (the right choice for a big result), and firstRow returns the first row or null. A GroovyRowResult is a Map whose keys are the column labels, and the properties are case-insensitive, so row.name and row.NAME are the same cell. A row also supports a numeric index.

println sql.rows('select * from plant order by id')
sql.eachRow('select name, zone from plant order by name') { row ->
    println "${row.name} lives in ${row.zone} (first column: ${row[0]})"
}
println sql.firstRow('select count(*) as n from plant')
println sql.firstRow('select * from plant where name = ?', ['nope'])
def row = sql.firstRow('select * from plant where id = 1')
println row.getClass().name
println row.keySet()
println row.NAME == row.name
sql.query('select name from plant order by name') { rs ->
    while (rs.next()) println rs.getString('name')
}
sql.executeInsert 'insert into plant (name, zone, min_moisture) values (?, ?, ?)', ['weed', 'east', 1]
println sql.executeUpdate('delete from plant where name = ?', ['weed'])
println sql.rows('select id, name from plant order by id')

[[ID:1, NAME:tomato, ZONE:south, MIN_MOISTURE:45], [ID:2, NAME:basil, ZONE:north, MIN_MOISTURE:35], [ID:3, NAME:fern, ZONE:east, MIN_MOISTURE:60]]
basil lives in north (first column: basil)
fern lives in east (first column: fern)
tomato lives in south (first column: tomato)
[N:3]
null
groovy.sql.GroovyRowResult
[ID, NAME, ZONE, MIN_MOISTURE]
true
basil
fern
tomato
1
[[ID:1, NAME:tomato], [ID:2, NAME:basil], [ID:3, NAME:fern]]

Column labels come back in the case the database reports (H2 upper-cases them), but because the property lookup ignores case the script does not have to care. query hands over the raw ResultSet for the cases where the closure-per-row model does not fit, and a rows closure variant receives the ResultSetMetaData first (see metadata below). The weed row was inserted and removed only to show executeUpdate returning the count of deleted rows, and the auto-increment counter does not go back, which is why the next plant added gets id 4 or later and not 3.

Source: Groovy documentation: Reading rows

Transactions

By default every statement runs in auto-commit mode. withTransaction turns auto-commit off for the closure, commits when the closure returns, and rolls back when it throws, so a series of updates either all apply or none does. The exception is logged at WARNING by groovy.sql.Sql on stderr and then rethrown.

try {
    sql.withTransaction {
        sql.executeUpdate 'update plant set min_moisture = 99'
        throw new IllegalStateException('boom')
    }
} catch (IllegalStateException e) {
    println "rolled back: ${e.message}"
}
println sql.rows('select min_moisture from plant order by id')*.min_moisture
sql.withTransaction {
    sql.executeUpdate "update plant set min_moisture = 50 where name = 'basil'"
}
println sql.rows('select min_moisture from plant order by id')*.min_moisture

rolled back: boom
[45, 35, 60]
[45, 50, 60]

The closure runs on the same connection as the rest of the Sql instance, so a transaction is tied to one Sql object and is not shared by another one on the same database. commit() and rollback() are also available for manual control, and cacheConnection keeps one connection for several calls when the Sql was built from a DataSource. The SQL-level concepts (isolation levels, savepoints) are on Transaction Control.

Source: Groovy documentation: Working with transactions

Batches

withBatch collects statements and sends them to the database in one round trip, which matters for loading many rows. The version with a SQL string takes a PreparedStatement wrapper and addBatch receives the parameters. The version without a string takes a Statement wrapper and addBatch receives complete SQL strings. The optional first integer is the batch size at which the wrapper flushes by itself. Both return the update counts.

def counts = sql.withBatch(2, 'insert into reading (plant_id, taken_at, celsius, moisture) values (?, ?, ?, ?)') { ps ->
    ps.addBatch(1, 6, 28.5, 52)
    ps.addBatch(1, 9, 31.2, 41)
    ps.addBatch(2, 6, 21.5, 38)
    ps.addBatch(3, 9, 19.0, 66)
}
println counts
def more = sql.withBatch { stmt ->
    stmt.addBatch('insert into plant (name, zone, min_moisture) values (\'mint\', \'north\', 55)')
    stmt.addBatch('insert into plant (name, zone, min_moisture) values (\'cactus\', \'south\', 10)')
}
println more

[1, 1, 1, 1]
[1, 1]

The second form builds SQL as text, so it is only for fixed statements. Anything with data from outside uses the prepared form with bind variables. If a statement in a batch fails, Sql logs the failure and rethrows the exception, and what was already applied depends on the driver and on auto-commit; wrap withBatch in a withTransaction when the batch must be all or nothing.

Source: Groovy documentation: Using batches

Pagination

rows and eachRow accept an offset and a maximum number of rows after the SQL (and parameters). The offset is 1-based, so 1, 2 is the first two rows. Pagination of this kind is applied on the client side while the result is read, and it always needs an order by, because a database promises no particular row order otherwise.

println sql.rows('select name from plant order by id', 1, 2)*.name
println sql.rows('select name from plant order by id', 3, 2)*.name

[tomato, basil]
[fern, mint]

For tables large enough that skipping rows is itself expensive, the database’s own offset ... fetch or a keyset condition (where id > :last) is the better tool.

Source: Groovy documentation: Performing pagination

Metadata

The ResultSetMetaData of a query is available to a second closure given to eachRow (or rows), which runs once before the rows and lets a script learn the column names and types of a query it did not write. For the catalog (tables, columns, keys), use the connection’s DatabaseMetaData.

sql.eachRow('select * from plant', { meta ->
    println((1..meta.columnCount).collect { "${meta.getColumnLabel(it)}:${meta.getColumnTypeName(it)}" })
}) { }
def tables = sql.connection.metaData.getTables(null, 'PUBLIC', '%', ['TABLE'] as String[])
while (tables.next()) println tables.getString('TABLE_NAME')
tables.close()

[ID:INTEGER, NAME:CHARACTER VARYING, ZONE:CHARACTER VARYING, MIN_MOISTURE:INTEGER]
PLANT
READING

Source: Groovy documentation: Fetching metadata

Named and named-ordinal parameters

A :name placeholder takes its value from a map passed as the parameter. A ?1.property placeholder (named-ordinal) takes the property of the first element of the parameter list, which lets a bean stand in for several values; ?2.property is the second element. Both forms are rewritten to ordinary ? markers before the statement reaches the driver.

println sql.rows('select name from plant where zone = :z and min_moisture > :m order by name', [z: 'north', m: 10])*.name
class Wanted { String zone; int min }
println sql.rows('select name from plant where zone = ?1.zone and min_moisture > ?1.min',
        [new Wanted(zone: 'south', min: 20)])*.name

[basil, mint]
[tomato]

The :name.property form (a map entry that is a bean) is not supported: the placeholder is rewritten to a single ? and the .property stays in the SQL text, which the database rejects. Use ?1.property for beans.

Source: Groovy documentation: Named and named-ordinal parameters

Collection parameters for IN (6.0)

A single ? binds a single value, so where zone in (?) with a list does not mean what it looks like. Groovy 6.0 adds Sql.inList(collection), a parameter that expands to as many placeholders as the collection has elements, and works in all three forms: a GString, a positional list and a named map.

def zones = ['north', 'east']
println sql.rows("select name from plant where zone in (${Sql.inList(zones)}) order by name")*.name
println sql.rows('select name from plant where zone in (:zs) order by name', [zs: Sql.inList(zones)])*.name
println sql.rows('select name from plant where zone in (?) order by name', [Sql.inList(['south'])])*.name

[basil, fern, mint]
[basil, fern, mint]
[cactus, tomato]

Every element is still a bound value. The other helper is Sql.expand(value), which does the opposite: it inserts the value into the SQL text without binding it, for the parts that cannot be parameters (a column list, a table name, a sort direction).

def column = 'name'
println sql.rows("select ${Sql.expand(column)} from plant order by ${Sql.expand('id desc')}")*.name

[cactus, mint, fern, basil, tomato]

expand is deliberately explicit, so that the one place where text goes into the statement is easy to find in a code review. Pass it only constants or values checked against a fixed list of allowed names, never user input.

Source: Groovy 6.0 release notes (the Groovy documentation page on databases does not cover Sql.inList)

Stored procedures and functions

call runs a JDBC callable statement. The first argument is the call escape syntax, and the parameter list marks output parameters with the constants of Sql (Sql.INTEGER, Sql.VARCHAR, …). Output values are passed to a closure after the call, and InOutParameter, ResultSetOutParameter and the call variants returning result sets cover the other directions. H2 lets you define a Java function from a script with create alias, which gives a self-contained example without a server-side language:

sql.execute 'create alias double_it as $$ int doubleIt(int x) { return x * 2; } $$'
sql.call('{ ? = call double_it(?) }', [Sql.INTEGER, 21]) { result -> println result }
println sql.firstRow('select double_it(min_moisture) as twice from plant where name = ?', ['basil']).twice

42
100

The same function is callable from a plain select. On a database with real stored procedures (PostgreSQL, Oracle, SQL Server) the Groovy side is the same, with the procedure name in the call escape. The SQL side is covered on Triggers and Stored Procedures.

Source: Groovy documentation: Stored procedures

DataSet

sql.dataSet('plant') returns a groovy.sql.DataSet, a view of a table that is queried with closures. findAll and sort do not run anything: the closure is translated into the where and order by parts of a new DataSet, and the SQL runs when rows() (or each) is called. add inserts a row from named arguments.

def plants = sql.dataSet('plant')
println plants.rows()*.name
def thirsty = plants.findAll { it.min_moisture > 40 }
println thirsty.sql
println thirsty.parameters
println thirsty.rows()*.name
println plants.sort { it.name }.reverse().sql
plants.add(name: 'rosemary', zone: 'south', min_moisture: 20)
println plants.rows().size()

[tomato, basil, fern, mint, cactus]
select * from plant where min_moisture > ?
[40]
[tomato, basil, fern, mint]
select * from plant order by name DESC
6

Only a small set of expressions can appear in a DataSet closure (comparisons of columns with constants, &&, ||, !), because the closure is decompiled into SQL and not executed. Anything outside that fails when the query is built, and joins, grouping and aggregates are not available at all. A DataSet is convenient for single-table filters. For anything larger write the SQL.

Source: Groovy documentation: Using DataSets

SQL injection checks

Everything above is safe as long as values are passed as parameters. The one way to get it wrong with a GString is to put quotes around the interpolation: '$wanted'. The GString machinery would have to splice the text in, because a ? inside quotes is not a bind variable. Groovy covers this in two places.

At runtime (6.0), Sql refuses such a query. It detects an interpolation that is directly surrounded by quotes, throws a java.sql.SQLException and does not run the statement.

try {
    sql.rows("select name from plant where name = '$wanted'")
} catch (SQLException e) {
    println e.class.name
    println e.message
}

java.sql.SQLException
Detected a quoted dynamic expression (one starting with $) in a Groovy SQL query. Surrounding such an expression with quotes prevents the use of a JDBC PreparedStatement and exposes a SQL injection vulnerability. Remove the surrounding quotes so the value can be bound safely as a parameter. The unsafe expression so far is: select name from plant where name = '?'. To restore the previous (insecure) behaviour, set the system property 'groovy.sql.injection.lenient' to 'true'.

The message names the escape hatch, the system property groovy.sql.injection.lenient (available as the constant Sql.INJECTION_LENIENT). Setting it to true gives the old behaviour of splicing the text in, with only a warning logged, and the injection works:

System.setProperty(Sql.INJECTION_LENIENT, 'true')
println sql.rows("select name from plant where name = '$wanted'").size()
System.clearProperty(Sql.INJECTION_LENIENT)

All six rows came back for a name that matches none: the value rewrote the where clause. The property exists for legacy code that cannot be fixed at once; new code should never set it.

At compile time, the SqlInjectionChecker is a type checking extension in the groovy-typecheckers module (see Built-in Type Checkers and Static Type Checking & @CompileStatic). Activated on a method or class, it reports the same mistake as a compile error, before the script runs:

@GrabConfig(systemClassLoader = true)
@Grab('com.h2database:h2:2.3.232')
import groovy.sql.Sql

@groovy.transform.TypeChecked(extensions = 'groovy.typecheckers.SqlInjectionChecker')
def lookup(Sql sql, String wanted) {
    sql.rows("select name from plant where name = '$wanted'")
}

[Static type checking] - Possible SQL injection: the interpolated value ${wanted} is surrounded by SQL quotes, which prevents it from being bound as a JDBC PreparedStatement parameter. Remove the surrounding quotes so the value is bound safely.
 @ line 7, column 14.
       sql.rows("select name from plant where name = '$wanted'")
                ^

1 error

The checker looks at calls to the Sql methods that take SQL (execute, executeInsert, firstRow, rows, eachRow, query, call). A message may also be a warning only, when the interpolated value comes from a field that could be reassigned elsewhere. The checker and the runtime rule target the same bug from both ends, and neither replaces a code review for the Sql.expand calls.

Source: Groovy 6.0 release notes, Groovy documentation: Working with a relational database

Organising database work

The scripts above share one sql variable, which is fine for a script but not for an application. The usual shape is a small data access class per table or aggregate, built around a shared DataSource (in production, a connection pool). Each DAO keeps its own Sql made from that DataSource, so each statement borrows a connection and gives it back, and the rest of the program calls methods such as add or thirsty without seeing SQL. A transaction that spans several statements stays inside one DAO method, where withTransaction holds a single connection until it commits.

@GrabConfig(systemClassLoader = true)
@Grab('com.h2database:h2:2.3.232')
import groovy.sql.GroovyRowResult
import groovy.sql.Sql
import javax.sql.DataSource
import org.h2.jdbcx.JdbcDataSource

class PlantDao {
    private final Sql sql

    PlantDao(DataSource ds) { sql = new Sql(ds) }   // one Sql per DAO, a connection per statement

    void createSchema() {
        sql.execute '''create table if not exists plant (
            id int auto_increment primary key, name varchar(40) not null, litres int not null)'''
    }

    int add(String name, int litres) {
        sql.executeInsert("insert into plant (name, litres) values ($name, $litres)")[0][0] as int
    }

    GroovyRowResult find(int id) { sql.firstRow("select * from plant where id = $id") }

    List<String> thirsty(int minLitres) {
        sql.rows("select name from plant where litres >= $minLitres order by name")*.name
    }

    void moveWater(int fromId, int toId, int litres) {
        sql.withTransaction {
            sql.executeUpdate "update plant set litres = litres - $litres where id = $fromId"
            sql.executeUpdate "update plant set litres = litres + $litres where id = $toId"
        }
    }
}

def ds = new JdbcDataSource(url: 'jdbc:h2:mem:dao;DB_CLOSE_DELAY=-1', user: 'sa')
def plants = new PlantDao(ds)
plants.createSchema()
def tomato = plants.add('tomato', 4)
def basil = plants.add('basil', 1)
plants.add('lettuce', 2)
println plants.find(tomato)
println plants.thirsty(2)
plants.moveWater(tomato, basil, 1)
println "${plants.find(tomato).litres} / ${plants.find(basil).litres}"

[ID:1, NAME:tomato, LITRES:4]
[lettuce, tomato]
3 / 2

Because the DAO takes a DataSource and not a URL, a test can hand it an in-memory H2 database (as here) and the application a pool for the real server, with no change to the class. Keep the queries as GString parameters, as in every other example on this page, so the values are still bound safely.

Source: Groovy documentation: Connecting with a DataSource and working with transactions

Where the book is outdated

The Manning book Groovy in Action (publisher page in the references) covers groovy.sql.Sql and DataSet, and the core API it describes (rows, eachRow, firstRow, execute, executeUpdate, withTransaction) is unchanged. Check these points against the current documentation:

The older text assumes a driver on the classpath of a command-line groovy script. Today @Grab with @GrabConfig(systemClassLoader = true) is the usual way to bring in the driver for a script, and the DriverManager visibility rule that makes the annotation necessary is described above.
Sql.inList is new in 6.0, and Sql can now be used in try-with-resources (it implements AutoCloseable). Check the API pages for the other methods against the version in use.
Quoted interpolations such as '$value' used to be accepted with a warning. Since 6.0 they are rejected unless the lenient system property is set, and SqlInjectionChecker finds them at compile time.
Version and JDK baselines: Groovy 6.0 needs JDK 17 or later.