SQL databases can be utilized as a resources within your organization. Users are able to take their already existing database tables or views and sync them with Device Magic. This integration allows for updates to your database to be passed seamlessly to Device Magic.

For an overview of our Resources feature, please have a look at our help section here first.

Please note: This is only available on our Enterprise plan.

Adding a SQL Resource

Database Table Requirements

1) One or more fields with unique values (ex. id).

2) One or more fields of the type TIMESTAMP. It's recommended that this field is set when a row is updated to the current timestamp.

First, navigate to your Resources page and click the "Add New Resource" button.

On the following page, select SQL Database as the Resource type (Step 1).

Connecting Your Database

A note on the requirements to successfully fetch and reference your SQL database:

The database must be accessible over HTTPS from a specified set of IP ranges as explained in this article.

SQL Options:

Enter your SQL server's details in order to connect.

  • Adapter: Select your SQL server's adapter (Microsoft SQL Server, MySQL or PostgreSQL) and enter the additional database details.

  • Host

  • Username

  • Password

  • Port

  • Database Name

  • Schema Name

  • Table: To select a table or view, click Fetch tables from database.

Select the table or view and the associated columns you'd like to sync. You can select as many columns as you want!

Note: If no records are present in the table, you will be unable to see or select columns in the forms. Once at least one record is added, you will be able to both see and select values

Record configuration:

In Step 3, configure the columns required to identify synced records.

  • Unique identifier: (required) This value should be a column containing unique values (ex. id).

  • Timestamp: (required) This should be a column containing TIMESTAMP values that is updated when a row is changed.

    Note: For MS SQL: The timestamp data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime or a smalldatetime data type.

  • Boolean filter: (optional) Column containing BOOLEAN values to include and exclude chosen rows from the sync. When a row is marked true, it will be included in the sync.

Lastly, click on Create Resource.

Your Resource List

On your Resources page, you'll see your newly added SQL Live Resource. You can also use the search bar or click the Updated header to order the resources so that the most recently added/updated resource is at the top.

Next to your SQL Live Resource, you'll see a settings button.

Sync Resource gives you the option to manually sync your resource. Although this isn't required for updates to your resource, the option is available.

The system checks for updates to the resource every 10 minutes automatically.

You also have the options to delete your resource.

Other Useful Articles:

If you have any questions or comments feel free to send us a message at support@devicemagic.com.

Did this answer your question?