SQL Server Connector
Introduction
This connector provides functionality for retrieving data using SQL Server Database in SCLAB Studio.
Features
- Connect to SQL Server DB with connection pool
- MQTT message publish using SQL Query with interval
- Create REST API endpoint with SQL Query
- JWT for HTTP authorization
- Processing of result data into JSON format
- SQL Injection filter (default on)
By adding a query in the format of "QUERY_1=mqtt;query;topic;interval ms" to the .env file, SQL is automatically executed to connect and retrieve data from SCLAB.
Two methods are supported: MQTT and API, each with a different variable format:
mqtt method format:
QUERY_#=mqtt;SQL Query;topic;interval MS
api method format:
QUERY_#=api;SQL Query;Endpoint URL
Installation
Prerequisites
- SQL Server Database
- connection information
- Install docker or nodejs
clone source
$ git clone https://github.com/sclab-io/sclab-sqlserver-connector
create JWT key file for API
$ mkdir jwt
$ ssh-keygen -t rsa -b 4096 -m PEM -f ./jwt/jwtRS256.key
# empty passphrase - just press enter
$ openssl rsa -in ./jwt/jwtRS256.key -pubout -outform PEM -out ./jwt/jwtRS256.key.pub
create .env.production.local
$ vi .env.production.local
# SQL Server Connection
MSSQL_DB_USER=sa
MSSQL_DB_PASSWORD=yourpassword
MSSQL_DB_NAME=dbname
MSSQL_SERVER=localhost
MSSQL_PORT=1433
MSSQL_POOL_MIN=1
MSSQL_POOL_MAX=10
MSSQL_IDLE_TIMEOUT_MS=30000
# SCLAB IoT
# MQTT_TOPIC=yourtopic/
# MQTT_HOST=yourhost
# MQTT_CLIENT_ID=your-client-id/1
# MQTT_ID=your-id
# MQTT_PASSWORD=your-password
# QUERY_#=mqtt;query;topic;interval ms
# QUERY_#=api;query;endPoint
QUERY_0=api;SELECT TOP 1 number FROM (SELECT ABS(CHECKSUM(NEWID())) % 100 + 1 AS number FROM sys.objects) AS random_numbers;/api/1
QUERY_1=api;SELECT * FROM employees;/api/2
# QUERY_3=mqtt;SELECT TOP 1 number FROM (SELECT ABS(CHECKSUM(NEWID())) % 100 + 1 AS number FROM sys.objects) AS random_numbers;test0;1000
# QUERY_4=mqtt;SELECT TOP 1 number FROM (SELECT ABS(CHECKSUM(NEWID())) % 1000 + 1 AS number FROM sys.objects) AS random_numbers;test1;5000
# PORT
PORT=3000
# TOKEN
SECRET_KEY=secretKey
JWT_PRIVATE_KEY_PATH=./jwt/jwtRS256.key
JWT_PUBLIC_KEY_PATH=./jwt/jwtRS256.key.pub
# LOG
LOG_FORMAT=combined
LOG_DIR=../logs
# CORS
ORIGIN=your.domain.com
CREDENTIALS=true
# SQL INJECTION
SQL_INJECTION=1
start
# docker compose
$ ./run.sh
# nodejs
$ npm run start
# pm2
$ npm run deploy:prod
stop
# docker compose
$ ./stop.sh
# pm2
$ ./node_modules/pm2/bin/pm2 stop 0
logs
$ ./logs.sh
access API end point
To access the API, you need to include authorization information in the Request Header in the following format:
authorization: yourkey
You can find your key information through the logs.
Build source
install nodejs
curl -sL https://rpm.nodesource.com/setup_18.x | sudo bash -
sudo yum install nodejs
build source
$ npm install
$ npm run build
Connect data using API
Connect to QUERY_2
QUERY_2=api;SELECT ${field} from ${table} where name="${name}";/api/2
1. Navigate to the data editor screen in SCLAB Studio and add an API.
Enter the API endpoint address you want to connect to. If you are using Docker, the default port is set to 9180.
Please enter the address in the following format.
http://ip:9080/api/2
2. Select the Headers tab and enter the authentication key value.
| Key | Value Type | Value |
|---|---|---|
| authorization | Fixed | yourkeyfromlog |
3. If it is a query with parameters set, add the setting value to the Params tab.
| Key | Value Type | Value |
|---|---|---|
| field | Variable | * |
| table | Variable | product |
| name | Variable | Bike |