QueryPie Development #6: QueryPie Architecture Configuration

Creating a single visible product requires a lot of invisible touches. When it comes to software development, the product is essential, but how the product is implemented is more important in many ways.

As mentioned in the two previous posts (Searching for my own Database IDE, QueryPie: a fresh design for SQL IDE), QueryPie is a database IDE that operates on three operating systems: Mac, Windows, and Linux. In addition to supporting database security solutions with QueryPie Protocol, we are planning an integrated data platform that will enable us to analyze and visualize data like Business Intelligence (B.I) on the web.

Because we planned to support all these functions as a single solution, the software's configuration and design were essential to establish before the development.

For applications installed and used on a PC/Laptop, programming languages that support multi-operating system compilation and run-time were essential. UI Framework was also a significant factor in providing convenient and excellent UI/UX.

So all of the development team members, including myself, have taken into account the technical capabilities and experiences that the company has and our mid-to-long term product road map. This article covers the story behind the development of QueryPie.

1. QueryPie UI (Front-end) : Electron, TypeScript, React


Electron is a framework that enables desktop applications to be developed on Mac, Windows, and Linux. It is open-source, developing business logic as JavaScript (or TypeScript) and UI/UX areas are HTML/CSS with high extensibility and excellent visual implementation. Also, since NodeJS and NPM ecosystems can be used as they are, it is possible to secure development scalability through various libraries.

Products with a high amount of established users such as VSCode, Skype, Slack, and Atom, guarantee the completeness and stability of Electron.

2. QueryPie Engine (Back-end) : OpenJDK & Java & JDBC

OpenJDK-based Java and JDBC Library

Because we decided to use Electron for our front-end, we considered NodeJS for the back-end development. But we changed our plan because of these factors:

  1. The Database Connectivity Library in NodeJS is not as mature as JDBC
  2. The Java EcoSystem is appropriate for stable implement of key functions such as simultaneous SQL Execution, Data Export/Import, and more

So we decided to use OpenJDK-based Java and JDBC libraries for our back-end development.

3. Communication between Processors : gRPC

Front-end and back-end are run as separate processors, so stable communication between the two processors is required. While general web applications communicate via HTTP/Web Locket, QueryPie has chosen gRPC (a TCP/IP-based RPC framework) because it operates on a user’s local machine, and application characteristics require fast response and reliable communication.

grpc에 대한 이미지 검색결과
gRPC Workflow (https://www.slideshare.net/shijucv/building-high-performance-apis-in-go-using-grpc-and-protocol-buffers)

gRPC is an open-source RPC Framework that is available from Google and delivers data through Google Protocol Buffer-based Binary Protocol. Protocol Buffer is one way to serialize structured data, and by defining the Interface Description Language (IDL) Proto, it automatically generates serialized models in various languages. This way, users can easily understand what data is sent and received without any separate documentation.

Google Protocol Buffer (https://blog.grijjy.com/2017/04/25/binary-serialization-with-google-protocol-buffers/)

4. Overall Architecture

Using Electron, Java, JDBC, and gRPC, we’ve configured QueryPie’s overall architecture as follows:

QueryPie Overall Architecture

5. Engine Exception Handler: Error Tracking

No matter how high test coverage and meticulous Q/A were done in the product development phase, it isn't easy to thoroughly test all environments. This includes hardware, OS, and OS patches for customers using the product. So it is essential to detect and reproduce the errors that occur quickly.

One of the most important aspects of engine development is the accurate and detailed recording of errors throughout the engine. If an error occurs in the product, it can be reproduced 100% or quickly within the company’s internal environment.

We used gRPC Interceptor, SLF4J MDC, and Logback Custom Appender to track in detail what user behavior instigated the error and what the situation was like at the time.

As previously described in the architecture, QueryPie Middleware requests the engine for the required data via gRPC to handle user behavior or requests. To track which gRPC Endpoint was called by Middleware, the gRPC Interceptor always has API Request Information on MDC, which is retrieved from the Logback Appender when an error occurs.

Tracking the Engine Error Occurrence Process

To track all error logs, we added a QueryPieLogAppender to the ROOT Appender so we could record all log.error (which is taken via SLFJ4). This error log information is sent to the server with the user’s consent, and the server quickly detects and reproduces errors based on the log data to resolve the problem.

6. Application Performance Monitoring: Skywalking

Finally, the performance measurement of the application remains. This should be a preconceived idea because it’s much easier to start measuring the performance of a request from the development process than to track it at once after the development is complete.

Consideration is required for APM behavior from the beginning of the development process because some of the features in QueryPie have some aspects that might be slow or take much longer than expected once tested, which might not be foreseen during development.

If all developers flew an APM-linked engine locally, they wouldn’t be able to identify the exact metric. We decided to activate the Engine on the Remote Server and configure it to run with the APM.

QueryPie Remote Engine

If Git Push occurs, Jenkins automatically builds the engine and distributes it to the remote server and restarts it. So front-end and middleware developers can access the Remote Engine without having to run an engine locally. All testing and execution records during development are recorded in the APM database. Among the many APMs out there, we chose the Apache Incubation Project called Sky Walking, which has simple installation and clean UI, topology maps, and gRPC support and run-time tracking.

Function to inform the endPoint-specific Reponse Time in Skywalking
Topology maps allow analysis of where requests are being delivered

We are continually finding effective ways to quickly find and improve bottlenecks by analyzing them at the development stage with APM application. And we hope that these processes will enable us to operate with high quality and superior performance when the product is delivered to the users.

In Conclusion:

QueryPie is gaining momentum every day. We are continually working with our best engineers to build a cornerstone with the right technology, improve usability, and solve problems that traditional users find uncomfortable. Quite a few key features have been established, and we’ve successfully been able to run the product in-house. We can’t wait to see how our users will use and enjoy these features!

To become the world’s leading database tool, QueryPie is actively developed even as you read this development log. Please wait a bit more for this great experience!