Here we will describe how to use EasyQuery.JS widgets to implement an ad-hoc user-friendly query builder in your Node.js webapp. In result you can get something like this sample web-page for advanced DB search:
So, let’s start.
To perform all the steps below you will need to download EasyQuery.JS first and unpack it into some folder on your hard drive.
Node.js 7.0 or later is required to run the samples.
In order to run eqdm command line utility (see more details below) you will need NET Core 2.0 or higher. This doesn't mean that you need it on the computer where you plan to set up this demo web-page or on your production server - it could be any other computer.
eqjs.zip archive contains almost all necessary parts to run and test query builder web-page locally. It's preconfigured to run over well-known demo Northwind database and already include an API key for the testing account on SqlQueryBuilder.com web-service (more about it later).
So, all you need to do is:
eqjs.zipfile to some folder on your hard drive
samples\NodeJsDemoMySqlfor MySql demo,
samples\NodeJsDemoPostgreSqlfor PostgeSql demo, etc.)
run.batscript there (or consistently run
call npm installand
http://localhost:3200in your browser and enjoy the demo.
If you encounter any problem on any of the steps described above please don't hesitate and contact us immediately.
Now, when our sample web-page is setup and running well, we are going to discuss how to get the same behavior in your own web-application and for your own database
As you possibly know (if not - please read this article first) EasyQuery components and widgets do not work with your database directly. Instead, they use a special user-friendly representation of your DB called “data model”.
The data model defines which entities and attributes your users can operate with when they build their queries. It allows to set user-friendly names for tables (entities), fields (attributes) and operators (like “is equal to” or “starts with”). Additionally data model can contain a list of predefined values for some attribute - so your users will be able to select a criteria parameter from some list instead of typing it.
.NET version of EasyQuery allows you to build data model at run-time. On other platforms, you will need to create data model “manually” using either our eqdm command line utility (runs on Windows, Linux and Mac) or Data Model Editor (DME) GUI application (only for Windows).
Here are step-by-step instructions for both cases:
eqdm.jsonfile there and open it in some text editor
DbType. Possible options are: "MsSql", "MySql" and "PostreSql" (other DB types will be supported soon as well ).
run.shfor Linux and Mac) command.
When it's finished - you will find ModelID.xml and ModelID.json files in the same folder.
\DME40sub-folder of unpacked EasyQuery.JS package.
That’s all, close DME.
Copy the JSON file with your model (built on step #2) into the same folder with
EasyQuery.js file. This script processes all AJAX requests from EasyQuery widgets.
config.js file contains all the settings you may need to change in order to get your application worked:
SQBAPI_HOST: "http://sqlquerybuilder.com/", //You will need to change this address in case of using a standalone (local) version of SQL Query Builder web-servive SQBAPI_KEY: "XXXX-XXXXXXXXX-XXXX-XXXX", //Your API key for SQL Query Builder service MODEL_ID: "NWind"; //Your model's ID MODEL_FILE_JSON: "NWind.json"; //The name of your model file
Our sample script loads NWind.json model file. You need to replace the value of
MODEL_FILE_JSON configuration settings to the ID of your model and the name of JSON file created on previous step (e.g. "MyModel" and "MyModel.json").
config.js file contains the database connection settings as well. By default they are set to connect to our public database sample, but you may change this as you need.
Note, that the samples work with correspondent databases only (MySql, PostgreSql, etc.). If you want some other database to be used, you will need to modify the
EasyQuery.jsfile as well
EasyQuery widgets send the query, defined by the user in a JSON format. You can get it through
queryJson parameter in
executeQuery action handlers.
So, how do we get a correct SQL statement by this JSON query?
The answer is a special SQL query builder web-service we created for this task. This service implements a web API which allows you to pass your query JSON string and get a correct SQL statement in the result. To use it you need to get your API Key first and attach this key to any your request (in a Header section).
So, here are the instructions:
SQBAPI_HOST: "http://sqlquerybuilder.com/"; SQBAPI_KEY: "Your API key goes here";
MODEL_IDconfig setting above) and copy the content of the XML file created on the step #2.
/api/2.0/SqlQueryBuilderaction when you need to get an SQL statement. Send the JSON representation of the query returned by EasyQuery widgets as the request's content.
So all you need to do now - is to execute this SQL statement over your database, return the result set back to the client-side in some format and show that result set to the user in a form of some data grid or chart. We gave an example of possible JSON string in the Node.js script from our sample.
In our demo web page, we show generated SQL statement on any query change. Of course, you don’t need to do it in a production environment. Most possibly, you will hide it from users and show only the result table returned after SQL execution.
Our SQL query builder REST service is free but has some limitations on the number of daily requests one user can send.
To remove those limits or if you want to avoid using a third-party web-service for SQL generation - we have a local version of this service which you can install on your own Windows, Linux or Mac server.
For more information please read EasyQuery.JS web-page.
Feel free to send a support request if you have any questions regarding EasyQuery widgets or SqlQueryBuilder.com web-service.