SQL Connector

The SQL Connector polls a relational database on a schedule and writes the results into the DataPool. It supports SQL Server, MySQL, PostgreSQL, and SQLite.

Connection Strings

SQL Server


Server=myserver.database.windows.net;Database=mydb;User Id=myuser;Password=mypassword;Encrypt=True;

MySQL


Server=myserver;Port=3306;Database=mydb;Uid=myuser;Pwd=mypassword;SslMode=Required;

PostgreSQL


Host=myserver;Port=5432;Database=mydb;Username=myuser;Password=mypassword;SSL Mode=Require;

SQLite


Data Source=/path/to/database.db;

Configuration

Setting	Description	Default
Connection String	Database connection string (see above)	Required
Query	SQL SELECT statement to execute	Required
Polling Interval	How often to run the query	60 seconds
Stream Name	DataPool stream name for the results	Required
Item Key Column	Column that uniquely identifies each row	First column
Timeout	Query execution timeout	30 seconds

Writing Queries

Write a standard SQL SELECT statement. Every column in the result set becomes a DataPool field.


SELECT
    RegionName AS region,
    TotalSales AS sales,
    Target AS target,
    ROUND((TotalSales / Target) * 100, 1) AS pct_of_target
FROM SalesReport
WHERE ReportDate = CAST(GETDATE() AS DATE)
ORDER BY TotalSales DESC

Avoid queries that return large result sets. The SQL Connector is designed for summary/dashboard data, not bulk data transfer. Keep results under 1,000 rows for optimal performance.

Parameterised Queries

You can use parameters in your queries to make them dynamic:

Parameter	Value
`@today`	Current date
`@now`	Current date and time
`@yesterday`	Yesterday’s date
`@startOfWeek`	Start of the current week
`@startOfMonth`	Start of the current month

Example:


SELECT Department, HeadCount
FROM StaffingLevels
WHERE ReportDate = @today

Data Type Mapping

SQL Type	DataPool Type
INT, BIGINT, FLOAT, DECIMAL	Number
VARCHAR, NVARCHAR, TEXT, CHAR	String
BIT, BOOLEAN	Boolean
DATE, DATETIME, TIMESTAMP	Date
JSON, JSONB	JSON

Error Handling

Error	Cause	Solution
Connection timeout	Server unreachable	Check network, firewall rules, server status
Authentication failed	Wrong credentials	Verify username and password
Query timeout	Query too slow	Optimise query, add indexes, increase timeout
Permission denied	Insufficient privileges	Grant SELECT permission to the database user

Best Practices

Use a read-only database user for security
Add indexes on columns used in WHERE clauses
Use views to simplify complex queries
Set polling interval based on how frequently the source data changes
Use column aliases (AS) to give fields meaningful names in the DataPool