Data Management

Data Management

The data management endpoints provide flexible querying, creation, updating, and deletion of records in your Infomaxim application database.

POST/getDataRows

Queries database records with advanced filtering, sorting, and pagination.

Request Body

{
  "table": "products",
  "fields": "id, title, price, category, inStock",
  "whereConditions": {
    "operator": "AND",
    "conditions": [
      {
        "field": "category",
        "operator": "equals",
        "value": "electronics"
      },
      {
        "field": "price",
        "operator": "less_than",
        "value": "1000"
      },
      {
        "field": "inStock",
        "operator": "equals",
        "value": "1"
      }
    ]
  },
  "orderBy": "price DESC",
  "isPaging": true,
  "perPage": 50,
  "page": 1,
  "isLive": true
}

Complete Parameters Reference

• table string required

  Name of the database table to query. This should match a table name configured in your Infomaxim application schema.

  Example: "products", "users", "orders"

• fields string

  Comma-separated list of field names to retrieve from the table. Controls which columns are returned in the result set.

  Options:

  • "*" - Returns all fields from the table (default if not specified)
  • "id, name, email" - Returns only specified fields
  • "COUNT(*) as total" - Aggregate functions are supported

  Best Practice: Always specify only the fields you need to reduce payload size and improve performance.

• whereConditions object

  Structured WHERE clause for filtering records. This is the secure and recommended way to filter data, preventing SQL injection attacks.

  Structure:

  {
    "operator": "AND" | "OR",
    "conditions": [
      {
        "field": "column_name",
        "operator": "equals" | "not_equals" | "greater_than" | "less_than" | 
                    "greater_than_or_equal" | "less_than_or_equal" | 
                    "like" | "not_like" | "in" | "not_in" | "between" | 
                    "is_null" | "is_not_null",
        "value": "search_value" | ["array", "of", "values"]
      }
    ]
  }

  Supported Operators:

  • equals, = - Exact match (e.g., status = 'active')
  • not_equals, != - Not equal (e.g., status != 'deleted')
  • greater_than, > - Greater than (e.g., price > 100)
  • less_than, < - Less than (e.g., quantity < 10)
  • greater_than_or_equal, >= - Greater than or equal
  • less_than_or_equal, <= - Less than or equal
  • like - Pattern matching with % wildcard (e.g., "%wireless%")
  • not_like - Inverse pattern matching
  • in - Match any value in array (e.g., ["active", "pending"])
  • not_in - Match none of values in array
  • between - Range between two values (value must be array: [min, max])
  • is_null - Field is NULL (no value needed)
  • is_not_null - Field is not NULL (no value needed)

  See the Secure WHERE Clause section for detailed examples.

• where string DEPRECATED

  WARNING: This parameter is deprecated and should NOT be used. It accepts raw SQL WHERE clause strings which are vulnerable to SQL injection attacks. Use whereConditions instead.

  If you have existing code using this parameter, migrate to whereConditions immediately for security.

• orderBy string

  Specifies the field(s) to sort results by and the sort direction.

  Format: "field_name [ASC|DESC]"

  Examples:

  • "price DESC" - Sort by price, highest first
  • "createdAt ASC" - Sort by creation date, oldest first
  • "lastName ASC, firstName ASC" - Multi-field sorting
  • "RAND()" - Random order (for SQL Server: "NEWID()")

  Default: If not specified, results are returned in the order they appear in the database (typically by primary key).

• isPaging boolean

  Enables pagination of results. When set to true, limits the number of results returned and provides pagination metadata.

  Values:

  • true - Enable pagination (use with page and perPage)
  • false - Disable pagination, return all matching records (default)

  Important: For large datasets, always use pagination to avoid performance issues and timeout errors.

• perPage number

  Number of records to return per page when pagination is enabled.

  Range: 1 to 100

  Default: 50

  Recommendation: Use smaller values (10-20) for mobile devices, larger values (50-100) for desktop applications to balance performance and user experience.

• page number

  The page number to retrieve when pagination is enabled. Pages are 1-indexed (first page is 1, not 0).

  Default: 1 (first page)

  Example: With perPage: 50, page 1 returns records 1-50, page 2 returns records 51-100, etc.

• distinct boolean

  Returns only unique/distinct records, eliminating duplicates.

  Values:

  • true - Return only distinct records
  • false - Allow duplicate records (default)

  Example: Get unique categories: fields: "category", distinct: true

