Step-by-Step Guide: Turn SQL Queries into Embedded Visualizations

Most teams have access to data, but getting that data into the hands of users is still slow and manual.

Dashboards take time to build. Reports go stale. And embedding insights into applications often requires custom backend work.

This guide shows how to solve that using AI Squared’s UNIFI platform. You will build a Data App that connects directly to your database, runs dynamic SQL queries, and renders the results as an interactive visualization that can be embedded anywhere.

Overview

Data Apps in UNIFI connect a structured data model to a configurable UI visualization. This workflow covers the full process: creating a dynamic SQL query data model over a PostgreSQL source, configuring a bar chart visualization in the Data App builder, and exporting the result as a JavaScript snippet for embedding in any HTML page.

Use case: Query aggregated records from a database and render them as an interactive bar chart widget — embeddable in a dashboard, internal tool, or customer-facing page — with no custom backend code required.

Getting Started with Data App

All Data Apps in UNIFI require a Dynamic SQL Query data model. Unlike static table selection, a dynamic SQL model accepts runtime variables — enabling the app to respond to user interactions, DOM selectors, or filter inputs without rebuilding the model.

Dynamic SQL Variables

Variables are declared in the SQL query using a colon prefix. UNIFI treats any :variable token as a runtime-injectable parameter:

— Syntax: colon prefix declares a runtime variableSELECT * FROM sales WHERE region = ‘:region’ — The variable name (region) becomes a named parameter– that can be bound at runtime via DOM selectors or API input

These variables allow downstream UI components — such as dropdowns, text inputs, or URL parameters — to dynamically change what data is retrieved from the database without modifying the model.

Retrieve-All Workaround

When no filtering is needed and the goal is to retrieve all records, use the IS NOT NULL workaround to satisfy the dynamic SQL requirement while returning the full dataset:

— Retrieve-all pattern: variable is always satisfiedSELECT    category,    SUM(value) AS total_valueFROM your_tableWHERE category IS NOT NULL  AND ‘:variable’ IS NOT NULLGROUP BY categoryORDER BY total_value DESC

Tip: The ‘:variable’ IS NOT NULL condition is always true regardless of what value is passed in — so all rows are returned on every call. This satisfies UNIFI’s dynamic SQL requirement while functioning as a full-table query. Replace category and value with your actual column names.

Step 1 — Create the Dynamic SQL Data Model

In UNIFI: Models → Add Model → select your PostgreSQL source → Dynamic Query

1.1  Model Configuration

Field	Value	Notes
Source	Your PostgreSQL source	Must be registered under Sources before creating the model
Retrieval Method	Dynamic Query	Required for all Data Apps — enables runtime variable injection
Variable syntax	:variable_name	Colon prefix in SQL — declares a named runtime parameter
Retrieve-all condition	WHERE ‘:variable’ IS NOT NULL	Returns all rows while satisfying the dynamic SQL requirement

1.2  Write the Query

1. In the Dynamic Query editor, write your SQL using the retrieve-all pattern (or a real filter variable if filtering is needed)
2. Ensure the SELECT returns the columns you intend to map to the chart axes — typically a category/label column and a numeric/aggregate column
3. Click Show Preview to confirm the query returns the expected rows and column structure before saving
4. Give the model a descriptive name and save it

Tip: Always use Show Preview before saving. The column names returned by the query become the available fields in the Data App visual builder — if a column is missing here, it will not be available for axis mapping later.

Step 2 — Create the Data App

In UNIFI: Data Apps → New Data App → select your data model

2.1  Select Visual Type

1. Create a new Data App and select the data model created in Step 1 as its data source
2. In the visual type selector, choose Bar as the chart type
3. UNIFI will load the available columns from your model as mappable fields

2.2  Configure the Bar Chart

Setting	Value	Notes
X Axis	Category / label column	The field that defines each bar — typically a string or enum column
Y Axis	Numeric / aggregate column	The field that determines bar height — typically a SUM, COUNT, or float column
Chart Title	Descriptive title	Displayed above the chart in the rendered widget
Color	Single color or per-category	Configure in the chart styling panel
Legend	On / Off	Enable if multiple series or categories need labelling

1. Adjust axis labels, color scheme, and title in the visual configuration panel
2. Use the live preview in the Data App builder to verify the chart renders correctly against the model data
3. If the chart is empty, click Refresh to re-run the model query and reload the preview data

2.3  Variable Binding (Optional)

If you want the chart to respond to user input — for example, a dropdown that filters by category — bind a DOM selector to the model variable:

— Model query with a real filter variable:SELECT category, SUM(value) AS totalFROM your_tableWHERE region = ‘:selected_region’GROUP BY category — In the exported HTML, a DOM selector (e.g. a <select> element)– with id=’selected_region’ will automatically bind to :selected_region– and re-query the chart on change

Tip: DOM selector binding uses the variable name as the element ID. For example, :selected_region binds to <select id=”selected_region”> in the HTML page. When the user changes the selection, the chart re-fetches data automatically using the new value.

Step 3 — Export and Embed

In UNIFI: Data Apps → open your app → Export → JavaScript / HTML

Once the chart is configured and previewed, export it for deployment. The export produces a self-contained JavaScript snippet that renders the bar chart widget in any web page.

3.1  Export Steps

1. In the Data App builder, click Export or Embed
2. Copy the generated JavaScript snippet
3. Paste the snippet into your target HTML page inside a <script> tag at the intended render location
4. If your query uses filter variables, add the corresponding DOM elements (dropdowns, inputs) with matching IDs to the HTML page
5. Open the HTML page in a browser and confirm the chart loads and displays data correctly

3.2  Example HTML Structure

<!DOCTYPE html><html lang=”en”><head>  <meta charset=”UTF-8″ />  <title>Bar Chart Widget</title></head><body>   <!– Optional: filter input bound to :selected_region variable –>  <select id=”selected_region”>    <option value=”West”>West</option>    <option value=”East”>East</option>  </select>   <!– Paste exported UNIFI Data App snippet below –>  <script src=”…”></script> </body></html>

Tip: The exported snippet is self-contained — it includes the chart rendering library, data fetching logic, and UNIFI authentication. No additional dependencies need to be installed. Keep the snippet up to date if you change the model or chart configuration.

Quick Reference — Workflow Summary

Step	Action	Key Detail
1a	Create Dynamic SQL Model	Select Dynamic Query retrieval method — required for all Data Apps
1b	Write retrieve-all query	Use WHERE ‘:variable’ IS NOT NULL to return all rows while satisfying the dynamic SQL requirement
1c	Preview and save model	Always Show Preview first — column names from this query become the chart axis fields
2a	Create Data App	Select the dynamic SQL model as the data source
2b	Select Bar visual type	Map X Axis to label/category column, Y Axis to numeric/aggregate column
2c	Configure and preview chart	Set title, color, legend; refresh preview to confirm data loads correctly
2d	Variable binding (optional)	DOM element IDs matching :variable names auto-bind to filter the chart on user input
3a	Export JS snippet	Self-contained script; paste into HTML <script> tag
3b	Add DOM selectors if needed	Match element IDs to variable names for dynamic filtering
3c	Verify in browser	Open HTML page and confirm chart renders with live data