Database Utility Functions

Mindbricks automatically generates a comprehensive set of database utility functions for each data object in your service. These functions are available through the dbLayer module and provide a safe, consistent interface for data operations that automatically handles:

• Database operations (create, read, update, delete)
• Elasticsearch indexing and synchronization
• Kafka event publishing on data changes
• Soft delete handling
• Data normalization via getData()

Always use these utility functions instead of directly accessing models to ensure data consistency across your system.

Important: All create, update, and delete functions accept an optional context parameter as the last argument. When calling these functions from Business API actions, pass this as the context to automatically include session and requestId information in events. See the Context Parameter section for details.

Importing dbLayer

// Using destructuring (recommended)
const { getUserById, createUser, updateUserById } = require("dbLayer");

// Or using the full module
const DB = require("dbLayer");

All functions follow the naming pattern: <operation>${ModelName}, where ModelName is the PascalCase name of your data object. For example, if your data object is named order, the functions will be prefixed with Order (e.g., createOrder, getOrderById).

Create Functions

create${ModelName}(data, context)

Creates a single record. If an id is provided and the record already exists, it will update the existing record instead.

Signature:

create${ModelName}(data: Object, context?: Object): Promise<Object>

Parameters:

• data (Object) — The data object to create. Must contain all required fields as defined in your data object schema.
• context (Object, optional) — Context object containing session and requestId for event tracking. When called from Business API actions, pass this to provide session and request context.

Returns:

• Object — The created/updated record data (normalized via getData())

Behavior:

• Automatically generates a UUID for id if not provided
• Generates codename automatically if the data object has codename support and a base name field is provided
• If id exists in database, updates the existing record (useful for upsert scenarios)
• Automatically indexes the record in Elasticsearch
• Publishes Kafka events for the creation/update (includes session and requestId from context if provided)

Example:

const { createOrder } = require("dbLayer");

// Create a new order (without context)
const order = await createOrder({
  userId: "user-123",
  totalAmount: 5000,
  currency: "USD",
  status: "pending"
});

// Create with context (from Business API action)
const order = await createOrder({
  userId: "user-123",
  totalAmount: 5000,
  currency: "USD",
  status: "pending"
}, this); // Pass 'this' to include session and requestId

// If order with this ID exists, it will update instead
const updated = await createOrder({
  id: order.id,
  status: "paid"
}, this);

createBulk${ModelName}(dataList, context)

Creates multiple records in bulk. Uses optimized bulk operations for better performance.

Signature:

createBulk${ModelName}(dataList: Array<Object>, context?: Object): Promise<Array<Object>>

Parameters:

• dataList (Array) — Array of data objects to create. Must be a non-empty array.
• context (Object, optional) — Context object containing session and requestId for event tracking. When called from Business API actions, pass this.

Returns:

• Array — Array of created/updated record data objects

Behavior:

• Validates that dataList is a non-empty array
• Uses bulk database operations for optimal performance
• Handles existing records (updates) and new records (creates) separately
• Each item follows the same rules as create${ModelName}
• Automatically indexes all records in Elasticsearch (in parallel)
• Publishes Kafka events for each creation/update (includes session and requestId from context if provided)

Example:

const { createBulkOrder } = require("dbLayer");

// Without context
const orders = await createBulkOrder([
  { userId: "user-1", totalAmount: 1000, status: "pending" },
  { userId: "user-2", totalAmount: 2000, status: "pending" },
  { userId: "user-3", totalAmount: 3000, status: "pending" }
]);

// With context (from Business API action)
const orders = await createBulkOrder([
  { userId: "user-1", totalAmount: 1000, status: "pending" },
  { userId: "user-2", totalAmount: 2000, status: "pending" },
  { userId: "user-3", totalAmount: 3000, status: "pending" }
], this); // Pass 'this' to include session and requestId

Read Functions

get${ModelName}ById(id)

Retrieves a record by its ID. Supports both single ID and array of IDs.

Signature:

get${ModelName}ById(id: string | Array<string>): Promise<Object | Array<Object> | null>

Parameters:

• id (String|Array) — Single ID string or array of ID strings

Returns:

• Object — Single record if id is a string
• Array — Array of records if id is an array
• null — If single ID not found (returns empty array for array input if none found)

