Directory Structure

Note

This guide describes the directory of the SQLa source repo and none of the structures are imposed on your own SQL or Typescript files. You are free to follow any structure conventions.

Important Top-level Directories

• SQLa Repo
  • examples/
  • lib/
  • pattern/
  • render/
  • support/
  • deps-test.ts
  • deps.ts

Examples

• SQLa Repo
  • examples

The examples directory in the SQLa source code repository serves as a valuable resource for developers looking to explore and understand the capabilities of SQLa (SQL Aide). It contains a collection of practical examples that demonstrate various aspects of SQLa’s functionality, showcasing its power and flexibility in generating SQL code.

Each example within the directory focuses on a specific use case, scenario, or feature of SQLa, providing hands-on guidance and illustrating how to leverage SQLa effectively in real-world scenarios. Whether you are new to SQLa or an experienced user, the examples offer valuable insights and serve as a reference to help you kickstart your SQL generation workflow.

By examining the examples, you can gain a deeper understanding of how to define database schemas, generate SQL statements using SQL templates, handle advanced SQL features, incorporate TypeScript and JavaScript logic, and more. The examples cover a range of topics, including table definitions, column specifications, relationships, views, stored procedures, and more.

To get started, navigate to the examples directory in the SQLa source code repository. Each example is accompanied by clear explanations, comments, and code snippets that walk you through the implementation. Feel free to explore, modify, and experiment with the provided examples to enhance your understanding of SQLa and its capabilities.

By leveraging the examples directory, you can accelerate your learning journey with SQLa and unlock its full potential for SQL generation. Start exploring the examples today to discover the power of SQLa and streamline your SQL code generation process.

Lib

• SQLa Repo
  • lib
    • osquery
    • pre-process
      • psql
    • sql
      • pg
      • sqlite
    • universal

The lib directory in the SQLa source code repository encompasses several subdirectories that contribute to the functionality and versatility of SQLa (SQL Aide). Each subdirectory serves a specific purpose and enhances different aspects of SQL generation and database-related tasks. Let’s explore the subdirectories within the lib directory:

osquery

The osquery subdirectory hosts modules and utilities that facilitate seamless integration with osquery, an open-source SQL-powered operating system instrumentation tool. The osquery subdirectory within the SQLa source code repository hosts modules and utilities that facilitate seamless integration with osquery, an open-source SQL-powered operating system instrumentation tool. This subdirectory provides specialized functionality for working with osquery and offers features such as Automatic Table Construction (ATC) configuration file generation capabilities.

pre-process

The pre-process subdirectory contains a PostgreSQL psql pre-processor, which excels at handling meta commands like \set and \include. This pre-processor significantly enhances SQLa’s capabilities by providing psql template features to databases that lack such functionality, such as SQLite, DuckDB, Clickhouse local, and Dolt. By leveraging the pre-processor, you can enjoy advanced features like variable substitution and file inclusion, improving your SQL generation workflow across different database systems.

sql

The sql subdirectory is a repo of reusable SQL files, schemas, and objects. These resources cater to various purposes and support different SQL dialects. You can utilize this directory as-is for different database dialects, or you can import its contents into any SQLa TypeScript project through local file access or web URLs. By leveraging this extensive library, you can save time and effort by leveraging pre-defined SQL code and schema structures for your SQL generation needs.

universal

The universal subdirectory houses TypeScript modules that offer universal functionality and can be seamlessly integrated into any project. While primarily included in this repository for convenience, these modules can be utilized across diverse projects to enhance development efficiency. The universal subdirectory provides a set of reusable TypeScript modules that transcend specific database systems or SQL dialects, ensuring flexibility and versatility in your SQLa projects.

By organizing functionality into these subdirectories, the lib directory fosters a modular and reusable architecture. Each subdirectory addresses specific needs and empowers you to work with different aspects of SQL generation, osquery integration, pre-processing, and cross-database compatibility.

