From businesses looking to optimize their operations, data influences the decisions being made. For scientists looking to validate their hypotheses, data influences the conclusions being arrived at. Regardless, the sheer amount of data collected and harnessed from various sources presents the challenge of identifying rising trends and interesting patterns hidden within this data. If the data is stored within an SQL database, such as PostgreSQL, querying data with the expressive power of the SQL language unlocks the data's underlying value. Creating interfaces to fully leverage the constructs of SQL in analytics dashboards can be difficult if done from scratch. With a library like React Query Builder, which contains a query builder component for fetching and exploring rows of data with the exact same query and filter rules provided by the SQL language, we can develop flexible, customizable interfaces for users to easily access data from their databases.
Although there are open source, administrative tools like pgAdmin, these tools cannot be integrated directly into a custom analytics dashboard (unless embedded within an iframe). Additionally, you would need to manage more user credentials and permissions, and these tools may be considered too overwhelming or technical for users who aren't concerned with advanced features, such as a procedural language debugger, and intricate back-end and database configurations.
By default, the
<QueryBuilder /> component from the React Query Builder library contains a minimal set of controls only for querying data with pre-defined rules. Once the requested data is queried, this data can then be summarized by rendering it within a data visualization, such as a table or a line graph.
Below, I'm going to show you how to integrate the React Query Builder library into your application to gain insights into your data.
Installation and Setup#
The Client-Side Application#
To get started, scaffold a basic React project with the Create React App and TypeScript boilerplate template.
Inside of this project's root directory, install the
If you happen to run into the following TypeScript error...
Could not find a declaration file for module 'react'. '<project-name>/node_modules/react/index.js' implicitly has an 'any' type.
... then add the
"noImplicitAny": false configuration under
compilerOptions inside of
tsconfig.json to resolve it.
The Server-Side Application#
React Query Builder composes a query from the rules or groups of rules set within the query builder interface. This query, in JSON form, should be sent to a server-side application that's connected to a PostgreSQL database to properly format the query into a SQL statement and execute the statement to fetch records of data from the database.
For this tutorial, we will send this query to an Express.js API running within a multi-container Docker application. This application also runs a PostgreSQL database and the pgAdmin in separate containers. The API connects to the PostgreSQL database and defines a POST route for processing the query. With Docker Compose, you can execute a single command to spin up all of these services at once on a single host machine! To run the entire back-end, you don't need to manually install PostgreSQL or pgAdmin on your machine; you only need Docker installed on your machine. Plus, if you decide to run other services, such as NGINX or Redis, then you can add them within the
docker-compose.yml configuration file.
Clone the following repository:
Inside the root this cloned project, add a
.env.development file with the following environment variables:
To run the server-side application, execute the following command:
This command starts up the server-side application. When you re-build and restart the application with this same command, it will do so from scratch with the latest images. It's up to you if you want to leverage caching to expedite the build and start up processes. Nevertheless, let's break down what this command does:
docker-compose build --force-rm --no-cache- Build the services. When building images, tell Docker to not use any already cached images and to use fresh images from the Docker Registry. After the build process, tell Docker to remove any temporary, intermediate containers involved during the build process.
docker-compose up --detach- Start up the application from the built services. Once the containers are spun up, tell Docker to exit and continue to run the containers in the background (detached mode, specified with the option
docker-compose logs -f- Prints the log output from all of the services to the terminal. The
--followoption keeps the logs running in the terminal, so new logs will continue to be printed to the terminal. This is extremely useful for debugging container issues.
docker-compose command, pass a set of environment variables via the
--env-file option. This approach in setting environment variables allows these variables to be accessed within the
docker-compose.yml file and easily works in a CI/CD pipeline. Since the
.env.<environment> files are typically not pushed to the remote repository (i.e., ignored by Git), especially for public-facing projects, when deploying this project to a cloud platform, the environment variables set within the platform's dashboard function the same way as those set by the
The PostgreSQL database contains only one table named
cp_squirrels that is seeded with 2018 Central Park Squirrel Census data downloaded from the NYC Open Data portal. Each record represents a sighting of an eastern gray squirrel in New York City's Central Park in the year 2018.
Let's verify that pgAdmin is running by visiting
localhost:5050 in the browser. Here, you will be presented a log-in page. Enter your credentials (
NYCSC_PGADMIN_PASSWORD) into the log-in form. On the pgAdmin welcome page, right-click on "Servers" in the "Browser" tree control (in the left pane) and in the dropdown, click
Create > Server.
Under "General," set the server name to
nyc_squirrels. Under "Connection," set the host name to
nycsc-pg-db, the container name set for our
nycsc-pg-db. It is where our PostgreSQL database is virtually hosted at on our local machine. Set the username and password to the values of
Save those server configurations. Wait for pgAdmin to connect to the PostgreSQL database. Once connected, it should appear under the "Browser" tree control.
Right-click on the database (
nyc_squirrels) in the "Browser" tree control and in the dropdown, click the
Query Tool option. Inside of the query editor, type a simple SQL statement to verify that the database has been properly seeded:
This statement should return the first ten records of the
Let's verify that the Express.js API is running by visiting
localhost:<NYCSC_API_PORT>/tables in the browser. The browser should display low-level information about the tables available in our PostgreSQL database. In this case, our database only contains a single table:
Great! With the server-side working as intended, let's turn our attention back to integrating the React Query Builder component into the client-side application.
Initializing and Configuring the React Query Builder#
Inside of our Create React App project's
src/App.tsx file, import the
<QueryBuilder /> component from the React Query Builder library. At a minimum, this component accepts two props:
fields- A list of fields that represent the fields of a record. For example, each record of our
cp_squirreltable contains the fields
x(the x-coordinate of the squirrel sighting),
y(the y-coordinate of the squirrel sighting),
id(the unique identifier assigned to a squirrel), etc. These fields can be used in a SQL statement's
WHEREclause to extract a subset of the records that fulfill a specific condition. For example, to retrieve records that involve sightings of squirrels with cinnamon-colored fur, we would write the predicate to return records only when the
primary_fur_colorfield set to "Cinnamon" (
WHERE primary_fur_color = 'Cinnamon'). Each field presented by the query builder consists of a name and a label. The label is displayed to the user in the query builder, and the name is sent to the API as part of a query object. For the
primary_fur_colorfield, the label would be
Fur Colorand the name would be
onQueryChange- A function that is invoked whenever the query changes. Any action, such as the user changing the operator (
>, etc.) or field of a rule, will cause this function to be invoked.
This is what the query builder looks like without any styling and with only these two props passed to the
<QueryBuilder /> component:
This probably doesn't make much sense, so let's immediately jump into a basic example to better understand the capabilities of this component.
Let's make the following adjustments to the
src/App.tsx file to create a very basic query builder:
Open the application within your browser. The following three element component is shown in the browser:
The first element is the combinator selector, which is a
<select /> element that contains two options:
OR. These options correspond to the
OR operators of a SQL statement's
The second element is the add rule action, which is a
<button /> element (
+Rule) that when pressed will add a rule. If you press this button, then a new rule is rendered beneath the initial query builder component:
A rule consists of a field, an operator and a value editor, and it corresponds to a condition specified in a SQL statement's
WHERE clause. The field
<select /> element lists all of the fields passed into the
fields prop. Notice that the label of the field is shown in this element. The operator
<select /> element lists all of the possible comparison/logical operators that can be used in a condition. Lastly, the value editor
<input /> element contains what the field will be compared to. For example, if we type
-73.9561344937861 into the
<input /> field, then the condition that will be specified in the
WHERE clause is
X = -73.9561344937861. Basically, this will fetch all squirrel sightings located at the longitudinal value of
With only one rule, the combinator selector is not applicable. However, if we press the add rule action button again, another rule will be rendered, and the combinator selector will become applicable.
With two rules, two conditions are specified and combined with the
X = -73.9561344937861 AND Y = 40.7940823884086.
The third element is the add group action, which is a
<button /> element (
+Group) that when pressed will add an empty group of rules. If you press this button, then a new group is rendered beneath whatever has already been rendered in the query builder component:
Currently, there are no rules within the newly created group. When we add two new rules to this group by pressing its add rule action button twice and change the value of its combinator selector to
OR, like so:
The two rules within this new group are combined together similar to placing parentheses around certain conditions in a
WHERE clause to give a higher priority to them during evaluation. For the above case, the overall condition specified to the
WHERE clause would be
X = -73.9561344937861 AND Y = 40.7940823884086 AND (X = -73.9688574691102 OR Y = 40.7837825208444).
A total of eight fields are defined. Essentially, they are based on the columns of the
cp_squirrels table. For each field, the
name property corresponds to the actual column name, and the
label property corresponds a more presentable column title that is shown in the field
<select /> element of each rule.
If you look into developer tools console, then you will see many query objects logged to the console:
Every single action performed on the query builder that changes the query will invoke the
logQuery function, which prints the query to the console. If we import the
formatQuery function from the
react-querybuilder library and call it inside of
logQuery with the query, then we can format the query in many different ways. For now, let's format the query to a SQL
If we modify any of the controls' values, then both the query (in its raw object form) and its formatted string (as a condition of a
WHERE clause) are printed to the console:
Querying the PostgreSQL Database#
With the fundamentals out of the way, let's focus on sending the query to our Express.js API to fetch data from our PostgreSQL database.
src/App.tsx, let's add a "Send Query" button below the
<QueryBuilder /> component:
Note: The underscore prefix of the
_evt argument indicates an unused argument.
When the user clicks this button, the client will send the most recent query to the
/api/records endpoint of the Express.js API. This endpoint takes the query, formats it into a SQL statement, executes this SQL statement and responds back with the result table.
We will need to store the query inside a state variable to allow other functions, such as , within the
<App /> component to access the query. This changes our uncontrolled component to a controlled component.
onQueryChange is invoked, the
setUpdateQuery method will update the value of the
updateQuery variable, which must adhere to the type
sendQuery function to send
updateQuery to the
/api/records endpoint and log the data in the response.
Inside of the query builder, if we want retrieve squirrel sightings found at the coordinates (40.7940823884086, -73.9561344937861), then create two rules: one for
X (longitude) and one for
When we press the "Send Query" button, the result table (in JSON) is printed to the console:
Only one squirrel sighting was observed at that particular set of coordinates.
Let's display the result table in a simple table: