Tutorials on Rule

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. 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 react-querybuilder dependency: 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. 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: For each 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 --env-file option. 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_EMAIL and 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 NYCSC_PGADMIN_EMAIL and NYCSC_PGADMIN_PASSWORD respectively. 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 cp_squirrels table. 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: cp_squirrels . 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. 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: 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: AND and OR . These options correspond to the AND and OR operators of a SQL statement's WHERE clause. 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 -73.9561344937861 . 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 AND operator: 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 WHERE clause: ( src/App.tsx ) 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: 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. Inside of 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 . ( src/App.tsx ) Anytime onQueryChange is invoked, the setUpdateQuery method will update the value of the updateQuery variable, which must adhere to the type RuleGroupType . Update the sendQuery function to send updateQuery to the /api/records endpoint and log the data in the response. ( src/App.tsx ) 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 Y (latitude). 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: ( src/App.tsx ) Press the "Send Query" button again. The result table (with only one record) should be displayed within a table. The best part is you can add other visualization components to display your fetched data. The sky's the limit! Click here for the final version of this project. Visit the React Query Builder to learn more about how you can customize it to your application's needs.