How filipeesch/dynamic-sql works
Project Type and Purpose
• This is a library that provides a new approach to dynamically compose SQL queries. Its design focuses on performance and type-safe parsing of SQL with support for conditional rendering, automatic parameter generation, and custom segment rendering.
• The README explains how developers can use special << >> tags, conditional operators, and interpolated strings to build SQL queries. As a result, the primary consumers of this library will be other .NET applications that need flexible, high-performance SQL generation.
File Structure for Architectural Insights
• The top-level contains several projects:
• DynamicSQL – the core library with subdirectories like Compiler, Parser, and Extensions.
• DynamicSQL.Sample – a sample implementation showing how to use the library.
• DynamicSQL.Tests – unit tests to verify functionality.
• DynamicSQL.Benchmark and DynamicSQL.PerformanceTests – modules for performance evaluations.
• Inside the DynamicSQL project, there are distinct submodules:
a. Compiler: Contains classes for compiling the SQL (e.g., CodeCompiler, StatementCompiler, StatementProcessor) which compile the parsed SQL into native code.
b. Parser: Includes parsers and expression evaluation logic (various Expression classes) that interpret the custom SQL string interpolation syntax.
c. Extensions: Implements helper methods for ADO.NET, asynchronous enumeration, and string manipulations.
d. Supporting utilities such as segment rendering and output readers.
• The presence of GitHub workflows indicates continuous integration and publishing pipelines.
Main Components of the System (to include
a. Client/Consumer Application:
• Represents the calling application (e.g., in the Sample).
• Provides input in the form of interpolated SQL queries that include conditional and dynamic segments.
b. DynamicSQL Library:
• Parser Module:
• Processes the interpolated SQL string.
• Contains classes like StatementParser, ExpressionStack, and various Expression types that handle specific tokens (e.g., ConditionalExpression, InterpolationExpression).
• Compiler Module:
• Uses components like StatementCompiler and CodeCompiler to convert parsed results into executable native code.
• Handles caching of compiled statements for high performance.
• Renderer/Segment Module:
• Responsible for segment rendering (SegmentRenderer, SegmentRendererContext) that assembles SQL parts.
• Extensions:
• Provides ADO.NET and other helper methods to facilitate query execution (AdoNetExtensions, AsyncEnumerableExtensions).
• Utilities:
• Various helpers (e.g., OutputReader, ReaderHelper, ValueStringBuilder) that support the build and execution of dynamic queries.
c. Database (External System):
• Represents the target SQL database that executes the dynamically created queries.
Relationships and Interactions
• Directional data flow starts at the client application, which sends an interpolated SQL string to the DynamicSQL library.
• The Parser module tokenizes and parses the input string into an intermediate representation.
• Next, the Compiler module compiles this representation into native code tailored for performance.
• The Renderer assembles the final SQL string along with properly generated SQL parameters.
• Finally, the application uses the Extensions (e.g., ADO.NET helpers) to send the generated SQL command to the database, which returns the results.
Architectural Patterns and Design Principles
• Separation of concerns: Distinct modules for parsing, compiling, and rendering.
• Pipeline architecture: Sequential processing from SQL string input → parsing → compilation → execution.
• Caching and performance optimization: The compiler compiles SQL to native code and caches the result to speed up subsequent executions.
• Extensibility: The design supports custom segment rendering and conditional logic, making it flexible for complex SQL query scenarios.