JavaScript query builder with Node.js

What it's all about.

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.

Running the samples on the testing data 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 web-service (more about it later).
So, all you need to do is:

  • Unpack file to some folder on your hard drive
  • Open appropriate folder under samples folder (samples\NodeJsDemoMySql for MySql demo, samples\NodeJsDemoPostgreSql for PostgeSql demo, etc.)
  • Start run.bat script there (or consistently run call npm install and node EasyQuery.js commands)
  • Open http://localhost:3200 in your browser and enjoy the demo.
    You can try to build a simple query and execute it: For example, add "Customer Company Name" and "Order Freight" columns into "Columns" panel and a simple condition like "Customer Country is equal to USA".

If you encounter any problem on any of the steps described above please don't hesitate and contact us immediately.

Running the samples on your own database

1. Create data model for your own database.

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 command line utility

  • Open eqdm folder, find eqdm.json file there and open it in some text editor
  • Specify the type of your database in DbType. Possible options are: "MsSql", "MySql" and "PostreSql" (other DB types will be supported soon as well ).
  • Specify the connection string to your database. If you don't know how the connection string should look like for your DB - use website. Just select your database and then use the Connector/Net option there.
  • You can also specify your model ID and name in the config file. Alternatively, your can pass them in parameters as --modelId:ModelID and --modelName:ModelName.
  • Save the config file and start eqdm utility using run.bat (or for Linux and Mac) command.

When it's finished - you will find ModelID.xml and ModelID.json files in the same folder.


  • Run DMEditor40.exe file from \DME40 sub-folder of unpacked EasyQuery.JS package.
  • Select "Model | New" menu item.
  • Type your model name (any), select the type of your database type (MS SQL, Oracle, MySQL, etc) and enter the connection string. If you don’t know how connection string may look like - use “Build connection” button and follow the wizard which helps you to set up a connection to your DB.
  • Press OK in “New model” dialog and then follow the prompts that will appear to add tables, links, etc. Save your model to some XML file (Model | Save menu) for future use.
  • Use “Model | Save as..” menu item to create a copy of your model file in JSON format (e.g. "MyModel.json"). Select “JSON” file type in “Save as type” field.

That’s all, close DME.

2. Connect your model and modify config.js script

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. The config.js file contains all the settings you may need to change in order to get your application worked:

SQBAPI_HOST: "",  //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_ID and 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").

The 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.js file as well

3. Generating and executing SQL by the query defined in UI

EasyQuery widgets send the query, defined by the user in a JSON format. You can get it through queryJson parameter in saveQuery, syncQuery or 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:

  • Open SQL query builder web-page and click on Register link.
  • Fill the form and click on Register button to finish registration and get your API Key.
  • You will get your API key in a moment by email. Copy it into your config.js script:
SQBAPI_KEY: "Your API key goes here";
  • Then press “Add model” link, enter the ID of your model (the same as you set for MODEL_ID config setting above) and copy the content of the XML file created on the step #2.
  • Now you can send a POST request to /api/2.0/SqlQueryBuilder action 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.

Standalone (local) version of SQL query builder web-service

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 web-service.

Published: 2017-11-24 Last updated: 2019-01-14