VQL Queries

VQL (VARIO Query Language) is a SQL-like query language for retrieving data from VARIO ERP. It is the primary way to fetch articles, stock levels, prices, customers, and other business data in shop integrations.

Basic Syntax

VQL follows a familiar SELECT-FROM-WHERE pattern:

SELECT field1, field2, field3
FROM entity.dataSource
WHERE condition = 'value'
ORDER BY field1 ASC
LIMIT 100
OFFSET 0

Executing VQL Queries

Use the ErpApi.vql() static method to run queries:

const ErpApi = require('@vario-software/vario-app-framework-backend/api/ErpApi');

const result = await ErpApi.vql(
  `SELECT id, articleNumber, descriptions.description
   FROM article.queryArticles
   WHERE active = true
   LIMIT 50`,
  {},    // variables (optional)
  50,    // limit
  0      // offset
);

const articles = result.data;

The method returns an object with a data array containing the query results.

Common Queries for Shop Integrations

Articles (Products)

-- Fetch article with descriptions
SELECT
  id,
  articleNumber,
  sku,
  active,
  ean,
  weight,
  descriptions.description,
  descriptions.longDescription,
  descriptions.languageCode.isoAlpha2
FROM article.queryArticles
WHERE id = '${articleId}'

-- Fetch all active articles
SELECT
  id,
  articleNumber,
  active
FROM article.queryArticles
WHERE active = true
ORDER BY articleNumber ASC
LIMIT 100
OFFSET 0

Article Variants

-- Fetch variants for a parent article
SELECT
  id,
  articleNumber,
  parentArticle.id,
  parentArticle.articleNumber,
  variantValues.label,
  variantValues.variantDimension.label
FROM article.queryArticles
WHERE parentArticle.id = '${parentArticleId}'

Stock

-- Fetch stock levels for an article
SELECT
  id,
  articleNumber,
  stock.available,
  stock.physical,
  stock.reserved
FROM article.queryArticles
WHERE id = '${articleId}'

Prices

-- Fetch prices for an article
SELECT
  id,
  articleNumber,
  articlePrices.salesPrice,
  articlePrices.currencyCode,
  articlePrices.priceGroup.label
FROM article.queryArticles
WHERE id = '${articleId}'

Customers (Accounts)

-- Find customer by email
SELECT
  id,
  accountNumber,
  name,
  email
FROM account.queryAccounts
WHERE email = '${email}'
LIMIT 1

Custom Fields (EAV)

Access custom fields stored in EAV groups using the custom. prefix:

-- Fetch article with custom shop fields
SELECT
  id,
  articleNumber,
  custom.myShop.externalProductId,
  custom.myShop.lastSyncDate
FROM article.queryArticles
WHERE custom.myShop.externalProductId NOTNULL

Filtering

Comparison Operators

WHERE field = 'value'          -- Equals
WHERE field != 'value'         -- Not equals
WHERE field > 100              -- Greater than
WHERE field >= 100             -- Greater or equal
WHERE field < 100              -- Less than
WHERE field <= 100             -- Less or equal
WHERE field NOTNULL            -- Not null
WHERE field ISNULL             -- Is null

String Matching

WHERE field LIKE '%search%'    -- Contains
WHERE field LIKE 'prefix%'     -- Starts with
WHERE field LIKE '%suffix'     -- Ends with

Logical Operators

WHERE field1 = 'a' AND field2 = 'b'
WHERE field1 = 'a' OR field2 = 'b'

IN Clause

WHERE field IN ('value1', 'value2', 'value3')

Pagination

Use LIMIT and OFFSET for paginated queries:

async function fetchAllArticles()
{
  const pageSize = 100;
  let offset = 0;
  let allArticles = [];
  let hasMore = true;

  while (hasMore)
  {
    const { data } = await ErpApi.vql(
      `SELECT id, articleNumber
       FROM article.queryArticles
       WHERE active = true
       ORDER BY articleNumber ASC
       LIMIT ${pageSize}
       OFFSET ${offset}`,
      {},
      pageSize,
      offset
    );

    allArticles = allArticles.concat(data);
    hasMore = data.length === pageSize;
    offset += pageSize;
  }

  return allArticles;
}

Ordering

ORDER BY field ASC        -- Ascending (default)
ORDER BY field DESC       -- Descending
ORDER BY field1 ASC, field2 DESC  -- Multiple fields

Performance Tips

• Limit your fields — Only select the fields you need. Selecting fewer fields is faster.

• Use pagination — Never fetch unbounded result sets. Always include LIMIT.

• Filter early — Add WHERE clauses to reduce the result set before the ERP processes it.

• Avoid LIKE with leading wildcards — LIKE '%value' is slower than LIKE 'value%' because it can't use indexes.

For more performance recommendations, see Performance.