Patterns

async function getOrders(pool, tenantId, status) {
  const result = await pool.query(
    'SELECT * FROM orders WHERE tenant_id = $1 AND status = $2',
    [tenantId, status]
  );
  return result.rows;
}

async function deleteOrder(pool, tenantId, orderId) {
  const check = await pool.query(
    'SELECT order_id FROM orders WHERE tenant_id = $1 AND order_id = $2',
    [tenantId, orderId]
  );

  if (check.rows.length === 0) {
    throw new Error('Order not found or access denied');
  }

  await pool.query(
    'DELETE FROM orders WHERE tenant_id = $1 AND order_id = $2',
    [tenantId, orderId]
  );
}

async function createLineItem(pool, tenantId, lineItemData) {
  const orderCheck = await pool.query(
    'SELECT order_id FROM orders WHERE tenant_id = $1 AND order_id = $2',
    [tenantId, lineItemData.order_id]
  );

  if (orderCheck.rows.length === 0) {
    throw new Error('Order does not exist');
  }

  await pool.query(
    'INSERT INTO line_items (tenant_id, order_id, product_id, quantity) VALUES ($1, $2, $3, $4)',
    [tenantId, lineItemData.order_id, lineItemData.product_id, lineItemData.quantity]
  );
}

async function deleteProduct(pool, tenantId, productId) {
  const check = await pool.query(
    'SELECT COUNT(*) as count FROM line_items WHERE tenant_id = $1 AND product_id = $2',
    [tenantId, productId]
  );

  if (parseInt(check.rows[0].count) > 0) {
    throw new Error('Product has existing orders');
  }

  await pool.query(
    'DELETE FROM products WHERE tenant_id = $1 AND product_id = $2',
    [tenantId, productId]
  );
}

CREATE TABLE people (
    id BIGINT GENERATED ALWAYS AS IDENTITY (CACHE 70000) PRIMARY KEY,
    name VARCHAR(255),
    address TEXT
);

-- Or with BY DEFAULT, which allows explicit value overrides
CREATE TABLE orders (
    order_number BIGINT GENERATED BY DEFAULT AS IDENTITY (CACHE 70000) PRIMARY KEY,
    tenant_id VARCHAR(255) NOT NULL,
    status VARCHAR(50) NOT NULL
);

INSERT INTO people (name, address) VALUES ('A', 'foo');
INSERT INTO people (name, address) VALUES ('B', 'bar');

-- Use DEFAULT to explicitly request the generated value
INSERT INTO people (id, name, address) VALUES (DEFAULT, 'C', 'baz');

CREATE SEQUENCE order_seq CACHE 1 START 101;

SELECT nextval('order_seq');
-- Returns: 101

INSERT INTO distributors VALUES (nextval('order_seq'), 'nothing');

function toTextArray(values) {
  return values.join(',');
}

function fromTextArray(textValue) {
  return textValue ? textValue.split(',').map(v => v.trim()) : [];
}

function toTextJSON(object) {
  return JSON.stringify(object);
}

function fromTextJSON(textValue) {
  if (!textValue) return null;
  try {
    return JSON.parse(textValue);
  } catch (err) {
    console.warn('Invalid JSON in column:', err.message);
    return null;
  }
}

const categoriesText = toTextArray(['backend', 'api', 'database']);
await pool.query('INSERT INTO projects (project_id, categories) VALUES ($1, $2)', [projectId, categoriesText]);

const configText = toTextJSON({ theme: 'dark', notifications: true });
await pool.query('INSERT INTO user_settings (user_id, preferences) VALUES ($1, $2)', [userId, configText]);

SELECT user_id, preferences::jsonb->>'theme' as theme
FROM user_settings WHERE preferences::jsonb->>'notifications' = 'true';

SELECT project_id, string_to_array(categories, ',') as category_array FROM projects;