Behavior:

• Respects soft delete settings (only returns active records if soft delete is enabled)
• Returns normalized data via getData()
• Does not include related/joined data (use get${ModelName}AggById for that)

Example:

const { getOrderById } = require("dbLayer");

// Get single order
const order = await getOrderById("order-123");

// Get multiple orders
const orders = await getOrderById(["order-123", "order-456", "order-789"]);

get${ModelName}AggById(id)

Retrieves a record with aggregated/joined data from related services via CQRS joins.

Signature:

get${ModelName}AggById(id: string | Array<string>): Promise<Object | Array<Object> | null>

Parameters:

• id (String|Array) — Single ID string or array of ID strings

Returns:

• Object — Single record with joined relations if id is a string
• Array — Array of records with joined relations if id is an array
• null — If single ID not found

Behavior:

• Same as get${ModelName}ById but includes related data from other services
• Uses CQRS join mechanism to fetch related data
• Respects soft delete settings
• Returns normalized data with populated relations

Example:

const { getOrderAggById } = require("dbLayer");

// Get order with user and items populated
const order = await getOrderAggById("order-123");
// order.user contains user data
// order.items contains related items

get${ModelName}ListByQuery(query)

Retrieves multiple records matching a query.

Signature:

get${ModelName}ListByQuery(query: Object): Promise<Array<Object>>

Parameters:

• query (Object) — Sequelize or MongoDB query object (format depends on your selected database type)

Returns:

• Array — Array of matching records (empty array if none found)

Behavior:

• Respects soft delete settings (automatically filters isActive: true if soft delete is enabled)
• Returns normalized data via getData()
• Returns empty array if no matches found (does not throw error)

Query Format Examples:

Sequelize (PostgreSQL, MySQL, etc.):

const { Op } = require("sequelize");
const { getOrderListByQuery } = require("dbLayer");

// Simple query
const orders = await getOrderListByQuery({ status: "paid" });

// Complex query
const orders = await getOrderListByQuery({
  status: "paid",
  totalAmount: { [Op.gte]: 1000 },
  createdAt: { [Op.between]: [startDate, endDate] }
});

MongoDB:

const { getOrderListByQuery } = require("dbLayer");

// Simple query
const orders = await getOrderListByQuery({ status: "paid" });

// Complex query
const orders = await getOrderListByQuery({
  status: "paid",
  totalAmount: { $gte: 1000 },
  createdAt: { $gte: startDate, $lte: endDate }
});

get${ModelName}ByQuery(query)

Retrieves a single record matching a query. Returns the most recently created record if multiple matches exist.

Signature:

get${ModelName}ByQuery(query: Object): Promise<Object | null>

Parameters:

• query (Object) — Sequelize or MongoDB query object

Returns:

• Object — First matching record (ordered by createdAt DESC) or null if not found

Behavior:

• Respects soft delete settings
• Orders results by createdAt DESC to get the most recent match
• Returns null if no match found (does not throw error)
• Returns normalized data via getData()

Example:

const { getOrderByQuery } = require("dbLayer");

// Get the most recent order for a user
const latestOrder = await getOrderByQuery({ userId: "user-123" });

get${ModelName}StatsByQuery(query, stats)

Calculates statistics (count, sum, avg, min, max) on records matching a query.

Signature:

get${ModelName}StatsByQuery(
  query: Object,
  stats: string | Array<string>
): Promise<number | Object>

Parameters:

• query (Object) — Sequelize or MongoDB query object
• stats (String|Array) — Stat operation(s) to perform:
  • "count" — Count of matching records
  • "sum(fieldName)" — Sum of a numeric field
  • "avg(fieldName)" — Average of a numeric field
  • "min(fieldName)" — Minimum value of a field
  • "max(fieldName)" — Maximum value of a field

Returns:

• Number — If single stat is requested
• Object — If multiple stats are requested, with keys like "count", "sum-fieldName", "avg-fieldName", etc.

Behavior:

• Respects soft delete settings
• Executes all stats in parallel for performance
• Returns single value if one stat requested, object with labeled keys if multiple

Example:

const { getOrderStatsByQuery } = require("dbLayer");

