SQL Aide (SQLa) Typescript library optimized for emitting and packaging SQL

Note

This documentation is currently in early-alpha (experimental) stage. Please come back often or check updates in support/docs-astro to see when it’s updated.

If you have suggestions for improvements please make changes and send a pull request or open tickets to request explanations, clarifications, etc.

Welcome to SQLa (SQL Aide), an innovative SQL generation library designed to simplify the process of generating SQL statements in Typescript and beyond. SQLa uses the power of Template literals (Template strings).

SQLa is like a ”static site generator” for SQL and related database artifacts, allowing you to efficiently prepare, organize, and assemble SQL code and other database source code components. Similar to how a static site generator simplifies the process of creating static websites, SQLa streamlines the generation of SQL statements and related artifacts for SQL-heavy applications and services.

By preparing relational tables, columns, views, stored procedures, and stored functions in TypeScript using SQLa’s intuitive syntax and the Zod library, you can ensure consistency and reliability in your SQL and application source code generation workflow.

Just like a static site generator allows you to define templates and content that get transformed into static web pages, SQLa enables you to define templates and schemas that get transformed into SQL statements and other database artifacts that you can pull into your apps and services as libraries. It provides a structured approach to SQL generation, allowing you to compose SQL code in a consistent and maintainable manner.

SQLa leverages the capabilities of JavaScript functions and Template literals (Template strings) to prepare SQL components as composable building blocks, known as “SQL partials.” It eliminates the need for introducing a separate template language by utilizing a set of naming conventions and harnessing the full power of JavaScript (and TypeScript) template strings. These partials serve as the fundamental units for constructing SQL files and other database artifacts.

By adopting SQLa, you can benefit from:

• Code Reusability: SQLa promotes the creation of reusable SQL partials, enabling you to encapsulate common SQL components and efficiently build SQL statements for various scenarios.

• Type Safety: With the integration of TypeScript, SQLa ensures type safety throughout the SQL generation process. You can catch errors and enforce data type consistency at compile-time, reducing the likelihood of runtime issues.

• Deterministic Reproducibility: SQLa facilitates the creation of SQL code that is consistently reproducible. This characteristic is crucial when working with data pipelines and processes that require predictable and repeatable results.

• Expressive SQL Generation: Leveraging JavaScript (and TypeScript) template strings, SQLa allows for the full power and expressiveness of the language. You can incorporate dynamic logic, conditional statements, loops, and other programming constructs seamlessly into your SQL templates.

• Database Agnosticism: SQLa provides support for multiple SQL dialects, allowing you to generate SQL code that is compatible with various database systems. This flexibility enables you to work with different databases without significant modifications to your codebase.

With SQLa, you can streamline your SQL generation workflow, enhance code maintainability, and improve the overall development experience when working with SQL-heavy applications and services.

Key Features

• Typesafe Schema Definition: SQLa seamlessly integrates with the Zod library, enabling you to define database schemas in a Typesafe manner within your Typescript code. By leveraging Typescript’s static typing, SQLa catches errors early in the development process, ensuring the correctness of your SQL code and enhancing developer productivity.
• SQL Generation in Typescript, JavaScript, and via CLI: SQLa offers a command-line interface (CLI) that generates SQL templates, making it versatile beyond Typescript code. You can use the CLI to generate templates for tables, columns, views, stored procedures, and stored functions. This capability allows for easier management and organization of your SQL code, even outside of a Typescript codebase.
• App/Service Source Code Generation for any Language: SQLa can go beyond SQL to generate a wide range of artifacts that support your database-related workflows such as ORM specifications, database documentation, schema migration, etc. from a single “source of truth”.
• Multi-Dialect SQL Generation: SQLa provides a unified interface to generate SQL statements compatible with different SQL dialects. Whether you’re working with MySQL, PostgreSQL, SQLite, or other popular database systems, SQLa allows you to generate SQL code that adheres to the specific requirements of each target database.
• PostgreSQL psql Meta Commands Subset: SQLa supports a subset of PostgreSQL psql meta commands, enabling you to write simple SQL templates that utilize these commands. Regardless of the target database, you can employ these commands to handle common SQL tasks efficiently, such as importing data, executing scripts, or managing database connections.
• Full Power of Typescript and Javascript: For advanced SQL templates, SQLa empowers you to leverage the full power of Typescript and Javascript string template literals. This flexibility enables you to incorporate dynamic logic, conditional statements, loops, and other programming constructs into your SQL templates. Consequently, you can generate complex and customized SQL code tailored to the specific requirements of your application.

Supported SQL Dialects

SQLa supports a wide range of SQL dialects, including but not limited to:

• PostgreSQL
• SQLite
• Microsoft SQL Server
• DuckDB
• Clickhouse
• MySQL, Dolt (coming soon, looking for contributors)
• Oracle Database (coming soon, looking for contributors)

