Query Live Data with SQL

In this tutorial you will explore your running ControlBird system two ways: visually with the Database Browser app, and with real SQL through the PostgreSQL-compatible SQL access. By the end you will have browsed the live entity tree, connected a standard PostgreSQL client, run SELECT queries with filters and aggregates, traversed entity relationships inline, and made your first INSERT, UPDATE, and DELETE. Every query you run reads or writes the same live in-memory store, so results reflect the exact state of your system at the moment you press enter.

What you'll need

• A running ControlBird node you can reach over the network.
• Access to the control plane UI with the app.database-browser permission, which is required to open the Database Browser app.
• A PostgreSQL client for the SQL portion. psql is used in the examples below, but DBeaver and most BI connectors work the same way.
• A valid JWT token to authenticate the SQL connection. You supply this token as the PostgreSQL password; the control plane issues it for your session.
• The node hostname and the SQL port, which defaults to 5432.

Part 1: Explore the tree in the Database Browser

Start visually so you can see the shape of your data before you query it. The Database Browser shows the entity store as a set of resizable columns you drill through one level at a time.

1. Open the Database Browser app from the applications menu in the control plane UI.

   Expected result: the app opens with a left column listing all available entity types, such as Service, Alarm, Agent, and Endpoint.

2. Select an entity type in the left column, for example Service. The next column populates with the entities of that type.

3. Keep selecting entities to drill down through the hierarchy one level at a time. Each selection opens a new column to its right, building a path from the root toward your target.

4. With an entity selected, look at the right-hand details panel. It shows the entity's field values, the field definitions, and metadata for each field: $WriteTime (when the field was last written) and $Writer (which entity wrote it).

   Expected result: for a Service you can read fields like Name and Description, each with its current value and write metadata.

5. Press Ctrl+F to focus the search box and filter entity names within the current column. This is the fastest way to find one entity among many.

6. Follow a Parent reference to move up the tree, or click an entity reference field (for example an Alarm's Behavior) to jump across the model to the referenced entity.

7. If a column feels cramped, drag a column divider to resize it. Columns resize between a minimum of 150px and a maximum of 400px.

Part 2: Connect a SQL client

Now switch to SQL. Every entity type you just browsed is automatically exposed as a table, so there is no separate schema to maintain. The table list is derived directly from your data model.

1. Point your PostgreSQL client at your node on port 5432, using username controlbird, database controlbird, and your JWT token as the password. With psql:

   PGPASSWORD="<your-jwt-token>" psql \
     --host=your-node-hostname \
     --port=5432 \
     --username=controlbird \
     --dbname=controlbird

   Expected result: a controlbird=> prompt. You are now connected to the live store. Your session token stays valid for the duration of your session.

Part 3: Run your first queries

Each row in a table is one entity instance. Start by selecting a couple of fields, then build up to filters, sorting, and relationship traversal.

1. Select specific fields from a table:

   SELECT Name, Description FROM Service;

   Expected result: one row per Service entity, showing each service's name and description.

2. Expand every field defined for the type with SELECT *:

   SELECT * FROM Service;

   Every row also carries a synthetic entity_id column (INT8). All values come back as TEXT; infer the concrete type from the field definition on the client side.

3. Narrow the result set with a WHERE clause:

   SELECT Name FROM Service WHERE Logging = 'Enabled';

   Expected result: only services whose Logging field equals Enabled. WHERE clauses are evaluated against each entity. Supported operators include =, !=, <, <=, >, >=, LIKE (with % and _ wildcards), IN, BETWEEN, IS NULL, IS NOT NULL, AND, and OR.

4. Sort and cap the output:

   SELECT Name, MaxLogFiles FROM Service ORDER BY Name LIMIT 10;

   Expected result: the first ten services, ordered by name.

Part 4: Traverse relationships and aggregate

ControlBird has no SQL JOIN. Instead you follow entity references inline with the field-indirection operator ->, reading fields from related entities in the same query.

1. Read a parent entity's field alongside the child:

   SELECT Name, Parent->Name FROM Service;

   Expected result: each service's name next to the name of its parent entity.

2. Chain references to walk further up the tree:

   SELECT Name, Parent->Parent->Name FROM Service;

3. Read field write metadata inline using the $WriteTime and $Writer suffixes:

   SELECT Name, Name$WriteTime, Name$Writer FROM Service;

4. Aggregate across entities. Count every entity of a type:

   SELECT COUNT(*) FROM Service;

5. Bucket results with GROUP BY:

   SELECT Logging, COUNT(*) FROM Service GROUP BY Logging;

   Expected result: a count of services per Logging value. The standard aggregates COUNT, SUM, AVG, MIN, and MAX are supported, with HAVING to filter the aggregated output.

Part 5: Write data

SQL access is not read-only. INSERT, UPDATE, and DELETE are converted into store mutations against the live system, so use them deliberately.

1. Create an entity. The entity_id is assigned automatically:

   INSERT INTO Service (Name, Description) VALUES ('my-service', 'Tutorial service');

   Expected result: a new Service entity. If you omit Name, a UUID-based name is generated for you.

2. Modify it with an UPDATE (a WHERE clause is required):

   UPDATE Service SET Description = 'Updated' WHERE Name = 'my-service';

3. Remove it with a DELETE:

   DELETE FROM Service WHERE Name = 'my-service';

   Expected result: the entity is gone. Verify with a quick SELECT Name FROM Service WHERE Name = 'my-service';, which should return no rows.

Bonus: query historical data

If the Historian service is running and reachable via HISTORIAN_ADDRESS, you can read time-series data through the History$ table prefix:

SELECT * FROM History$SensorData LIMIT 100;

Historian rows use a fixed column layout: timestamp (INT8), entity_id (INT8), field_path (TEXT), value (TEXT), and writer_name (TEXT).

Next steps

You can now navigate the entity tree visually and query it with SQL. To go deeper:

• Read the Database Browser & SQL reference for the full operator list, limitations, and configuration settings.
• Learn how entity types and fields are defined in the Model Builder guide. The tables you just queried come straight from your data model.