// Single stat
const count = await getOrderStatsByQuery(
  { status: "paid" },
  "count"
);
// Returns: 42

// Multiple stats
const stats = await getOrderStatsByQuery(
  { status: "paid" },
  ["count", "sum(totalAmount)", "avg(totalAmount)", "min(totalAmount)", "max(totalAmount)"]
);
// Returns: {
//   count: 42,
//   "sum-totalAmount": 125000,
//   "avg-totalAmount": 2976.19,
//   "min-totalAmount": 100,
//   "max-totalAmount": 10000
// }

getIdListOf${ModelName}ByField(fieldName, fieldValue, isArray)

Gets a list of IDs for records matching a specific field value. Useful for quick lookups and building relationships.

Signature:

getIdListOf${ModelName}ByField(
  fieldName: string,
  fieldValue: any,
  isArray?: boolean
): Promise<Array<string>>

Parameters:

• fieldName (String) — Name of the field to match
• fieldValue (Any) — Value to match against
• isArray (Boolean, optional) — If true, uses array containment check (for array-type fields)

Returns:

• Array — Array of record IDs matching the criteria

Behavior:

• Respects soft delete settings
• If isArray is true, checks if fieldValue is contained in the array field
• Throws NotFoundError if no records match (unlike list functions that return empty array)
• Only returns IDs, not full records (for performance)

Example:

const { getIdListOfOrderByField } = require("dbLayer");

// Get all order IDs for a user
const orderIds = await getIdListOfOrderByField("userId", "user-123");

// Get all order IDs that contain a specific tag (array field)
const taggedOrderIds = await getIdListOfOrderByField("tags", "urgent", true);

Update Functions

update${ModelName}ById(id, dataClause, context)

Updates a record by its ID.

Signature:

update${ModelName}ById(
  id: string | Object,
  dataClause?: Object,
  context?: Object
): Promise<Object>

Parameters:

• id (String|Object) — Record ID string, or object with id property (if object, dataClause can be omitted and data included in the object)
• dataClause (Object, optional) — Fields to update (only required if id is a string)
• context (Object, optional) — Context object containing session and requestId for event tracking. When called from Business API actions, pass this.

Returns:

• Object — Updated record data (normalized via getData())

Behavior:

• Throws NotFoundError if record doesn't exist
• Respects soft delete settings (only updates active records)
• Automatically indexes updated record in Elasticsearch
• Publishes Kafka events for the update (includes session and requestId from context if provided)
• If id is an object, extracts id and uses remaining properties as dataClause

Example:

const { updateOrderById } = require("dbLayer");

// Standard usage (without context)
const updated = await updateOrderById("order-123", {
  status: "shipped",
  shippedAt: new Date()
});

// With context (from Business API action)
const updated = await updateOrderById("order-123", {
  status: "shipped",
  shippedAt: new Date()
}, this); // Pass 'this' to include session and requestId

// Using object form
const updated = await updateOrderById({
  id: "order-123",
  status: "shipped",
  shippedAt: new Date()
}, null, this);

update${ModelName}ByIdList(idList, dataClause, context)

Updates multiple records by a list of IDs. All records receive the same update.

Signature:

update${ModelName}ByIdList(
  idList: Array<string>,
  dataClause: Object,
  context?: Object
): Promise<Array<string>>

Parameters:

• idList (Array) — Array of record IDs to update
• dataClause (Object) — Fields to update (applied to all records)
• context (Object, optional) — Context object containing session and requestId for event tracking. When called from Business API actions, pass this.

Returns:

• Array — Array of updated record IDs

Behavior:

• Updates all records in the list with the same data
• Respects soft delete settings (only updates active records)
• Automatically indexes all updated records in Elasticsearch
• Publishes Kafka events for each update (includes session and requestId from context if provided)
• Returns IDs (not full records) for performance

Example:

const { updateOrderByIdList } = require("dbLayer");

// Mark multiple orders as shipped (without context)
const updatedIds = await updateOrderByIdList(
  ["order-1", "order-2", "order-3"],
  {
    status: "shipped",
    shippedAt: new Date()
  }
);

// With context (from Business API action)
const updatedIds = await updateOrderByIdList(
  ["order-1", "order-2", "order-3"],
  {
    status: "shipped",
    shippedAt: new Date()
  },
  this // Pass 'this' to include session and requestId
);

