SQL from Node.js and Java

Running SQL from Node.js or from Java takes the same three steps as from any language: open a connection through a driver, run a statement whose values are placeholders and are handed over separately, then read the rows and close what you opened. In Node.js the driver is a module: node:sqlite is built in, and pg (node-postgres) and mysql2 are npm packages. In Java it is JDBC, the java.sql API that comes with the JDK, plus one driver JAR per database. What changes between them is the spelling: ? or $1, synchronous calls or await, plain objects or a ResultSet. The two production bugs are the same everywhere. N+1 queries run one query for a list and then one more for each item in it. A connection leak is a connection your code took from a pool and never gave back.

Four drivers, one pattern

• node:sqlite runs SQLite inside the Node.js process and answers at once, without await.
• node-postgres sends your SQL unchanged to PostgreSQL with the values beside it, so its placeholders are PostgreSQL’s own numbered ones.
• mysql2’s execute() prepares a statement on its first use and reuses it from a cache on that connection.
• JDBC’s PreparedStatement holds one statement with ? placeholders that you fill with typed setters, numbered from 1.

A prepared statement is parsed and planned once and can then run many times with new values. Each run is still one round trip to the database, and that is where N+1 hurts:

The drivers differ in when that parsing happens. node:sqlite and JDBC compile the statement when you call prepare() or prepareStatement() and keep it until you close it, so a statement prepared before a loop is parsed once. node-postgres sends an unnamed query that PostgreSQL parses on every call; giving the query a name makes the server keep its plan for that connection, which the node-postgres documentation suggests only for queries that run very often. mysql2’s execute() keeps prepared statements in a cache on each connection, 16,000 of them by default.

No driver binds a table or a column name, because a placeholder stands only for a value. Choose such names from a fixed set in your own code; for PostgreSQL the node-postgres documentation suggests escaping them with a library such as pg-format.

The report in Node.js, run

The mart shop is built by a small module, mart.mjs. The report prepares one statement and runs it for two cities, looks up a product by a named parameter, and places three orders inside transactions:

import { DatabaseSync } from 'node:sqlite';
import { buildMart } from './mart.mjs';

const db = new DatabaseSync(':memory:');
buildMart(db);

// Prepared once, run for two cities: the values go to all(), never into the SQL text.
const spend = db.prepare(`
  SELECT c.name, count(DISTINCT o.order_id) AS orders,
         sum(i.quantity * p.price_paise) AS spent_paise
  FROM customers c
  JOIN orders o      ON o.customer_id = c.customer_id
  JOIN order_items i ON i.order_id = o.order_id
  JOIN products p    ON p.product_id = i.product_id
  WHERE c.city = ? AND o.status = ?
  GROUP BY c.customer_id
  ORDER BY spent_paise DESC, c.name`);
for (const city of ['Pune', 'Kochi']) {
  for (const row of spend.all(city, 'delivered')) {
    const rupees = (row.spent_paise / 100).toFixed(2).padStart(8);
    console.log(`${city.padEnd(6)} ${row.name.padEnd(14)} ${row.orders} order(s) ${rupees} rupees`);
  }
}

// get() returns the first row, or undefined; a named parameter takes an object.
const product = db.prepare('SELECT name, price_paise FROM products WHERE product_id = :id');
console.log(JSON.stringify(product.get({ id: 3 })), product.get({ id: 99 }));

// run() says what a change did. An order and its items commit together, or not at all.
const addOrder = db.prepare("INSERT INTO orders VALUES (?, ?, 'placed')");
const addItem = db.prepare('INSERT INTO order_items VALUES (?, ?, ?)');
function placeOrder(orderId, customerId, items) {
  db.exec('BEGIN');
  try {
    const { changes } = addOrder.run(orderId, customerId);
    for (const [productId, quantity] of items) addItem.run(orderId, productId, quantity);
    db.exec('COMMIT');
    return `order ${orderId}: committed (${changes} order row, ${items.length} item rows)`;
  } catch (e) {
    db.exec('ROLLBACK');
    return `order ${orderId}: rolled back (${e.message})`;
  }
}
console.log(placeOrder(111, 2, [[3, 2], [5, 1]]));
console.log(placeOrder(112, 2, [[3, 2], [5, 0]]));
console.log(placeOrder(113, 42, [[1, 1]]));
console.log('in a transaction now:', db.isTransaction);
console.log('orders:', db.prepare('SELECT count(*) AS n FROM orders').get().n);
db.close();

