Introducing a new follow-along series from CTO Nathan Northcutt
Looking for a TypeScript SQL Parser that can:
- Express database schemas through both types and builders that provide concrete implementations
- Build and verify a SQL query against a schema to ensure valid, compile time queries.
- Interpret raw SQL strings as queries that can be validated against a schema
- Extend the parsing to provide extensions for SQL engine variants
- Identify changes in schemas and provide migrations between versions
All in a self contained library that does not have any external dependencies?
So were we. Learn along with us!
Diving Deeper into TypeScript with SQL Parsing
This project was chosen as a deep dive into the capabilities of TypeScript through a project that would leverage its unique strengths as well as the author’s domain expertise. SQL parsing is a perfect fit for TypeScript’s extensible type system and compile-time feedback. Plus, there are many existing projects to draw inspiration from and gaps to fill.
TypeScript excels at compile-time types and flexibility, but there are limited existing solutions or resources for building them. The goals above are a challenge that will push the limits of this powerful language and deepen our understanding of its capabilities.
Follow along in Nathan’s series of articles and the GitHub repository.
Key Concepts
Abstract Syntax Trees (AST)
An AST represents structural components (expressions, programs, etc.) in a general form, processed independently of the original form. Each node has a specific type and can have nested nodes, forming a tree structure.
SQL Basic Types
We’ll start with basic SQL types and translate them into TypeScript in a types.ts file. We’ll define supported column types and classify columns by their database use or TypeScript impact. Conditional types will map these columns to their TypeScript equivalents.
Conditional Types
Conditional types in TypeScript allow us to ask simple relational questions between types. They’re powerful but come with caveats, like depth limits and the need to ask simple questions.
Defining a SQL AST
Our AST will represent various SQL components. For example, SELECT statements can have complex logic, but we can represent most scenarios with a few simple concepts and recursive types.
Recursive Types
Recursive types in TypeScript allow a type to reference itself, either directly or through a circular dependency. This is useful for our AST but requires careful handling to avoid infinite recursion and circular references.
Type Inference
TypeScript infers types based on context, which is helpful but has limitations. We should avoid using the any keyword and provide explicit hints to the compiler to narrow valid types.
AST Types
Our AST types will be split into different files to manage complexity. We’ll use string unions for join types and column aggregation methods to allow extension while supporting common use cases.
Next Steps
We’ve defined simple types, an AST, and some goals for our library. We explored TypeScript concepts like conditional and recursive types, generics, and inference, along with potential pitfalls. Next, we’ll define a simple database schema and start building and parsing queries.
Check out the full article to go in depth with code samples:
Or go straight for the repository: