📘 Documentation `sqlgg`

🧭 Contents

📘 Documentation sqlgg

🆕 Feature Review

Features are listed from latest to earliest, with detailed descriptions and examples.

Column-level Customization for INSERT/UPDATE Operations

Added: May 2025

Extends column-level customization to INSERT and UPDATE statements using the same custom modules as query parameters.

🔹 Example Schema and Queries

CREATE TABLE users (
  -- [sqlgg] module=UserId
  id INT AUTO_INCREMENT PRIMARY KEY,
  -- [sqlgg] module=UserName
  name VARCHAR(255) NOT NULL,
  -- [sqlgg] module=UserEmail
  email VARCHAR(255) UNIQUE,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- @create_user
INSERT INTO users (name, email) 
VALUES (@name, @email);

-- @update_user_email
UPDATE users 
SET email = @new_email 
WHERE id = @user_id;

-- @update_user_profile
UPDATE users 
SET name = @name, email = @email 
WHERE id = @user_id;

🔹 Generated OCaml (Excerpt)

let create_user db ~name ~email =
  let set_params stmt =
    let p = T.start_params stmt 2 in
    T.set_param_Text p (UserName.set_param name);
    T.set_param_Text p (UserEmail.set_param email);
    T.finish_params p
  in
  T.execute db 
    "INSERT INTO users (name, email) VALUES (?, ?)" 
    set_params

let update_user_email db ~new_email ~user_id =
  let set_params stmt =
    let p = T.start_params stmt 2 in
    T.set_param_Text p (UserEmail.set_param new_email);
    T.set_param_Int p (UserId.set_param user_id);
    T.finish_params p
  in
  T.execute db 
    "UPDATE users SET email = ? WHERE id = ?" 
    set_params

let update_user_profile db ~name ~email ~user_id =
  let set_params stmt =
    let p = T.start_params stmt 3 in
    T.set_param_Text p (UserName.set_param name);
    T.set_param_Text p (UserEmail.set_param email);
    T.set_param_Int p (UserId.set_param user_id);
    T.finish_params p
  in
  T.execute db 
    "UPDATE users SET name = ?, email = ? WHERE id = ?" 
    set_params

🔹 Module Requirements

Uses the same set_param functions as for query parameters - no additional implementation needed.

→ See Column-level Customization for Query Parameters for module implementation details

→ PR #199

Column-level Customization for Query Parameters

Added: May 2025

Extends the existing column-level customization to query parameters (@param), using the same [sqlgg] annotations from table definitions. Now custom modules are consistently applied to both input parameters and output columns.

🔹 Supported Parameter Forms

Form	Example
`col = @param`	`WHERE id = @id`
`col IN @param`	`WHERE name IN @names`
`(cols...) IN @param`	`WHERE (id, type) IN @pairs`

Note: Column metadata propagation applies in any SQL clause (SELECT, WHERE, HAVING, JOIN, etc.), not just in WHERE. The customization works whenever a column reference appears in the expression, as metadata is propagated from the column definition to the parameter.

CREATE TABLE example (
  -- [sqlgg] module=ExampleId  
  id INT AUTO_INCREMENT PRIMARY KEY,
  -- [sqlgg] module=Name
  name VARCHAR(255) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE example2 (
  id INT AUTO_INCREMENT PRIMARY KEY,
  -- [sqlgg] module=Name2
  name_2 VARCHAR(255) NOT NULL
);

-- @complex_query
SELECT example2.id, name_2 
FROM example
JOIN example2 ON example.id = example2.id
WHERE name IN @name AND example2.name_2 IN @name_2 AND example.id = @id;

🔹 Generated OCaml (Excerpt)

let complex_query db ~name ~name_2 ~id callback =
  let invoke_callback stmt =
    callback
      ~id:(T.get_column_Int stmt 0)
      ~name_2:(Name2.get_column (T.get_column_string stmt 1))
  in
  let set_params stmt =
    let p = T.start_params stmt (1 + (match name with [] -> 0 | _ :: _ -> 0) + (match name_2 with [] -> 0 | _ :: _ -> 0)) in
    (* ... params handling ... *)
    T.set_param_int64 p (ExampleId.set_param id);
    T.finish_params p
  in
  T.select db 
    ("SELECT example2.id, name_2 FROM example JOIN example2 ON example.id = example2.id WHERE ... ") 
    set_params invoke_callback

🔹 Module Requirements

module ExampleId = struct
  (* ... *)
  (* Transforms domain type to SQL int64 type *)
  let set_param (x : custom_id_type) : int64 = 
    (* Convert from domain type to SQL type *)
    Int64.of_int (CustomId.to_int x)
end

module Name = struct
  (* ... *)
  (* Transforms domain type to SQL string type *)
  let set_param (x : user_name_type) : string = Fun.id (* for example *)
end

To use this feature, you must ensure that the specified module implements:

For standard cases (when only module=ModuleName is specified):
- ModuleName.set_param - For converting domain types to SQL types
For custom function names (when both module=ModuleName and set_param=custom_name are specified):
- ModuleName.custom_name - Custom function for parameter conversion

All together:

Direction	Function	Type Signature	Example
SQL → Domain	`get_column`	`sql_type -> domain_type`	`int64 -> user_id`
Domain → SQL	`set_param`	`domain_type -> sql_type`	`user_id -> int64`

Note: Column-based customization for parameters in INSERT and UPDATE statements (e.g., for the right side of SET column = @param) is not yet supported and will be enhanced in future releases.

→ PR #198

Column-level Customization for `SELECT` (currently) Queries

Added: May 2025

Provides custom mapping for column values in SELECT queries using simple -- [sqlgg] annotations in table definitions.

🔹 Supported Annotations

Annotation	Description
`module=Module_name`	(Optional) Specifies a module to wrap column value access
`get_column=function_name`	(Optional) Specifies a custom function within the module

Both annotations are optional:

With no annotations: Default trait functions are used directly (T.get_column_Int, etc.)
With only module: Uses Module_name.get_column or Module_name.get_column_nullable
With both: Uses the custom function specified (Module_name.function_name)

🔹 Example

CREATE TABLE table_37 (
  -- [sqlgg] module=HelloWorld
  col_1 INT PRIMARY KEY,

  -- [sqlgg] module=Abcdefg
  -- [sqlgg] get_column=test
  col_2 INT NOT NULL
);

CREATE TABLE table_38 (
  -- [sqlgg] module=FooBar
  col_3 INT PRIMARY KEY,
  col_4 TEXT NOT NULL
);

And query

-- @deeply_nested_example
SELECT 
  (
    SELECT MAX(x.col_val)
    FROM (
      SELECT col_1 as col_val,
           1 + 3 as aaaaa,
           1 + col_1 as bbb
      FROM table_37 
      WHERE col_1 > (
        SELECT MIN(col_1) 
        FROM table_37
      )
    ) as x
  ) as deeply_nested_query,
  col_2,
  col_3,
  col_4
FROM table_37
LEFT JOIN table_38
ON table_37.col_1 = table_38.col_3;

🔹 Generated OCaml (Excerpt)

callback
  ~deeply_nested_query:(HelloWorld.get_column_nullable (T.get_column_int64_nullable stmt 0))
  ~col_2:(Abcdefg.test (T.get_column_int64 stmt 1))
  ~col_3:(FooBar.get_column_nullable (T.get_column_int64_nullable stmt 2))
  ~col_4:(T.get_column_Text_nullable stmt 3)

col_1 uses HelloWorld.get_column_nullable to wrap the standard getter
col_2 uses Abcdefg.test as specified by the custom function name
col_3 uses FooBar.get_column_nullable with the default function name
col_4 uses the default T.get_column_Text_nullable (no annotation)

🧠 Semantics

Case	Supported	Notes
`SELECT` column results	✅	Custom column accessors are generated based on annotations
`@param` in `WHERE` etc.	❌	Parameters currently use default `T.set_param_*` behavior
`INSERT` / `UPDATE`	❌	Not yet supported — future extension planned

Currently, customization applies only to SELECT results. Parameter handling (@param) and other DML operations(INSERT, UPDATE) still rely on default trait functions defined in the module Sqlgg (T : Sqlgg_traits.M) implementation.

🔹 Module Requirements

To use this feature, you must ensure that the specified module implements:

For standard cases (when only module=ModuleName is specified):
- ModuleName.get_column - For non-nullable values
- ModuleName.get_column_nullable - For nullable values
For custom function names (when both module=ModuleName and get_column=custom_name are specified):
- ModuleName.custom_name - For non-nullable values
- ModuleName.custom_name_nullable - For nullable values

The function signatures must match the corresponding SQL type. For example, for an INT column:

(* Standard function names *)
val get_column : int64 -> 'b
val get_column_nullable : int64 option -> 'b

(* Custom function names *)
val custom_name : int64 -> 'b
val custom_name_nullable : int64 option -> 'b

Where 'b is your desired domain-specific output type.

🔹 OCaml Implementation Example

module HelloWorld = struct
  (* For non-nullable INT values *)
  let get_column (x : int64) : int = 
    Printf.printf "Processing value: %Ld\n" x;
    Int64.to_int x

  (* For nullable INT values *)
  let get_column_nullable (x : int64 option) : int option = 
    match x with
    | None -> 
        Printf.printf "Received NULL value\n";
        None
    | Some value -> 
        Printf.printf "Processing value: %Ld\n" value;
        Some (Int64.to_int value)
end

module Abcdefg = struct
  (* Custom function name used in annotation for INT column *)
  let test (x : int64) : string =
    Printf.printf "Custom test function: %Ld\n" x;
    Int64.to_string x
end

Default trait function signatures (click to expand)

(* Core getter functions in trait module *)
val get_column_bool : row -> int -> bool
val get_column_bool_nullable : row -> int -> bool option

val get_column_int64 : row -> int -> int64
val get_column_int64_nullable : row -> int -> int64 option

val get_column_float : row -> int -> float
val get_column_float_nullable : row -> int -> float option

val get_column_decimal : row -> int -> float
val get_column_decimal_nullable : row -> int -> float option

val get_column_datetime : row -> int -> string
val get_column_datetime_nullable : row -> int -> string option

(* These functions are mapped to SQL types in the trait *)
val get_column_Bool : row -> int -> Bool.t
val get_column_Int : row -> int -> Int.t
val get_column_Text : row -> int -> Text.t
val get_column_Any : row -> int -> Any.t
val get_column_Float : row -> int -> Float.t
val get_column_Decimal : row -> int -> Decimal.t
val get_column_Datetime : row -> int -> Datetime.t

val get_column_Bool_nullable : row -> int -> Bool.t option
val get_column_Int_nullable : row -> int -> Int.t option
val get_column_Text_nullable : row -> int -> Text.t option
val get_column_Any_nullable : row -> int -> Any.t option
val get_column_Float_nullable : row -> int -> Float.t option
val get_column_Decimal_nullable : row -> int -> Decimal.t option
val get_column_Datetime_nullable : row -> int -> Datetime.t option

→ PR #192

Support for DEFAULT Values

Added: April 2025

Use {expr}?? syntax to conditionally include values or fall back to SQL DEFAULT.

🔹 Example

INSERT INTO `tbl`
SET `field_name` = { CONCAT(@param, 'string') }??

When @param is provided: The expression inside braces is used
When @param is empty: SQL DEFAULT keyword is inserted

Multiple parameters are treated as tuples:

INSERT INTO `tbl`
SET `field_name` = { CONCAT(@param1, @param2) }??

⚠️ Note: Fields must have DEFAULT value defined in schema, otherwise SQL errors will occur.

⚠️ Note#2: Currently only supported in OCaml using the option type.

→ PR #189

Support for Reusable Queries

Added: April 2025

Create reusable query fragments with Common Table Expressions (CTE) using include: reuse and &name syntax.

🔹 Usage Modes

Annotation	Behavior
`include: reuse`	Query is reusable but no function is generated
`include: reuse-and-execute`	Query is both reusable and directly callable
`include: executable`	Normal behavior (default)

🔹 Example

-- @abcd | include: reuse
SELECT 
    1 as y, 
    4 + @five as y1
FROM (
    SELECT 1 AS id, 'l' AS creator_name
    UNION ALL
    SELECT 2, 'k'
) AS x
WHERE @param { None { TRUE } | Some { FALSE } };

-- @test2
WITH x AS &abcd
SELECT 1 + @test - @test2 + @test5 + x.y1 as y2
FROM x;

In this example:

@abcd defines a reusable query fragment
&abcd embeds that query as a CTE named x
Parameters are supported in reused queries
Type inference works across both queries

⚠️ Note: Currently only available for OCaml.

→ PR #174

Type-safe Enums

Added: March 2025

Provides type-safe handling of SQL ENUMs and string literal union types with inference and validation.

🔹 String Literal Type Inference

SELECT @param { A { 'A' } | B { 'B' } | C { 'C' } }

This defines an enum-like union type: 'A' | 'B' | 'C'

🔹 Union Types

Type	Description
🔒 Closed union	Fixed set of literal values (like enums)
🔓 Open union	Built dynamically from literals or other unions

🔹 Type Checking Rules

With schema:

CREATE TABLE tbl (
  status_enum ENUM('A', 'B', 'C') NOT NULL,
  status_text TEXT NOT NULL
);

Type Hierarchy

✅ Enum values can be used as text (Enum <: Text)
❌ Text values cannot be treated as enums

Valid Operations

-- ✅ Valid: assigning Enum to Text
INSERT INTO tbl (status_text)
VALUES (@param { A { 'A' } | B { 'B' } });

-- ✅ Valid: using enum in string operations
INSERT INTO tbl (status_text)
SELECT CONCAT(
  @param { A { 'A' } | B { 'B' } },
  '_suffix'
);

-- ✅ Valid: direct enum value
INSERT INTO tbl (status_enum) VALUES ('A');

-- ❌ Invalid: CONCAT() returns Text, not Enum
INSERT INTO tbl (status_enum)
SELECT CONCAT(@param { A { 'A' } | B { 'B' } }, '_suffix');

⚙️ Optional Flag: `type-safe-enums`

When enabled:

🧪 Parameters for enum fields are type-checked
🧠 Corresponding enum types are inferred
🔁 For OCaml only, enums can map to polymorphic variants

💡 OCaml Example

CREATE TABLE some_table (
  id INT PRIMARY KEY AUTO_INCREMENT,
  status ENUM('pending', 'sending', 'sent', 'cancelled') NOT NULL,
  status_b ENUM('a', 'b', 'c', 'd') NOT NULL
);

-- @name | include: executable
INSERT INTO some_table (
  status,
  status_b
) VALUES (
  'pending',
  @status_b
);

Generated OCaml code with type-safe-enums enabled:

module Enum_status_b = T.Make_enum(struct
  type t = [ `A | `B | `C | `D ]
  let inj = function
    | "a" -> `A | "b" -> `B | "c" -> `C | "d" -> `D
    | s -> failwith ("Invalid enum value: " ^ s)
  let proj = function
    | `A -> "a" | `B -> "b" | `C -> "c" | `D -> "d"
end)

let insert_some_table db ~status_b =
  let set_params stmt =
    let p = T.start_params stmt 1 in
    Enum_status_b.set_param p status_b;
    T.finish_params p
  in
  T.execute db "INSERT INTO some_table (status, status_b) VALUES ('pending', ?)" set_params

🔐 All SQL string values are type-checked against the field's declared enum in the database.

🧬 The type-safe-enums flag affects only OCaml code generation, where enum parameters can be mapped to polymorphic variants.

→ PR #152

VALUES as Table Source

Added: February 2025

Support for using VALUES as a table source in JOINs and other contexts.

🔹 Supported Forms

Inline values: VALUES ROW(1, 'foo'), ROW(2, 'bar')
Parameterized values: VALUES @param

🔹 Examples

-- Using inline ROWs
SELECT p.id, x.a
FROM products p
JOIN ( VALUES ROW(1, 'foo'), ROW(2, 'bar') ) AS x (a, b)
ON p.name = x.b;

-- Using parameters
SELECT p.id, x.a
FROM products p
JOIN ( VALUES @param ) AS x (a, b)
ON p.name = x.b;

⚠️ Note: If @param is an empty list, a fallback query is generated: SELECT %cols% WHERE FALSE

📘 Reference: MySQL 8.4 VALUES Syntax

→ PR #148

Conditional WHERE Clauses

Added: January 2025

Use {expr}? syntax to conditionally include WHERE clauses based on optional parameters.

🔹 Example

SELECT *
FROM test19
LEFT JOIN test20 ON test20.c = @test20a
WHERE { c = @choice2 }?
GROUP BY b;

If parameter @choice2 is:

Present/Non-empty → generates WHERE c = ?
Empty/Missing → generates WHERE TRUE

Note: The exact representation of "empty" vs "non-empty" depends on the target language. Currently only OCaml is supported, where this is implemented using the option type. No polymorphic variant is introduced to avoid unnecessary wrapping.

🧠 Semantics

Syntax	When empty	Suitable for
`{expr}?`	Inserts `TRUE`	`WHERE` clauses
`{expr}??`	Inserts `DEFAULT`	`INSERT/UPDATE`

→ PR #142

🛣️ Roadmap

Coming soon

FilesExpand file tree

doc.md

Latest commit

History

doc.md

File metadata and controls

📘 Documentation sqlgg

🧭 Contents

🆕 Feature Review

Column-level Customization for INSERT/UPDATE Operations

🔹 Example Schema and Queries

🔹 Generated OCaml (Excerpt)

🔹 Module Requirements

Column-level Customization for Query Parameters

🔹 Supported Parameter Forms

🔹 Generated OCaml (Excerpt)

🔹 Module Requirements

Column-level Customization for SELECT (currently) Queries

🔹 Supported Annotations

🔹 Example

🔹 Generated OCaml (Excerpt)

🧠 Semantics

🔹 Module Requirements

🔹 OCaml Implementation Example

Support for DEFAULT Values

🔹 Example

Support for Reusable Queries

🔹 Usage Modes

🔹 Example

Type-safe Enums

🔹 String Literal Type Inference

🔹 Union Types

🔹 Type Checking Rules

Type Hierarchy

Valid Operations

⚙️ Optional Flag: type-safe-enums

💡 OCaml Example

VALUES as Table Source

🔹 Supported Forms

🔹 Examples

Conditional WHERE Clauses

🔹 Example

🧠 Semantics

🛣️ Roadmap

📘 Documentation `sqlgg`

Column-level Customization for `SELECT` (currently) Queries

⚙️ Optional Flag: `type-safe-enums`