As you explore the lib directory, you can leverage the resources within each subdirectory to enhance your SQL generation workflows. The sql subdirectory, in particular, offers a convenient library of reusable SQL files, schemas, and objects, adaptable to different database dialects. The universal subdirectory provides TypeScript modules that can be seamlessly integrated into any project for added convenience and flexibility.

Take advantage of the resources within the lib directory to streamline your SQL generation process, enhance cross-database compatibility, and improve overall development efficiency.

Pattern Directory

• SQLa Repo
  • pattern
    • configuration
    • data-vault
    • observability
    • typical

The pattern directory in the SQLa source code repository serves a distinct purpose compared to the examples and lib directories. While the examples directory is primarily for learning and exploration, and the lib directory contains code that can be used as-is or built upon, the pattern directory focuses on providing underlying models and templates that need to be assembled and composed with consumer libraries to be useful. Let’s delve into the details:

The pattern directory houses a collection of subdirectories that offer structured and reusable patterns for various SQLa features, configurations, observability components, and more. These patterns serve as foundational building blocks, providing standardized models, schemas, and SQL objects that can be tailored and composed to meet specific requirements.

Unlike the examples directory, which offers self-contained examples for learning purposes, the pattern directory emphasizes the assembly and composition of patterns with consumer libraries. Patterns are not meant to be used in isolation but rather combined with other libraries and customizations to create robust and tailored solutions. They provide a starting point and a set of best practices that developers can leverage to accelerate their development process.

Similarly, the pattern directory differs from the lib directory in that it focuses on providing underlying models rather than ready-to-use code. While the lib directory offers reusable code and modules that can be used as-is or extended, the pattern directory provides foundational patterns that need to be combined with consumer libraries, configurations, and code to be fully utilized. These patterns establish the structure, rules, and conventions that enable consistent and maintainable SQL generation workflows.

By organizing resources into the pattern directory, SQLa promotes a modular and composable approach to SQL generation. Each subdirectory within the pattern directory represents a specific pattern, offering standardized models, schemas, and SQL objects that can be assembled, customized, and integrated with consumer libraries to create powerful and tailored SQLa implementations.

When working with the pattern directory, developers should leverage these patterns as foundational building blocks and combine them with their own libraries, configurations, and code to create comprehensive SQL generation solutions that meet their specific needs. The patterns provide a consistent and reliable starting point, enabling developers to focus on the higher-level customization and implementation details to achieve their desired SQL generation workflows.

configuration

The configuration directory contains a set of SQLa feature toggles, feature flags, and configuration information models, schemas, and SQL objects. These resources enable flexible configuration management within SQLa, allowing you to customize the behavior and options of the library according to your specific requirements. Feature toggles and feature flags are mechanisms used in software development to control the availability and behavior of certain features. They provide the ability to enable or disable specific features dynamically at runtime or execute specific code based on configuration settings.

data-vault

The data-vault directory provides a pattern for SQLa Data Vault information models, schemas, and SQL objects. Data Vault is a data modeling and methodology approach designed to handle large-scale data integration and historical data storage. It provides a scalable and flexible solution for data warehousing and analytics.

SQLa incorporates the Data Vault methodology by generating SQL code for consistent Data Vault concepts such as hubs, satellites, links, and other related entities. By utilizing the Data Vault pattern in SQLa, you can efficiently implement Data Vault structures in your SQL generation workflows. SQLa takes care of generating the required SQL code, ensuring consistency and adherence to Data Vault principles. This allows you to focus on the logical design and transformation of your data, while SQLa handles the underlying SQL generation for Data Vault structures.

observability

The observability directory encompasses a pattern for SQLa telemetry, metrics, and observability information models, schemas, and SQL objects. These resources enable you to integrate observability components into your SQLa projects, capturing valuable insights into the performance, health, and behavior of your SQL code. By adopting the observability pattern, you can enhance monitoring, troubleshooting, and optimization of your SQLa-based applications.

typical (Most Important)

The most significant subdirectory within the pattern directory is the typical directory. It contains a comprehensive pattern called “typical” that serves as a foundation for SQLa’s built-in patterns and is designed to be used by all consumers of the SQLa library. The typical pattern encompasses SQLa governance rules, SQL domains, table types, information models, schemas, and SQL objects. It provides a robust and standardized set of resources that ensure consistency, maintainability, and best practices across SQLa projects.