• record boolean

  Returns a single object instead of an array when set to true. Useful when you expect exactly one result.

  Values:

  • true - Return single object (first record only)
  • false - Return array of objects (default)

  Example: Get single product by ID: whereConditions: {...}, record: true

• rows number

  Maximum number of rows to return from the query. Different from pagination - this limits the total result set.

  Default: -1 (no limit)

  Example: Get top 10 records: rows: 10, orderBy: "price DESC"

• relatedTable string

  Name of a related table to join with the main table. Used for fetching related data in a single query.

  Example: Get products with their categories: table: "products", relatedTable: "categories"

  Note: Works in conjunction with relatedTable_id parameter.

• relatedTable_id number or string

  ID of the related record or the foreign key field name for relationship joins.

  Example: relatedTable: "categories", relatedTable_id: 5

  Note: Can accept encrypted IDs when using secure mode.

• isReverseRelated boolean

  Reverses the direction of the relationship join.

  Values:

  • true - Reverse the join direction
  • false - Normal join direction (default)
• secureId string

  Encrypted record ID for secure single-record retrieval. When provided, automatically adds a WHERE clause for this ID.

  Example: table: "products", secureId: "encrypted_id_string"

  Note: The ID is decrypted server-side and used safely in the query.

• isLive boolean

  Returns only live/published records from versioned tables.

  Values:

  • true - Return only published/live data
  • false - Return all versions (default)

  Note: Use this for production data queries to ensure only published content is shown.

• isPublished boolean

  Alias for isLive. Returns only published records.

• isStage boolean

  Returns staging/preview versions of records instead of published versions.

  Values:

  • true - Return staging data
  • false - Return published data (default)
• isAppGrid boolean

  Returns raw data directly from the table, bypassing the versioning system entirely.

  Values:

  • true - Return raw, unversioned data
  • false - Use versioning system (default)
• fullText boolean

  Enables full-text search mode using SQL Server's full-text search capabilities.

  Values:

  • true - Enable full-text search (use with s_keyword)
  • false - Standard search (default)
• s_keyword string

  Search keyword(s) for full-text search. Used when fullText is enabled.

  Example: s_keyword: "wireless headphones", fullText: true

  Note: Automatically filters out common noise words and uses SQL Server full-text search.

• debugOn boolean

  Includes the generated SQL query and parameters in the response for debugging purposes.

  Values:

  • true - Include debug information in response
  • false - Normal response (default)

  Warning: Only use in development environments. Do not enable in production as it exposes query structure.

Advanced Query Examples

{
  "table": "products",
  "fields": "id, name, price, category, inStock",
  "whereConditions": {
    "operator": "AND",
    "conditions": [
      {
        "field": "category",
        "operator": "in",
        "value": ["electronics", "computers"]
      },
      {
        "field": "price",
        "operator": "between",
        "value": ["100", "1000"]
      },
      {
        "field": "inStock",
        "operator": "greater_than",
        "value": "0"
      },
      {
        "field": "name",
        "operator": "like",
        "value": "%wireless%"
      }
    ]
  },
  "orderBy": "price ASC",
  "isPaging": true,
  "perPage": 25,
  "page": 1
}

{
  "table": "products",
  "fields": "id, title, price, description",
  "s_keyword": "wireless bluetooth headphones",
  "fullText": true,
  "orderBy": "rank DESC",
  "isPaging": true,
  "perPage": 50
}

{
  "table": "products",
  "fields": "products.id, products.title, products.price, categories.name as categoryName",
  "relatedTable": "categories",
  "relatedTable_id": "category_id",
  "whereConditions": {
    "operator": "AND",
    "conditions": [
      {
        "field": "products.status",
        "operator": "equals",
        "value": "active"
      }
    ]
  },
  "isLive": true,
  "orderBy": "products.title ASC"
}

{
  "table": "products",
  "fields": "id, title, price, description, images",
  "whereConditions": {
    "operator": "AND",
    "conditions": [
      {
        "field": "id",
        "operator": "equals",
        "value": "123"
      }
    ]
  },
  "record": true,
  "isLive": true
}

{
  "table": "products",
  "fields": "category",
  "distinct": true,
  "orderBy": "category ASC",
  "isLive": true
}

Success Response (200 OK)

