Database and Query Builder

EverShop uses PostgreSQL (version 13 or higher) as its database. It provides a type-safe query builder that gives you column name autocompletion and typed return values for all known EverShop tables.

Connection Setup

Environment Variables

Create a .env file in your project root with your database credentials:

DB_HOST=localhost
DB_PORT=5432
DB_USER=postgres
DB_PASSWORD=postgres
DB_NAME=evershop
DB_SSLMODE=disable

SSL Connection

Change DB_SSLMODE to enable SSL. Supported modes: disable, require, prefer, verify-ca, verify-full, no-verify.

For certificate verification, provide:

DB_SSLMODE=verify-full
DB_SSLROOTCERT=/path/to/ca-certificate.pem
DB_SSLCERT=/path/to/client-certificate.pem
DB_SSLKEY=/path/to/client-key.pem

Connection Pool

EverShop maintains a connection pool (max 20 concurrent connections). There are two ways to use the database:

pool — For simple read queries. The pool automatically manages connection acquisition and release.
getConnection() — For transactions. You manage the connection lifecycle and must call commit() or rollback().

Import Paths

EverShop provides two import paths for database operations:

// Typed query builder (recommended) — provides column autocompletion and typed results
import { select, insert, update, del, insertOnUpdate } from '@evershop/evershop/lib/postgres/query';

// Connection and transaction management
import { pool, getConnection } from '@evershop/evershop/lib/postgres';

// Transaction helpers (re-exported from query module)
import { startTransaction, commit, rollback, execute } from '@evershop/evershop/lib/postgres/query';

info

The typed query builder in @evershop/evershop/lib/postgres/query wraps @evershop/postgres-query-builder with TypeScript types for all EverShop tables. When you call .from('order'), your IDE will suggest columns like order_id, status, grand_total, etc.

SELECT Queries

Basic Select

import { select } from '@evershop/evershop/lib/postgres/query';
import { pool } from '@evershop/evershop/lib/postgres';

// Load a single row (returns the row or null)
const order = await select()
  .from('order')
  .where('order_id', '=', orderId)
  .load(pool);

// Load multiple rows (returns an array)
const orders = await select()
  .from('order')
  .where('status', '=', 'processing')
  .execute(pool);

`.load()` vs `.execute()`

.load(connection) — Returns a single row (RowOf<T> | null). Use for fetching one record.
.execute(connection) — Returns an array of rows (RowOf<T>[]). Use for fetching multiple records.

Select Specific Columns

const products = await select('product_id', 'sku', 'price')
  .from('product')
  .where('status', '=', true)
  .execute(pool);

Joins

const query = select()
  .from('cms_page')
  .leftJoin('cms_page_description')
  .on(
    'cms_page.cms_page_id',
    '=',
    'cms_page_description.cms_page_description_cms_page_id'
  );

query.where('status', '=', 1);
query.andWhere('cms_page_description.url_key', '=', 'about-us');

const page = await query.load(pool);

Supported join types: .leftJoin(), .rightJoin(), .innerJoin().

Ordering, Grouping, and Pagination

// `.orderBy()`, `.limit()`, `.groupBy()` and `.having()` are SelectQuery methods.
// `.where()` returns a `Where`, which only carries the condition builders
// (`.andWhere()`, `.orWhere()`, `.and()`, `.or()`) plus `.execute()` / `.load()`.
// Chaining ordering onto a condition throws `orderBy is not a function`.
const query = select().from('product');
query.where('status', '=', true);
query.orderBy('created_at', 'DESC');
query.limit(0, 20); // offset, limit

const products = await query.execute(pool);

const categoryCounts = await select('category_id')
  .from('product_category')
  .groupBy('category_id')
  .execute(pool);

Never Pass an Unbounded ID List to `IN`

danger

Do not fetch a set of ids into Node and feed them back as .where('id', 'IN', ids). Two things go wrong at scale:

The builder converts named bindings by scanning the whole SQL string once per parameter — an O(n²) loop that is fully synchronous. Measured on a real catalog: 0.9s at 25k ids, 12.5s at 100k, ~145s at 341k — during which the Node event loop is frozen and every other request, storefront included, hangs with it.
If the query ever reaches the wire, node-pg's bind message parameter count is a 16-bit integer, so anything above 65,535 parameters fails outright (bind message has N parameter formats but 0 parameters).

Keep the set operation in SQL. Either push it down into a semi-join subquery on the WHERE tree:

const subQuery = baseQuery.clone();
subQuery.removeLimit();
subQuery.removeOrderBy();

const subSql = await subQuery.sql();

baseQuery
  .getWhere()
  .addRaw('AND', `product.product_id IN (${subSql})`, subQuery.getBinding());

or bind the whole list as a single array parameter with execute():

