Type-Level SQL Parser in TypeScript

This challenge asks you to build a SQL parser that operates entirely at the TypeScript type level. Instead of parsing strings at runtime, you will define types that represent SQL statements, allowing for compile-time validation and analysis of SQL queries. This is useful for creating strongly-typed database interfaces, preventing SQL injection vulnerabilities, and enabling advanced static analysis of your data access layer.

Problem Description

Your task is to design and implement a set of TypeScript generic types that can parse and represent a subset of SQL SELECT statements. The parser should be able to infer the structure of the query, including the selected columns, the table being queried, and any WHERE clauses.

Key Requirements:

1. Parsing SELECT Statements: The primary goal is to parse SELECT statements.
2. Column Selection: Be able to identify and represent the columns being selected.
3. Table Identification: Correctly identify the table from which data is being selected.
4. WHERE Clause Support: Parse simple WHERE clauses involving equality (=), inequality (!=), greater than (>), less than (<), greater than or equal to (>=), and less than or equal to (<=) comparisons. Support for AND and OR logical operators within the WHERE clause is required.
5. Type Safety: All parsing and representation should happen at the type level, meaning no runtime execution or string manipulation is involved beyond the initial type definition.
6. Error Handling (Implicit): Invalid SQL syntax should result in TypeScript compilation errors (e.g., type mismatches).

Expected Behavior:

Given a type representing a SQL SELECT statement, the parser should expose inferred types that accurately reflect the query's structure. For example, it should be possible to get a type representing the list of selected columns and the table name.

Edge Cases:

• Queries with no WHERE clause.
• Queries with multiple conditions in the WHERE clause, combined with AND and OR.
• Different data types for column values in WHERE clauses (numbers, strings, booleans).
• Case sensitivity of keywords (assume keywords are case-insensitive for parsing, but this might be simplified to case-sensitive for the type-level challenge).

Examples

Example 1: Simple SELECT

// Input type representing the SQL query
type SelectUsersQuery = ParseSQL<"SELECT name, email FROM users">;

// Expected Output (inferred types)
// We expect SelectUsersQuery to be something like:
// {
//   columns: ["name", "email"];
//   from: "users";
//   where: undefined;
// }

Example 2: SELECT with WHERE Clause

// Input type representing the SQL query
type SelectActiveAdminsQuery = ParseSQL<"SELECT id, username FROM admin_users WHERE status = 'active' AND role = 'admin'">;

// Expected Output (inferred types)
// We expect SelectActiveAdminsQuery to be something like:
// {
//   columns: ["id", "username"];
//   from: "admin_users";
//   where: {
//     operator: "AND";
//     conditions: [
//       { column: "status", operator: "=", value: "active" },
//       { column: "role", operator: "=", value: "admin" }
//     ];
//   }
// }

Example 3: SELECT with Numeric WHERE Clause and OR

// Input type representing the SQL query
type SelectRecentOrdersQuery = ParseSQL<"SELECT order_id, total FROM orders WHERE amount > 100 OR customer_id = 55">;

// Expected Output (inferred types)
// We expect SelectRecentOrdersQuery to be something like:
// {
//   columns: ["order_id", "total"];
//   from: "orders";
//   where: {
//     operator: "OR";
//     conditions: [
//       { column: "amount", operator: ">", value: 100 },
//       { column: "customer_id", operator: "=", value: 55 }
//     ];
//   }
// }

Constraints

• The input SQL query will always be a string literal type.
• The supported SQL subset is limited to SELECT <column_list> FROM <table> [WHERE <condition>].
• Column names and table names will consist of alphanumeric characters and underscores.
• String literals in WHERE clauses will be enclosed in single quotes (').
• Numeric literals in WHERE clauses will be standard integers.
• Boolean literals are not explicitly supported for now, assume string/number comparisons.
• The parser does not need to handle complex SQL features like JOIN, GROUP BY, ORDER BY, LIMIT, subqueries, or aggregate functions.
• Focus on correctness and type-level manipulation. Performance is not a primary concern for this type-level challenge.

Notes

This challenge requires advanced TypeScript generics, template literal types, and conditional types. You will likely need to break down the parsing process into smaller, manageable type-level functions for different parts of the SQL query (e.g., parsing columns, parsing the FROM clause, parsing the WHERE clause).

Consider how you will represent the different components of the SQL query in your output types. For WHERE clauses, think about how to represent the logical operators (AND, OR) and the individual comparison conditions.

A good starting point is to parse the string into its main clauses: SELECT, FROM, and WHERE. Then, recursively parse each clause.