Database (JDBC)

DieselEngine provides direct JDBC access to PostgreSQL. Scripts can create tables, run queries, insert/update/delete data, and work with JSONB columns and PostgreSQL arrays.

Connection

No manual connection setup is needed. The diesel object uses the application's shared connection pool. All SQL runs against the engine schema.

Prepared Statements

Use diesel.prepareStatement(sql) for all queries with parameters. This prevents SQL injection and handles type conversion.

const ps = diesel.prepareStatement(
  'SELECT id, data FROM engine."users" WHERE id = ?'
);
ps.setLong(1, userId);
const rs = ps.executeQuery();

while (rs.next()) {
  const id = rs.getLong("id");
  const data = JSON.parse(rs.getString("data"));
  console.log(id, data.name);
}

WARNING

Always use prepared statements with ? placeholders when incorporating user input. Never concatenate user input into SQL strings.

Raw Statements

Use diesel.createStatement() for DDL or fully trusted queries (no parameters):

const stmt = diesel.createStatement();
stmt.execute(`
  CREATE TABLE IF NOT EXISTS engine."items" (
    id SERIAL PRIMARY KEY,
    data JSONB NOT NULL DEFAULT '{}',
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    deleted BOOLEAN DEFAULT FALSE
  )
`);

Parameter Types

Set parameters on prepared statements using typed setter methods:

Method	Java Type	Example
ps.setString(index, value)	String	ps.setString(1, "hello")
ps.setLong(index, value)	long	ps.setLong(1, 42)
ps.setInt(index, value)	int	ps.setInt(1, 10)
ps.setDouble(index, value)	double	ps.setDouble(1, 3.14)
ps.setBoolean(index, value)	boolean	ps.setBoolean(1, true)
ps.setNull(index, sqlType)	null	ps.setNull(1, 2)
ps.setArray(index, array)	Array	ps.setArray(1, arr)

INFO

Parameter indices are 1-based, not 0-based.

Setting NULL

When setting a parameter to null, you must provide the SQL type code:

ps.setNull(1, 12);  // 12 = java.sql.Types.VARCHAR
ps.setNull(2, 2);   // 2  = java.sql.Types.NUMERIC

Common SQL type codes:

Code	Type
2	NUMERIC
4	INTEGER
12	VARCHAR
16	BOOLEAN
-5	BIGINT
8	DOUBLE

Reading Results

By Column Name

const rs = ps.executeQuery();
while (rs.next()) {
  const id = rs.getLong("id");
  const name = rs.getString("name");
  const active = rs.getBoolean("active");
  const score = rs.getDouble("score");
  const created = rs.getString("created_at");
}

By Column Index

Column indices are 1-based:

const id = rs.getLong(1);
const name = rs.getString(2);

Available Getters

Method	Returns
rs.getString(col)	string
rs.getLong(col)	number (long)
rs.getInt(col)	number (int)
rs.getDouble(col)	number (double)
rs.getBoolean(col)	boolean
rs.getArray(col)	java.sql.Array

JSONB Columns

PostgreSQL's jsonb type is the standard way to store flexible data. Store JSON as a string with ::JSONB cast, read it back with getString() and JSON.parse():

Insert JSONB

const ps = diesel.prepareStatement(
  'INSERT INTO engine."items" (data) VALUES (?::JSONB)'
);
ps.setString(1, JSON.stringify({
  name: "Widget",
  price: 9.99,
  tags: ["sale", "popular"]
}));
ps.execute();

Query JSONB Fields

Use PostgreSQL's JSONB operators in queries:

// Access a JSONB field
const ps = diesel.prepareStatement(
  `SELECT id, data FROM engine."items" WHERE data->>'name' ILIKE ?`
);
ps.setString(1, "%widget%");

// Check if a JSONB field exists
const ps2 = diesel.prepareStatement(
  `SELECT id FROM engine."items" WHERE data ? 'premium'`
);

// Nested JSONB access
const ps3 = diesel.prepareStatement(
  `SELECT id FROM engine."items" WHERE data->'address'->>'city' = ?`
);
ps3.setString(1, "Berlin");