update${ModelName}ByQuery(query, dataClause, context)

Updates all records matching a query.

Signature:

update${ModelName}ByQuery(
  query: Object,
  dataClause: Object,
  context?: Object
): Promise<Array<Object>>

Parameters:

• query (Object) — Sequelize or MongoDB query object
• dataClause (Object) — Fields to update
• context (Object, optional) — Context object containing session and requestId for event tracking. When called from Business API actions, pass this.

Returns:

• Array — Array of updated record data (normalized via getData())

Behavior:

• Updates all records matching the query
• Respects soft delete settings (only updates active records)
• Automatically indexes all updated records in Elasticsearch
• Publishes Kafka events for each update (includes session and requestId from context if provided)
• Returns empty array if no records match

Example:

const { Op } = require("sequelize");
const { updateOrderByQuery } = require("dbLayer");

// Mark all pending orders older than 7 days as expired (without context)
const expired = await updateOrderByQuery(
  {
    status: "pending",
    createdAt: { [Op.lt]: sevenDaysAgo }
  },
  {
    status: "expired",
    expiredAt: new Date()
  }
);

// With context (from Business API action)
const expired = await updateOrderByQuery(
  {
    status: "pending",
    createdAt: { [Op.lt]: sevenDaysAgo }
  },
  {
    status: "expired",
    expiredAt: new Date()
  },
  this // Pass 'this' to include session and requestId
);

Delete Functions

delete${ModelName}ById(id, context)

Deletes a record by its ID. Uses soft delete if enabled, otherwise hard delete.

Signature:

delete${ModelName}ById(id: string | Object, context?: Object): Promise<Object>

Parameters:

• id (String|Object) — Record ID string or object with id property
• context (Object, optional) — Context object containing session and requestId for event tracking. When called from Business API actions, pass this.

Returns:

• Object — Deleted record data (normalized via getData())

Behavior:

• Throws NotFoundError if record doesn't exist
• If soft delete is enabled: sets isActive: false and _archivedAt: timestamp
• If soft delete is disabled: permanently deletes from database
• Automatically removes record from Elasticsearch
• Publishes Kafka events for the deletion (includes session and requestId from context if provided)

Example:

const { deleteOrderById } = require("dbLayer");

// Delete an order (without context)
const deleted = await deleteOrderById("order-123");

// With context (from Business API action)
const deleted = await deleteOrderById("order-123", this); // Pass 'this' to include session and requestId

delete${ModelName}ByQuery(query, context)

Deletes all records matching a query. Uses soft delete if enabled, otherwise hard delete.

Signature:

delete${ModelName}ByQuery(query: Object, context?: Object): Promise<Array<Object>>

Parameters:

• query (Object) — Sequelize or MongoDB query object
• context (Object, optional) — Context object containing session and requestId for event tracking. When called from Business API actions, pass this.

Returns:

• Array — Array of deleted record data (normalized via getData())

Behavior:

• Deletes all records matching the query
• If soft delete is enabled: sets isActive: false and _archivedAt: timestamp for all matching records
• If soft delete is disabled: permanently deletes all matching records
• Automatically removes all records from Elasticsearch
• Publishes Kafka events for each deletion (includes session and requestId from context if provided)
• Returns empty array if no records match

Example:

const { Op } = require("sequelize");
const { deleteOrderByQuery } = require("dbLayer");

// Delete all expired orders (without context)
const deleted = await deleteOrderByQuery({
  status: "expired",
  expiredAt: { [Op.lt]: oneYearAgo }
});

// With context (from Business API action)
const deleted = await deleteOrderByQuery({
  status: "expired",
  expiredAt: { [Op.lt]: oneYearAgo }
}, this); // Pass 'this' to include session and requestId

Query Format Reference

All functions that accept a query parameter use the same format as your selected database type.

Sequelize (PostgreSQL, MySQL, SQLite, etc.)

Use Sequelize query syntax with operators from Op:

const { Op } = require("sequelize");

// Simple equality
{ status: "active" }

// Comparison operators
{ age: { [Op.gte]: 18 } }
{ price: { [Op.between]: [100, 500] } }
{ createdAt: { [Op.lt]: new Date() } }

