This project is a HTTP SQL query wrapper for Azure SQL Data Warehouse built with Node.js.
It helps you query Azure SQL Data Warehouse through HTTP request.
sudo apt-get build-essential libssl-dev git
yum groupinstall "Development Tools"
yum install openssl-devel git
curl -o- https://raw.githubusercontent.com/creationix/nvm/v0.33.0/install.sh | NVM_DIR=/usr/local/nvm bash
nvm install 7.6.0
nvm use 7.6.0
nvm alias default 7.6.0
Install pm2:
npm install pm2 yarn -g
pm2 install pm2-logrotate
pm2 set pm2-logrotate:retain 5
pm2 startup
Install project modules, cd
to the project folder, and do:
npm install
Specify your Azure SQL Data Warehouse configurations in config.json
.
Build:
npm run build
Start server:
pm2 start process.json --env production
Save current running app
pm2 save
To execute a SQL command, you need to send a POST request to http://<ip-or-host>:5000/api/sql
with a body as follows:
{
"sql": "select * from my_table"
}
To run this application, you need to have both Node.js Runtime and Node Modules ready.
To install nvm (Node Version Manager) and Node.js.
First, you will need to make sure your system has a C++ compiler and git installed.
sudo apt-get build-essential libssl-dev git
yum groupinstall "Development Tools"
yum install openssl-devel git
To install or upgrade nvm, you can use the install script using cURL:
curl -o- https://raw.githubusercontent.com/creationix/nvm/v0.33.0/install.sh | NVM_DIR=/usr/local/nvm bash
or Wget:
wget -qO- https://raw.githubusercontent.com/creationix/nvm/v0.33.0/install.sh | NVM_DIR=/usr/local/nvm bash
The script clones the nvm repository to
~/.nvm
and adds the source line to your profile (~/.bash_profile
,~/.zshrc
,~/.profile
, or~/.bashrc
).
export NVM_DIR="$HOME/.nvm"
[ -s "$NVM_DIR/nvm.sh" ] && . "$NVM_DIR/nvm.sh" # This loads nvm
You can customize the install source, directory, profile, and version using the NVM_SOURCE
, NVM_DIR
, PROFILE
, and NODE_VERSION
variables.
Eg: curl ... | NVM_DIR=/usr/local/nvm bash
for a global install.
To verify that nvm has been installed, do:
command -v nvm
which should output 'nvm' if the installation was successful. Please note that which nvm
will not work, since nvm
is a sourced shell function, not an executable binary.
To download, compile, and install the LTS (Long Term Support) release of Node.js, do:
nvm install --lts
And then in any new shell just use the installed version:
nvm use --lts
To verify that Node.js has been installed, do:
node -v
Which should output the version of installed Node.js if the installation was successful.
This project is managed by PM2, which allows you to keep applications alive forever, to reload them without downtime and to facilitate common system admin task.
It is recommended to install pm2 as global module and other project ones as local module.
To install pm2, do:
npm install pm2 -g
To install project modules, cd
to the project folder, and do:
npm install
PM2 can generate startup scripts and configure them in order to keep your process list intact across expected or unexpected machine restarts.
Login as the user you want to run this application, and do:
pm2 startup
Follow the instructions to complete the startup configuration.
When updating nodejs, the pm2 binary path might change (it will necessarily change if you are using nvm).
Therefore, we would advise you to run the startup command after any update.
To optimize the performance, you need to build the project.
To Build the project, cd
to the project folder, and do:
npm run build
which should output built files under ./build
folder.
You can define several deployment environment configurations in process.json
and run different processes separately as you need by doing:
pm2 start process.json --env production
Refer to pm2 documentation for more details.
| Field | Type | Example | Description |
---------------------------------------------------------------------------------------------------------------------------------------
| name | string | "my-app" | application name |
| script | string | "./api/app.js" | script path relative to pm2 start |
| instances | number | -1 | number of app instance to be launched |
| exec_mode | string | "cluster" | mode to start your app, can be "cluster" or "fork", default "fork" |
| env | object | {"NODE_ENV": "development", "ID": "1"} | env variables which will appear in your app, -1 to spread to all CPUs |
| env_ | object | {"NODE_ENV": "production", "ID": "2"} | inject when doing pm2 restart app.json --env |
You may have noticed that you can declare environment-specific variables with the attribute env_* (e.g. env_production, env_staging…).
They can be switched easily. You just need to specify the --env <environment_name>
when acting on the application declaration.
# Inject what is declared in env_production
$ pm2 start process.json --env production
# Inject what is declared in env_staging
$ pm2 restart process.json --env staging
{
"apps": [{
"script": "./build/index.js",
"instances": -1,
"exec_mode": "cluster",
"env": {
"name": "adw-api-dev",
"ENV": "staging",
"NODE_ENV": "development",
"PORT": 3000,
"LOG_LEVEL": "debug"
},
"env_daily": {
"name": "adw-api-daily",
"ENV": "daily",
"NODE_ENV": "production",
"PORT": 3000,
"LOG_LEVEL": "info"
},
"env_staging": {
"name": "adw-api-staging",
"ENV": "staging",
"NODE_ENV": "production",
"PORT": 4000,
"LOG_LEVEL": "info"
},
"env_production": {
"name": "adw-api-production",
"ENV": "production",
"NODE_ENV": "production",
"PORT": 5000,
"LOG_LEVEL": "info"
}
}]
}
Before starting this application, you have to specify your Azure SQL Data Warehouse configurations in config.json
.
You can define several env configurations and switch to any of them easily by specify env variables in process.json
.
{
"default": {
"user": "username",
"password": "password",
"server": "hostname",
"database": "database",
"pool": {
"max": 1024
},
"options": {
"requestTimeout": 30000,
"encrypt": true
}
},
"daily": {
"user": "username",
"password": "password",
"server": "hostname",
"database": "database",
"pool": {
"max": 1024
},
"options": {
"requestTimeout": 30000,
"encrypt": true
}
},
"staging": {
"user": "username",
"password": "password",
"server": "hostname",
"database": "database",
"pool": {
"max": 1024
},
"options": {
"requestTimeout": 30000,
"encrypt": true
}
},
"production": {
"user": "username",
"password": "password",
"server": "hostname",
"database": "database",
"pool": {
"max": 1024
},
"options": {
"requestTimeout": 30000,
"encrypt": true
}
}
}
Finally, to manage this application, you just need 3 commands as follows.
To start a new application, do:
pm2 start process.json --env <environment>
to gracefully reload a application without downtime, do:
pm2 reload <app-id>
To stop a application, do:
pm2 stop <app-id>
To execute a SQL command, you need to send a POST request to http://<ip-or-host>:<port>/api/sql
with a body as follows:
{
"sql": "select * from my_table",
"timeout": 30000, // optional, request timeout in milliseconds
"user": "user", // optional, customized query credentials
"password": "password" // optional, customized query credentials
}
Note: Do NOT send more than one queries at the same time, ONLY the result of the last query will be returned.
select * from my_table_1; select * from my_table_2Which will ONLY return all rows from
my_table_2
.