By organizing resources into these subdirectories, the pattern directory facilitates a modular and reusable architecture within SQLa. Each subdirectory represents a specific pattern, offering pre-defined SQLa components that align with common use cases and industry best practices. These patterns enable you to leverage SQLa’s features, configurations, observability components, and more, seamlessly within your projects.

When working with the pattern directory, developers should leverage these patterns as foundational building blocks and combine them with their own libraries, configurations, and code to create comprehensive SQL generation solutions that meet their specific needs. The patterns provide a consistent and reliable starting point, enabling developers to focus on the higher-level customization and implementation details to achieve their desired SQL generation workflows.

Render Directory

• SQLa Repo
  • render
    • ddl
      • table
    • diagram
    • dialect
      • pg
    • dml
    • domain
    • dql
    • emit
    • pl
    • graph.ts

The render directory in the SQLa source code repository contains the core SQL generation TypeScript modules. It serves as the foundation for SQLa’s SQL generation capabilities, providing essential tools for composing and generating SQL statements. Let’s explore the subdirectories within the render directory in alphabetical order:

diagram

The diagram subdirectory hosts modules that enable the generation of various types of diagrams, including PlantUML diagrams. Diagrams provide visual representations of database structures, relationships, and other SQLa components. By utilizing the modules within the diagram subdirectory, you can generate PlantUML diagrams and other visual representations that aid in understanding and communicating the architecture of your SQLa projects.

dialect

The dialect subdirectory contains modules that cater to dialect-specific objects and features. Different database systems often have their own unique syntax and functionality. The modules within the dialect subdirectory provide support for dialect-specific objects, such as PostgreSQL stored procedures and stored functions. This ensures compatibility and enables the generation of SQL code that aligns with the requirements of specific database systems.

DDL

The ddl subdirectory contains modules that focus on SQL Data Definition Language (DDL) operations. DDL statements are responsible for defining and modifying the structure of database objects such as tables, views, indexes, and constraints. The modules within the ddl subdirectory facilitate the generation of SQL statements for creating, altering, and dropping database objects, allowing you to effectively manage the schema and structure of your database.

DML

The dml subdirectory encompasses modules that handle SQL Data Manipulation Language (DML) operations. DML statements are responsible for inserting, updating, and deleting data within the database. The modules within the dml subdirectory facilitate the generation of SQL statements for manipulating data, allowing you to efficiently perform data modifications and updates.

domain

The domain subdirectory serves as a mapping layer between Zod types (defined using the Zod library) and SQL domains, including data types and column types. The modules within the domain subdirectory enable seamless mapping between Zod types and the appropriate SQL domain definitions. This ensures consistency and accuracy when generating SQL statements based on the defined types and domains.

emit

The emit subdirectory contains modules responsible for the core SQLa composition work. These modules leverage the power of TypeScript’s string template literals to compose and generate SQL statements. The emit functionality within the emit subdirectory plays a crucial role in assembling the SQLa components, allowing for dynamic and flexible SQL generation based on predefined templates and models.

pl (Procedural Language)

The pl subdirectory encompasses modules related to procedural languages. Procedural languages, such as SQL stored procedures and stored functions (also known as stored routines), enable the creation of reusable code blocks within the database. The modules within the PL subdirectory provide the necessary tools to generate SQL code for procedural language constructs, allowing you to define and manage complex business logic and database operations. These modules form the foundation for generating SQL stored procedures and stored functions in SQLa, empowering you to implement custom logic and encapsulate frequently used operations within your SQL generation workflows.

The render directory, with its various subdirectories, provides a comprehensive set of tools and functionality for SQL generation in SQLa. Each subdirectory focuses on a specific aspect of SQL operations, allowing you to effectively manage database structures, compose queries, perform data manipulations, work with dialect-specific features, generate diagrams, and more. By utilizing the modules within the render directory, you can streamline your SQL generation workflow and build robust SQLa projects.