Apps can register SQL queries to be used by the app. This is useful for apps that need to query the database for data.

Declaring the specific SQL queries in the app manifest.json allows the app to be more secure and performant. The app can also be more easily audited for security and performance.

​
Usage

...
  "sql_queries": [
    {
      "id": "appx_default_sql_query_1",
      "type": "select",
      "name": "SQL query 1",
      "description": "Example of predefined SQL query. For security reasons, use predefined queries to just fetch the data you needs",
      "query": "SELECT * FROM `user` WHERE external_id = ?",
      "test_args": ["auth-54-22"]
    },
    {
      "id": "appx_default_full_access",
      "type": "select",
      "name": "Full access",
      "description": "The app needs full 'read access' to the tables in order to do... (name the reason).",
      "query": "*"
    }
  ],
  ...

​
Usage in the app

You can execute an SQL query by calling the POST /api/app.execQuery API endpoint with:

​
Pre-defined SQL query

{
    "workspace_id": "acme_workspace1",
    "app_id": "app_myapp1",
    "query_id": "app_myapp1_get_user",
    "args": ["user_123"]
}

​
Custom SQL query

{
    "workspace_id": "acme_workspace1",
    "app_id": "app_myapp1",
    "query": "SELECT u.* FROM `user` u INNER JOIN user_segment s ON u.id = s.user_id WHERE s.id = ? LIMIT 100",
    "args": ["segment_abc", "US"]
}
Don’t forget to escape the table names with backticks like: SELECT * FROM `user` LIMIT 1. Especially for the user, order and session tables.
Data hooksExtra columns