CG/SQL: Easy, accurate code generation for SQLite

What it is: CG/SQL is a code generation system for the popular SQLite library that allows developers to write stored procedures in a variant of Transact-SQL (T-SQL) and compile them into C code that uses SQLite’s C API to do the coded operations. CG/SQL enables engineers to create highly complex stored procedures with very large […]

ME

Meta EngineeringMeta Engineering

·October 8, 2020·Open Source

CG/SQL: Easy, accurate code generation for SQLite

A compiler-backed layer over SQLite

CG/SQL is a code generation system for SQLite that lets developers write stored procedures in a dialect of Transact-SQL (T-SQL) and compile them into C code using SQLite's C API. The approach is intended to remove the manual, error-prone checking that hand-written data access layers typically require, especially when dealing with large or complex queries.

Beyond procedure compilation, the toolset covers schema management and upgrades, test code generation for procedures, retrieval of query plans at compile time, and bindings for other languages such as Java and Objective-C. A JSON output format makes it possible to build additional analysis or interfacing tools, and the package includes thorough documentation on both the language and the system itself.

How the compiler enforces correctness

The CQL compiler is the core of the system. It reads the schema and procedure definitions, applying strong typing and a large catalog of compile-time errors designed to catch SQL issues before runtime. The compiler tracks both variable types and schema column types, flagging mismatches such as assigning a nullable column to a non-nullable output variable. It also ensures that all SQLite API usage is consistent and correct.

Generated code always checks return codes from SQLite calls and handles column ordinals and types precisely when binding or reading data. These are areas where mistakes are common and easy to introduce over time.

Schema annotations let the system automatically produce stored procedures that upgrade a database from any prior schema version to the current one, with many sanity checks built in. Procedure-level annotations can request supporting test code that creates schema fragments and inserts sample data, allowing unit tests to run without depending on a deployed environment. Similar facilities generate schema for checking query plans during compilation.

Why this approach matters

SQLite is ubiquitous, but building a reliable, maintainable data access layer on top of it is rarely straightforward. Many teams turn to code generation to avoid manually updating dozens of column ordinals every time a schema changes, yet such generators can themselves be error-prone. CQL's strong typing and syntax helpers aim to make complex stored procedures easier to write correctly in the first place and to keep correct as they evolve.

For instance, a procedure that inserts into any table can be written with arguments derived directly from that table's columns:

create procedure insert_a_row(like your_table)
begin
  insert into your_table from arguments;
end;

This form makes it impossible to omit a column or place arguments in the wrong order, even when the table has dozens of them. The syntax helpers translate such concise, safer constructs into canonical SQL, letting developers write less code that still runs everywhere SQLite does. Combined with the unit testing facilities, this gives engineers a stronger guarantee that intricate database logic behaves as intended.