import { execute } from '@evershop/evershop/lib/postgres/query';

const result = await execute(
  connection,
  `SELECT * FROM product WHERE product_id = ANY($1::int[])`,
  [productIds]
);

Both send a bounded number of parameters instead of n. This bit production once already — see catalog/services/ProductCollection.js, which now does its variant de-duplication entirely in SQL.

When you embed a cloned query's SQL, re-key its bindings first — clone() preserves binding keys, so the inner and outer queries would collide during the named-to-positional conversion. ProductCollection.js shows the re-keying loop.

Chaining Conditions

const query = select().from('order');

query.where('status', '=', 'processing');
query.andWhere('grand_total', '>', 100);
query.orWhere('customer_email', '=', 'vip@example.com');

const orders = await query.execute(pool);

Clone a Query

const baseQuery = select().from('product').where('status', '=', true);

// Clone to create variations without modifying the original
const featuredQuery = baseQuery.clone();
featuredQuery.andWhere('visibility', '=', true);

INSERT Queries

import { insert } from '@evershop/evershop/lib/postgres/query';
import { getConnection, startTransaction, commit, rollback } from '@evershop/evershop/lib/postgres/query';

const connection = await getConnection();
await startTransaction(connection);

try {
  const shipment = await insert('shipment')
    .given({
      shipment_order_id: orderId,
      carrier: 'fedex',
      tracking_number: '1234567890'
    })
    .execute(connection);

  await commit(connection);
} catch (e) {
  await rollback(connection);
  throw e;
}

The .given() method accepts an object where keys are column names. Unknown columns are silently ignored.

Set Individual Columns with `.prime()`

const result = await insert('product')
  .given(productData)
  .prime('status', true)        // Override or add a specific column
  .prime('created_at', new Date())
  .execute(connection);

UPDATE Queries

import { update } from '@evershop/evershop/lib/postgres/query';

await update('order')
  .given({ shipping_note: 'Leave at the front desk' })
  .where('order_id', '=', orderId)
  .execute(connection);

warning

Never write order.shipment_status directly. It is a derived rollup — recomputeOrderShipmentStatus() recalculates it from the order's shipment items after any shipment write, so a manual UPDATE is silently overwritten on the next shipment change and leaves the order status (psoMapping) out of sync in the meantime. Change a shipment's status through the service instead:

import { updateShipmentStatus } from '@evershop/evershop/oms/services';

await updateShipmentStatus(shipmentUuid, 'delivered');

The same applies to order.status, which is derived from the payment status and the shipment rollup. See Multi-Shipment and Fulfillment.

DELETE Queries

import { del } from '@evershop/evershop/lib/postgres/query';

await del('order')
  .where('order_id', '=', orderId)
  .execute(connection);

INSERT ON CONFLICT (Upsert)

Insert a row, or update it if a conflict occurs on the specified columns:

import { insertOnUpdate } from '@evershop/evershop/lib/postgres/query';

await insertOnUpdate('setting', ['name'])
  .given({
    name: 'storeName',
    value: 'My Shop'
  })
  .execute(connection);

The first argument is the table name, the second is an array of columns that define the uniqueness constraint. If a row with matching values exists, the other columns are updated.

Raw SQL

For queries that can't be expressed with the query builder, use execute():

import { execute } from '@evershop/evershop/lib/postgres/query';

const result = await execute(
  connection,
  `SELECT p.*, pd.name
   FROM product p
   JOIN product_description pd ON p.product_id = pd.product_description_product_id
   WHERE p.price > $1`,
  [100]
);

Transactions

Wrap multiple operations in a transaction to ensure atomicity:

import { getConnection, startTransaction, commit, rollback } from '@evershop/evershop/lib/postgres/query';

const connection = await getConnection();
await startTransaction(connection);

try {
  await insert('order').given(orderData).execute(connection);
  await insert('order_item').given(itemData).execute(connection);
  await update('product_inventory')
    .given({ qty: newQty })
    .where('product_inventory_product_id', '=', productId)
    .execute(connection);

  await commit(connection);
} catch (e) {
  await rollback(connection);
  throw e;
}

warning

Always use try/catch with rollback() in the catch block. If a transaction is left open (no commit or rollback), the connection leaks and will eventually exhaust the pool.

Type Safety

The query builder provides TypeScript column autocompletion for all known EverShop tables. When you call .from('order'), your IDE suggests only columns from the order table:

// TypeScript knows 'order_id', 'uuid', 'status', 'grand_total', etc.
select().from('order').where('order_id', '=', 1)

// .load() returns OrderRow | null
const order = await select().from('order').where('order_id', '=', 1).load(pool);
// order.grand_total, order.customer_email, etc. are typed