This allows you to write database-agnostic code using SQLa and effortlessly switch between different database systems without extensive modifications to your codebase.

Whether you’re a Typescript developer seeking type-safe SQL generation or someone working with SQL templates outside of a Typescript project, SQLa provides a comprehensive solution. The following sections of this documentation will guide you through the installation process, CLI usage, SQL template generation, and advanced features of SQLa. By using SQLa, you can streamline your SQL code generation workflow and enhance your development experience.

Note

SQLa was initially created for PostgreSQL Data Computing Platform (PgDCP)-based apps and services but is beneficial in other SQL-centric use cases for non-PostgreSQL-based databases.

Beyond SQL

In addition to its first-class support for generating SQL code, SQLa offers a versatile framework that enables the generation of various related text artifacts. With SQLa, you can go beyond SQL generation and leverage its capabilities to generate a wide range of artifacts that support your all database-related workflows in any target language, framework, or ecosystem.

ORM Definition Files

SQLa can be used to generate ORM (Object-Relational Mapping) definition files that serve as a bridge between your database and an ORM framework of your choice. By defining a consistent, well-documented schema using SQLa’s schema definition capabilities, you can generate ORM definition files that accurately reflect your database structure. These files provide a foundation for leveraging any ORM in any programming language, ensuring seamless integration between your application code and the database.

Configuration File Generation

SQLa’s powerful template generation capabilities can be used to generate configuration files for applications and services. By defining templates that incorporate environment-specific settings, SQLa can generate configuration files tailored to different deployment environments. This allows for efficient configuration management and ensures consistency across various instances of an application.

Polyglot Code Generation

SQLa’s template generation capabilities can extend beyond SQL code and be utilized for generating code in other programming languages. By defining templates that incorporate language-specific syntax and conventions, SQLa can generate boilerplate code, data structures, or other repetitive code patterns. This enables rapid prototyping, code scaffolding, and reduces manual effort in generating code components.

Database Documentation

SQLa can generate comprehensive database documentation by leveraging its schema definition and SQL generation features. By annotating your SQLa schema with descriptive comments and generating documentation files based on the schema, you can easily produce well-documented and up-to-date documentation for your database. This documentation can serve as a valuable resource for developers, database administrators, and other stakeholders involved in understanding and maintaining the database structure.

Migration Files

SQLa’s SQL generation capabilities can be used to generate migration files that facilitate database schema changes and version control. By defining your database schema using SQLa’s schema definition syntax and utilizing SQLa’s migration generation features, you can automate the generation of migration files that capture the necessary SQL statements to migrate your database from one version to another. These migration files enable seamless and consistent database schema evolution, ensuring smooth deployments and version management.

Data Seeder Files

SQLa can also assist in generating data seeder files that provide initial or sample data for your database. By utilizing SQLa’s schema definition and SQL generation capabilities, you can define data seeder templates that populate your database with pre-defined data. These seeder files can be executed during the database setup or as part of a data import process, allowing you to bootstrap your database with consistent and meaningful data.

The flexibility of SQLa allows you to adapt its text generation capabilities to suit your specific needs outside of pure SQL and for any target programming language(s). By harnessing SQLa’s ability to generate an unlimited range of text artifacts, you can streamline your development workflows, maintain consistency across different tools and frameworks, and ensure the reliability and accuracy of your database-related processes.

Type-safe Test Data Generation

With SQLa’s schema definition and data generation features, you can generate test data for your application or service. By defining templates that describe the desired data structure and utilizing SQLa’s data generation functions, you can automatically generate synthetic or randomized test data that simulates real-world scenarios. This assists in comprehensive testing and ensures data integrity across different testing environments.

Data Validation

SQLa’s integration with the Zod library enables powerful data validation capabilities. You can use SQLa to define Zod schemas for validating input data, API payloads, or user-submitted data. By leveraging SQLa’s schema definition and validation features, you can ensure that the data being processed by your application meets the required criteria, improving data quality and integrity.

Data Transformation

SQLa’s schema definition and data manipulation capabilities make it a useful tool for data transformation tasks. You can define transformation rules using SQLa’s schema and leverage its data manipulation functions to convert, reshape, or aggregate data. This enables streamlined data transformation processes and supports data integration and ETL (Extract, Transform, Load) workflows.

Data Export and Import

SQLa can be utilized to generate export and import scripts for data migration or synchronization purposes. By defining templates that capture the necessary SQL statements for exporting or importing data, SQLa can generate scripts that enable seamless data transfer between different systems or databases. This simplifies the process of data migration or synchronization tasks.

Data Validation Rules Generation

SQLa can assist in generating data validation rules for various purposes. By defining templates that incorporate validation rules, SQLa can generate code snippets or configuration files that enforce data validation on different platforms or frameworks. This promotes consistent validation practices and reduces the effort required to implement and maintain data validation rules.

Test Coverage