Create Custom SQL APIs in Drupal

The Create SQL API feature allows administrators to expose Drupal database records through secure REST API endpoints without writing any code. Using the visual Query Builder, you define the source table, joins, filter conditions, calculated fields, grouping, sorting, and output options — the module builds and executes the SQL query for you and returns the results as JSON.

1. Basic Configuration

• Navigate to the Create SQL API tab of the module and click the + Create SQL API button.

2. Query Builder

The Query Builder allows you to create SQL-based API endpoints using a visual interface, eliminating the need to write SQL queries manually. By configuring tables, joins, conditions, and output settings, you define exactly what data the API returns.

Every section below supports multiple rows:

• Click + to add a new row.
• Click − to remove a row.
• Drag rows using the drag handle (Order column) to reorder them.

2.1 Select From Table

• Choose the database table that is the primary source of data for the API (the SQL FROM table). All other Query Builder sections build on this selection.

2.2 Joins

• The Joins section combines data from multiple tables into a single API response.

2.3 Conditions

•  Conditions filter the records before they are returned by the API.

• Dynamic values from the request: Set the Value to a placeholder starting with a colon (e.g., :status) and the module resolves it at request time — from the query string for GET APIs (?status=1) or from the JSON body for POST APIs ({"status": 1}). If the request does not supply the parameter, the literal value is used as-is.

2.4 Expressions

• Expressions add calculated or aggregated values to the API response.

• Only the following SQL functions are allowed inside expressions:
  • COUNT, SUM, AVG, MIN, MAX, CONCAT, CONCAT_WS, GROUP_CONCAT, COALESCE, IFNULL, NULLIF, IF, NOW, DATE, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DATE_FORMAT, STR_TO_DATE, DATEDIFF, TIMEDIFF, LENGTH, CHAR_LENGTH, UPPER, LOWER, TRIM, LTRIM, RTRIM, REPLACE, SUBSTRING, SUBSTR, LEFT, RIGHT, ROUND, FLOOR, CEIL, CEILING, ABS, MOD, CAST, CONVERT, DISTINCT.
• For security, statements and unsafe syntax are blocked — for example ;, --, /* */, UNION, SELECT, INSERT, UPDATE, DELETE, DROP, CREATE, ALTER, TRUNCATE.

2.5 Columns

• The Columns section defines which fields are returned in the API response.

• Only the selected columns (plus expressions) appear in the response.
• If no Columns and no Expressions are configured, all columns of the base table are returned. With joins, all columns of each joined table are also included, keyed as <join_table>__<column> to avoid name clashes.

2.6 Group By

• Group By combines records that share the same value, typically together with an aggregate expression (COUNT, SUM, …).
  • Add one row per grouping column (e.g., node.type).
  • A column or expression alias is also accepted.
  • Use a having condition to filter the resulting groups.

2.7 Order By

• Order By controls the sorting of the returned records.

• Multiple rows create a multi-level sort in the order listed.

2.8 Options

• Once done, click the Save Query button (or Update Query when editing an existing API).

3. After Saving

• The API appears in the SQL API list, where you can:
  • Copy the full endpoint URL with one click.
  • View the saved configuration, including pagination help.
  • Edit the API configuration.
  • Test the API in a popup without leaving Drupal.
  • Delete the API.

Calling the API

• GET https://<your-site>/custom-api/<slug>
• A successful response looks like:

{
  "success": true,
  "type": "SELECT",
  "data": [
    { "type": "article", "cnt": "12" },
    { "type": "page", "cnt": "7" }
  ],
  "count": 2
}

Pagination (GET APIs)

4. Example: Content-Type Counts API

Goal: Return each content type and how many nodes it has, but only for types with more than 5 nodes, sorted by count.

• Basic Configuration: Name = Content type counts, Method = GET, Endpoint = type_counts.
• From Table: node
• Columns: Alias = type, Column = node.type
• Expressions: Alias = cnt, Expression = COUNT(node.nid)
• Group By: node.type
• Conditions: Type = having, Column = cnt, Operator = >, Value = 5
• Order By: Column = cnt, Direction = DESC
• Click Save Query, then call:

GET https://<your-site>/custom-api/type_counts

{
  "success": true,
  "type": "SELECT",
  "data": [
    { "type": "article", "cnt": "12" }
  ],
  "count": 1
}