{
  "status": "Success",
  "data": {
    "rows": [
      {
        "id": 101,
        "title": "Wireless Headphones",
        "price": 299.99,
        "category": "electronics",
        "inStock": 1
      },
      {
        "id": 102,
        "title": "Smart Watch",
        "price": 399.99,
        "category": "electronics",
        "inStock": 1
      }
    ],
    "pagination": {
      "total": 45,
      "page": 1,
      "perPage": 50,
      "totalPages": 1
    }
  }
}

Example Usage

// JavaScript Example
const response = await fetch('http://localhost:3001/getDataRows', {
  method: 'POST',
  headers: {
    'Authorization': 'Bearer ' + accessToken,
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({
    table: 'products',
    fields: 'id, title, price',
    whereConditions: {
      operator: 'AND',
      conditions: [
        { field: 'category', operator: 'equals', value: 'electronics' },
        { field: 'price', operator: 'less_than', value: '1000' }
      ]
    },
    orderBy: 'price DESC',
    isPaging: true,
    perPage: 50,
    page: 1,
    isLive: true
  })
});

const data = await response.json();
console.log(data.data.rows);

POST/saveRow

Creates a new record or updates an existing record in the database.

Request Body (Create New Record)

{
  "table": "products",
  "data": {
    "title": "New Product",
    "price": 499.99,
    "category": "electronics",
    "description": "A brand new product",
    "inStock": 1
  }
}

Request Body (Update Existing Record)

{
  "table": "products",
  "id": 101,
  "data": {
    "title": "Updated Product Name",
    "price": 449.99,
    "inStock": 0
  }
}

Success Response (200 OK)

{
  "status": "Success",
  "data": {
    "id": 103,
    "message": "Record saved successfully"
  }
}

POST/deleteRow

Deletes a record from the database.

Request Body

{
  "table": "products",
  "id": 103
}

Success Response (200 OK)

{
  "status": "Success",
  "message": "Record deleted successfully"
}

POST/getApp/:domain

Retrieves application configuration by domain name.

URL Parameters

• domain string required

  Domain name of the application (e.g., "example.com").

Success Response (200 OK)

{
  "status": "Success",
  "data": {
    "appID": 1,
    "name": "My Application",
    "domain": "example.com",
    "settings": {
      /* Application-specific settings */
    }
  }
}

Secure WHERE Clause

The Secure WHERE Clause system provides SQL injection-safe filtering for data queries using a structured JSON format.

Structure

{
  "whereConditions": {
    "operator": "AND|OR",
    "conditions": [
      {
        "field": "column_name",
        "operator": "equals|not_equals|greater_than|less_than|greater_than_or_equal|less_than_or_equal|like|not_like|in|not_in|between|is_null|is_not_null",
        "value": "search_value"
      }
    ]
  }
}

Supported Operators

Examples

Simple Equality

{
  "whereConditions": {
    "operator": "AND",
    "conditions": [
      {
        "field": "status",
        "operator": "equals",
        "value": "active"
      }
    ]
  }
}

Multiple Conditions with AND

{
  "whereConditions": {
    "operator": "AND",
    "conditions": [
      {
        "field": "category",
        "operator": "equals",
        "value": "electronics"
      },
      {
        "field": "price",
        "operator": "less_than",
        "value": "500"
      },
      {
        "field": "inStock",
        "operator": "equals",
        "value": "1"
      }
    ]
  }
}

IN Operator with Array

{
  "whereConditions": {
    "operator": "AND",
    "conditions": [
      {
        "field": "category",
        "operator": "in",
        "value": ["electronics", "computers", "phones"]
      }
    ]
  }
}

LIKE Pattern Matching

{
  "whereConditions": {
    "operator": "AND",
    "conditions": [
      {
        "field": "title",
        "operator": "like",
        "value": "%wireless%"
      }
    ]
  }
}

BETWEEN Range

{
  "whereConditions": {
    "operator": "AND",
    "conditions": [
      {
        "field": "price",
        "operator": "between",
        "value": ["100", "500"]
      }
    ]
  }
}

Security Features

• Field Name Validation: Field names are validated against a safe pattern to prevent SQL injection
• Operator Whitelist: Only predefined, safe operators are allowed
• Parameterized Queries: All values are passed as SQL parameters, never concatenated
• Type Validation: Values are validated and sanitized based on the operator