Pune   Farah Qureshi  2 order(s)   754.00 rupees
Pune   Asha Kulkarni  1 order(s)   389.48 rupees
Pune   Lakshmi Iyer   1 order(s)   149.99 rupees
Kochi  Vikram Nair    1 order(s)   215.00 rupees
{"name":"Masala chai 250 g","price_paise":12000} undefined
order 111: committed (1 order row, 2 item rows)
order 112: rolled back (CHECK constraint failed: quantity > 0)
order 113: rolled back (FOREIGN KEY constraint failed)
in a transaction now: false
orders: 11

// The mart practice shop, built in any node:sqlite database: four tables and their rows.
export function buildMart(db) {
  db.exec(`
    CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL);
    CREATE TABLE products (product_id INTEGER PRIMARY KEY, name TEXT NOT NULL, category TEXT NOT NULL,
                           price_paise INTEGER NOT NULL);
    CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers,
                         status TEXT NOT NULL);
    CREATE TABLE order_items (order_id INTEGER NOT NULL REFERENCES orders,
                              product_id INTEGER NOT NULL REFERENCES products,
                              quantity INTEGER NOT NULL CHECK (quantity > 0), PRIMARY KEY (order_id, product_id));
    INSERT INTO customers VALUES (1, 'Asha Kulkarni', 'Pune'), (2, 'Vikram Nair', 'Kochi'), (3, 'Meera Rathore', 'Jaipur'),
      (4, 'Tenzin Dorjee', 'Gangtok'), (5, 'Farah Qureshi', 'Pune'), (6, 'Rohan Das', 'Kochi'),
      (7, 'Lakshmi Iyer', 'Pune'), (8, 'Arjun Mehta', 'Jaipur');
    INSERT INTO products VALUES (1, 'Basmati rice 1 kg', 'Grains', 14999), (2, 'Toor dal 500 g', 'Pulses', 8950),
      (3, 'Masala chai 250 g', 'Beverages', 12000), (4, 'Coconut oil 1 L', 'Oils', 21500),
      (5, 'Ragi flour 1 kg', 'Grains', 6800);
    INSERT INTO orders VALUES (101, 1, 'delivered'), (102, 2, 'delivered'), (103, 1, 'placed'), (104, 3, 'cancelled'),
      (105, 5, 'delivered'), (106, 7, 'delivered'), (107, 5, 'delivered'), (108, 6, 'placed'),
      (109, 8, 'delivered'), (110, 7, 'cancelled');
    INSERT INTO order_items VALUES (101, 1, 2), (101, 2, 1), (102, 4, 1), (103, 3, 2), (103, 5, 1), (104, 2, 3),
      (105, 3, 1), (105, 4, 2), (106, 1, 1), (107, 5, 3), (108, 2, 2), (109, 1, 1), (109, 3, 1), (110, 4, 1);
  `);
}

• all() and get() return plain objects keyed by column name. get() returns undefined when there is no row, which is why the second lookup printed undefined instead of throwing.
• run() reports the change as changes and lastInsertRowid.
• Each order commits as a whole. Order 112’s second item broke the CHECK on quantity and order 113 named a customer who does not exist, so both rolled back, and 11 orders remain: the 10 of mart and order 111.
• node:sqlite enforces foreign keys by default. Python’s sqlite3 and the sqlite3 shell do not until you run PRAGMA foreign_keys = ON, so the same INSERT of order 113 would succeed there.

SQL from Node.js through node-postgres looks the same with await: const { rows } = await client.query(sql, [city, 'delivered']) with $1 and $2 in the text. Every query of a transaction must run on one client, so a transaction takes a client from the pool, runs BEGIN, its statements and COMMIT on it, and releases it in a finally block.

In Java, JDBC turns the same report into a PreparedStatement: conn.prepareStatement(sql), then ps.setString(1, "Pune") and ps.executeQuery(), with a while (rs.next()) loop over the rows. Java’s try-with-resources closes the ResultSet, the statement and the connection in reverse order, even when an exception is thrown. A new JDBC connection is in auto-commit mode, which commits every statement on its own.

JDBC in Java, run: prepared statements, try-with-resources and transactions Pro: opens with a Pro, Premium or Ultimate pass

N+1 queries and leaked connections, reproduced and fixed Pro: opens with a Pro, Premium or Ultimate pass

Interview questions

Key takeaways

• Node.js and Java run SQL through drivers that keep values apart from the SQL text: ? in node:sqlite, mysql2 and JDBC, $1 in node-postgres.
• node:sqlite is synchronous and runs in your process; node-postgres and mysql2 are asynchronous and talk to a server, usually through a pool.
• JDBC numbers parameters from 1, closes with try-with-resources and starts every connection in auto-commit mode.