// Array operators
{ id: { [Op.in]: ["id1", "id2", "id3"] } }
{ tags: { [Op.contains]: ["urgent"] } }

// Logical operators
{
  [Op.and]: [
    { status: "active" },
    { age: { [Op.gte]: 18 } }
  ]
}
{
  [Op.or]: [
    { status: "pending" },
    { status: "processing" }
  ]
}

MongoDB

Use MongoDB query syntax:

// Simple equality
{ status: "active" }

// Comparison operators
{ age: { $gte: 18 } }
{ price: { $gte: 100, $lte: 500 } }
{ createdAt: { $lt: new Date() } }

// Array operators
{ id: { $in: ["id1", "id2", "id3"] } }
{ tags: "urgent" }

// Logical operators
{
  $and: [
    { status: "active" },
    { age: { $gte: 18 } }
  ]
}
{
  $or: [
    { status: "pending" },
    { status: "processing" }
  ]
}

Context Parameter

All create, update, and delete functions accept an optional context parameter as the last argument. The context object should contain:

• session (Object, optional) — Current user session data
• requestId (String, optional) — Unique identifier for the current request

When to use context:

• In Business API actions: Always pass this as the context argument. This automatically provides this.session and this.requestId to the utility functions.
• In library functions: If you have access to the Business API context, pass it through. Otherwise, you can omit it or pass null.

Benefits of providing context:

• Kafka events will include session and requestId information
• Better traceability and debugging
• Enables session-aware event processing in downstream services

Example from Business API action:

// Inside a Business API action method
async myAction() {
  const { createOrder, updateOrderById } = require("dbLayer");
  
  // Pass 'this' to include session and requestId
  const order = await createOrder({
    userId: this.session.userId,
    totalAmount: 1000
  }, this);
  
  await updateOrderById(order.id, { status: "paid" }, this);
}

Best Practices

✅ Do: Use dbLayer Functions with Context

const { getOrderById, updateOrderById } = require("dbLayer");

// Good: Uses utility function with context (from Business API action)
const order = await getOrderById(orderId);
await updateOrderById(orderId, { status: "paid" }, this); // Pass 'this' for context

❌ Don't: Access Models Directly

const { Order } = require("models");

// Bad: Bypasses Elasticsearch sync and Kafka events
const order = await Order.findByPk(orderId);
await order.update({ status: "paid" });

Why?

Direct model access:

• ❌ Bypasses Elasticsearch synchronization
• ❌ Doesn't publish Kafka events
• ❌ May break data consistency
• ❌ Doesn't respect soft delete settings automatically
• ❌ Doesn't normalize data via getData()

Complete Example

Here's a complete example using multiple utility functions in a library function:

const { Op } = require("sequelize");
const {
  getOrderListByQuery,
  getOrderStatsByQuery,
  updateOrderByIdList,
  getIdListOfOrderByField
} = require("dbLayer");

// Example 1: Library function called from Business API action
// If called from a Business API action, receive context and pass it through
module.exports = async function processUserOrders(userId, context = null) {
  // Get all active orders for user
  const orders = await getOrderListByQuery({
    userId: userId,
    status: { [Op.in]: ["pending", "processing"] }
  });

  if (orders.length === 0) {
    return { message: "No orders to process" };
  }

  // Get statistics
  const stats = await getOrderStatsByQuery(
    { userId: userId },
    ["count", "sum(totalAmount)"]
  );

  // Update all pending orders to processing (with context if provided)
  const orderIds = orders.map(o => o.id);
  await updateOrderByIdList(orderIds, {
    status: "processing",
    processedAt: new Date()
  }, context); // Pass context through

  // Get order IDs for a specific status
  const paidOrderIds = await getIdListOfOrderByField("status", "paid");

  return {
    processed: orders.length,
    totalAmount: stats["sum-totalAmount"],
    paidOrders: paidOrderIds.length
  };
};

// Example 2: Called from Business API action
// In a Business API action, you would call it like:
// LIB.processUserOrders(this.userId, this)

Summary

All functions automatically handle Elasticsearch indexing, Kafka event publishing, and soft delete logic. Always prefer these utility functions over direct model access to maintain data consistency across your system.