Connecting to Databases

A Database Connection lets your reports read data from a SQL Server or PostgreSQL database, using the [Database.Xxx:] macros.

If the database cannot be reached from the internet, install a Magic Suite Agent on your network instead, and use the Agent SQL macros, such as [Agent.SqlValue:]. Please raise a support ticket if you need more guidance.

Before you start

  • Allow our servers to reach the database: their public IP range is 46.249.212.128/27
  • Have a database login that can read the data your reports need

Creating the Connection

  1. In Admin, open Connections, then click Create
  2. Choose Database as the type
  3. Enter a name and, optionally, a description. We recommend Default as the name of the first Connection of each type
  4. Fill in:
    • URL: the database server's host name, such as db.example.com. For SQL Server on a port other than 1433, add the port after a comma, as db.example.com,14330
    • User Name and Password: the database login. A PostgreSQL password may contain any characters, semicolons and quotes included (coming in version 4.7)
    • Configuration: see Configuration below
  5. Click Test Connection, then Save

Configuration

The Configuration is JSON. Set only what you need: a setting you leave out takes its default.

SettingWhat it doesDefault
TypeThe database engine: Mssql for SQL Server, or Postgres for PostgreSQL.Mssql
NameThe database to read from. On SQL Server, leave it out to use the login's default database.None
PortPostgreSQL only: the server's port. Ignored when the URL includes a port, as host:port. For SQL Server, put the port in the URL instead, as server,port.5432
TimeoutMsHow long one attempt at a query may take, in milliseconds, when the macro does not set its own.20000
ConnectTimeoutSecondsSQL Server only: how long to wait to connect, in seconds.15
EncryptSQL Server only: true requires encryption, false allows unencrypted traffic.Required
TrustServerCertificateSQL Server only: true accepts the server's certificate without checking it, for a server with a self-signed certificate. This is less secure.false
SqlServerAuthenticationMethodSQL Server only: how to sign in. Leave it out for a SQL Server login. For a Microsoft Entra app, use ActiveDirectoryServicePrincipal, with the app's client ID as the User Name and its secret as the Password. Microsoft's documentation lists the other values.A SQL Server login

For example, for SQL Server:

{
  "Type": "Mssql",
  "Name": "Sales"
}

And for PostgreSQL on port 5433:

{
  "Type": "Postgres",
  "Name": "sales",
  "Port": 5433
}

Tip: Connections, Tools, Configure a Connection fills in the Configuration with a form, with help on every setting (coming in version 4.8).

Using it in reports

Choose the Connection with [Database.Connection:], then query it with [Database.Value:], [Database.Values:], [Database.List:], [Database.Table:] or [Database.Graph:]. For example:

[Database.Connection: name=Default]
[Database.Value: sql="SELECT COUNT(*) FROM Orders"]

More information

Reconnection

Reconnection

Reconnection

timed out

timed out

Attempt of

Reload
An unhandled error has occurred. Reload 🗙