Read JSONB

const rs = ps.executeQuery();
while (rs.next()) {
  const data = JSON.parse(rs.getString("data"));
  console.log(data.name, data.price);
}

PostgreSQL Arrays

Create and Set Arrays

const arr = diesel.createArrayOf("text", ["admin", "editor", "viewer"]);
const ps = diesel.prepareStatement(
  'SELECT id FROM engine."users" WHERE role = ANY(?)'
);
ps.setArray(1, arr);

Read Arrays from Results

const rs = ps.executeQuery();
while (rs.next()) {
  const pgArray = rs.getArray("tags").getArray(); // returns Java array
  // Convert to JS array if needed
  const tags = [];
  for (let i = 0; i < pgArray.length; i++) {
    tags.push(pgArray[i]);
  }
}

Insert with RETURNING

Get the auto-generated ID after an insert:

const ps = diesel.prepareStatement(
  'INSERT INTO engine."items" (data) VALUES (?::JSONB) RETURNING id'
);
ps.setString(1, JSON.stringify({ name: "New Item" }));
const rs = ps.executeQuery();
rs.next();
const newId = rs.getLong("id");

Service Class Pattern

The recommended pattern is a class with static methods per domain entity:

export class ItemService {

  static createTable() {
    diesel.createStatement().execute(`
      CREATE TABLE IF NOT EXISTS engine."items" (
        id SERIAL PRIMARY KEY,
        data JSONB NOT NULL DEFAULT '{}',
        created_at TIMESTAMP DEFAULT NOW(),
        updated_at TIMESTAMP DEFAULT NOW(),
        deleted BOOLEAN DEFAULT FALSE
      )
    `);
  }

  static getItemById(id) {
    const ps = diesel.prepareStatement(
      'SELECT id, data, created_at FROM engine."items" WHERE id = ? AND deleted = false'
    );
    ps.setLong(1, id);
    const rs = ps.executeQuery();
    if (!rs.next()) return null;
    return {
      id: rs.getLong("id"),
      ...JSON.parse(rs.getString("data")),
      created_at: rs.getString("created_at")
    };
  }

  static createItem(data) {
    const ps = diesel.prepareStatement(
      'INSERT INTO engine."items" (data) VALUES (?::JSONB) RETURNING id'
    );
    ps.setString(1, JSON.stringify(data));
    const rs = ps.executeQuery();
    rs.next();
    return rs.getLong("id");
  }

  static updateItem(id, data) {
    const ps = diesel.prepareStatement(
      'UPDATE engine."items" SET data = ?::JSONB, updated_at = NOW() WHERE id = ? AND deleted = false'
    );
    ps.setString(1, JSON.stringify(data));
    ps.setLong(2, id);
    ps.execute();
  }

  static deleteItem(id) {
    const ps = diesel.prepareStatement(
      'UPDATE engine."items" SET deleted = true WHERE id = ?'
    );
    ps.setLong(1, id);
    ps.execute();
  }

  static findItems(searchTerm, page, pageSize) {
    const offset = (page - 1) * pageSize;
    const ps = diesel.prepareStatement(`
      SELECT id, data, created_at FROM engine."items"
      WHERE deleted = false AND data->>'name' ILIKE ?
      ORDER BY id DESC LIMIT ? OFFSET ?
    `);
    ps.setString(1, "%" + searchTerm + "%");
    ps.setInt(2, pageSize);
    ps.setInt(3, offset);
    const rs = ps.executeQuery();

    const results = [];
    while (rs.next()) {
      results.push({
        id: rs.getLong("id"),
        ...JSON.parse(rs.getString("data")),
        created_at: rs.getString("created_at")
      });
    }
    return results;
  }
}

Table Naming

DieselEngine uses the engine schema with quoted table names:

engine."items"      -- correct
engine."UserRoles"  -- correct (case-sensitive)
items               -- wrong (no schema, no quotes)

TIP

Always use engine."TableName" format in your SQL. The quotes preserve case sensitivity and the engine. prefix ensures you're in the right schema.