For write operations, numeric string columns (like price, weight) accept both string and number:

// Both work — the WriteRow type widens string fields to string | number
await insert('product').given({ price: '29.99' }).execute(connection);
await insert('product').given({ price: 29.99 }).execute(connection);

Known Tables

The following tables have full type support with column autocompletion. The authoritative list is the TableName union in lib/postgres/query.ts — check there if you suspect this list has drifted.

admin_user, attribute, attribute_group, attribute_group_link, attribute_option, cart, cart_address, cart_item, category, category_description, changeset, changeset_operation, cms_page, cms_page_description, collection, core_shipping_method, core_shipping_method_rate, coupon, customer, customer_address, customer_group, event, migration, order, order_activity, order_address, order_item, payment_transaction, product, product_attribute_value_index, product_category, product_collection, product_custom_option, product_custom_option_value, product_description, product_image, product_inventory, reset_password_token, rollout_plan, session, setting, shipment, shipment_item, shipping_zone, shipping_zone_country, shipping_zone_province, shipping_zone_provider, tax_class, tax_rate, url_rewrite, variant_group, widget_instance, widget_placement

warning

Three tables that older documentation lists no longer exist:

shipping_method and shipping_zone_method were dropped by modules/checkout/migration/Version-1.0.9.ts. Their replacements are core_shipping_method and core_shipping_method_rate, alongside the new shipping_zone_country and shipping_zone_provider tables.
widget was renamed to widget_instance by modules/cms/migration/Version-1.3.0.ts (and widget_id to widget_instance_id), with the new widget_placement table holding one row per route/area placement.

user_token_secret still appears in the TableName union, but the table itself was dropped by modules/auth/migration/Version-1.0.1.js and its column map resolves to never — do not use it. url_redirect is a real table but is not in the union yet, so it works without column autocompletion.

Custom tables created by extensions also work — they just won't have column autocompletion (any string is accepted as a column name).

Query Builder Reference

SELECT Chain Methods

Method	Description
`.select(column, alias?)`	Add a column to the SELECT clause
`.from(table, alias?)`	Set the FROM table (narrows column types)
`.where(column, operator, value)`	Add a WHERE condition
`.andWhere(column, operator, value)`	Add an AND condition
`.orWhere(column, operator, value)`	Add an OR condition
`.leftJoin(table, alias?)`	Add a LEFT JOIN (call `.on()` after)
`.rightJoin(table, alias?)`	Add a RIGHT JOIN
`.innerJoin(table, alias?)`	Add an INNER JOIN
`.orderBy(column, direction?)`	Add ORDER BY (`'ASC'` or `'DESC'`)
`.groupBy(...columns)`	Add GROUP BY
`.having(column, operator, value)`	Add HAVING condition
`.limit(offset, limit)`	Add LIMIT with offset
`.execute(connection)`	Execute and return array of rows
`.load(connection)`	Execute and return single row or null
`.clone()`	Create a copy of the query
`.sql()`	Return the generated SQL string

Write Query Methods

Method	Available On	Description
`.given(data)`	INSERT, UPDATE, INSERT ON UPDATE	Set column values from an object
`.prime(column, value)`	INSERT, UPDATE, INSERT ON UPDATE	Set a single column value
`.where(column, operator, value)`	UPDATE, DELETE	Add WHERE condition
`.execute(connection, releaseConnection?)`	All	Execute the query

Support us

EverShop is an open-source project that relies on community support. If you find our project useful, please consider sponsoring us.

Become a sponsor

Connection Setup​

Environment Variables​

SSL Connection​

Connection Pool​

Import Paths​

SELECT Queries​

Basic Select​

.load() vs .execute()​

Select Specific Columns​

Joins​

Ordering, Grouping, and Pagination​

Never Pass an Unbounded ID List to IN​

Chaining Conditions​

Clone a Query​

INSERT Queries​

Set Individual Columns with .prime()​

UPDATE Queries​

DELETE Queries​

INSERT ON CONFLICT (Upsert)​

Raw SQL​

Transactions​

Type Safety​

Known Tables​

Query Builder Reference​

SELECT Chain Methods​

Write Query Methods​

Support us

Connection Setup

Environment Variables

SSL Connection

Connection Pool

Import Paths

SELECT Queries

Basic Select

`.load()` vs `.execute()`

Select Specific Columns

Joins

Ordering, Grouping, and Pagination

Never Pass an Unbounded ID List to `IN`

Chaining Conditions

Clone a Query

INSERT Queries

Set Individual Columns with `.prime()`

UPDATE Queries

DELETE Queries

INSERT ON CONFLICT (Upsert)

Raw SQL

Transactions

Type Safety

Known Tables

Query Builder Reference

SELECT Chain Methods

Write Query Methods