getting-started.md

sidebar_position	1
title	Getting Started
description	Quick start guide for sqlgg - type-safe SQL code generator

Getting Started

Dune Configuration

To use sqlgg in your project, add the following to your dune file:

(executable
 (name your_executable_name)
 (libraries
  sqlgg.mariadb
  mariadb))

This example assumes MariaDB as your chosen database. For other databases, replace sqlgg.mariadb and mariadb with the appropriate libraries (e.g., sqlgg.sqlite3 and sqlite3).

:::note This doesn't have to be an executable — you can use library or any other stanza as needed. :::

Generating Modules

Suppose you have a file file.sql with the following content:

-- @create_table_users
CREATE TEMPORARY TABLE tmp_table_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    age INT
);

-- @insert_sample
INSERT INTO tmp_table_users (name, age)
VALUES 
    ('Alice', 30),
    ('Bob', 25),
    ('Charlie', 40);

-- @select_all
SELECT * FROM tmp_table_users;

:::tip We will explain later how IF NOT EXISTS can be useful for organizing migrations, although it is entirely optional. :::

To generate an OCaml module sql_file.ml from this SQL file, add the following rule to your dune file:

(rule
 (target sql_file.ml)
 (mode promote)
 (action
  (with-stdout-to
   %{target}
   (run sqlgg -gen caml -params unnamed -static-header %{deps})))
 (deps file.sql))

This rule will:

• Generate sql_file.ml from file.sql
• Use promote mode to copy the generated file to your source directory
• Pass the -static-header flag to output a short static header without version/timestamp

Basic Usage

Let's take a look at what we've generated:

(* DO NOT EDIT MANUALLY *)
(*  *)
(* generated by sqlgg *)

module Sqlgg (T : Sqlgg_traits.M) = struct

  module IO = Sqlgg_io.Blocking

  let create_table_users db  =
    T.execute db ("CREATE TEMPORARY TABLE tmp_table_users (\n\
    id INT AUTO_INCREMENT PRIMARY KEY,\n\
    name VARCHAR(255) NOT NULL,\n\
    age INT\n\
)") T.no_params

  let insert_sample db  =
    T.execute db ("INSERT INTO tmp_table_users (name, age)\n\
VALUES \n\
    ('Alice', 30),\n\
    ('Bob', 25),\n\
    ('Charlie', 40)") T.no_params

  let select_all db callback =
    T.select db ("SELECT * FROM tmp_table_users")
      T.no_params
      (fun row -> callback
        ~id:(T.get_column_Int64_exn row 0)
        ~name:(T.get_column_Text_exn row 1)
        ~age:(T.get_column_Int64 row 2))

  module Fold = struct
    let select_all db f acc = IO.fold (select_all db) f acc
  end

  module List = struct
    let select_all db f = IO.to_list (select_all db) f
  end

end (* module Sqlgg *)

The (T : Sqlgg_traits.M) functor parameter and module IO = Sqlgg_io.Blocking will be covered in detail in the Traits chapter (OCaml-specific) — for now, we can skip over these details.

Running the Example

Now let's try this out! We'll be using the ocaml-mariadb library for database connectivity.

In your executable, start by setting up the blocking MariaDB module:

module Blocking_mariadb = Sqlgg_mariadb.Default(Sqlgg_io.Blocking)(struct 
  include Mariadb.Blocking
  type 'a future = 'a
end)

Note that type 'a future = 'a indicates blocking mode. We use Sqlgg_io.Blocking here accordingly. A non-blocking example will be covered later — for now, let's focus on getting things working.

Next, instantiate the generated module with our database implementation:

module Db = Sql_file.Sqlgg(Blocking_mariadb)

Here Sql_file is the module we generated earlier, and Sqlgg is the functor inside it.

Now we can use our generated functions:

let () = 
  let db = Mariadb.Blocking.connect 
    ~host:"your_host" 
    ~port:3306 
    ~user:"your_user" 
    ~pass:"your_pass" 
    ~db:"your_db" 
    () 
  in
  match db with
  | Error _ ->
    Printf.eprintf "Failed to connect to database";
    exit 1
  | Ok c ->
    let (_: Blocking_mariadb.execute_response) = Db.create_table_users c in
    let (_: Blocking_mariadb.execute_response) = Db.insert_sample c in
    let cnt = Db.Fold.select_all c (fun ~id:_ ~name:_ ~age:_ acc -> acc + 1) 0 in
    let result = Db.List.select_all c (fun ~id ~name ~age -> (id, name, age)) in
    prerr_endline @@ Printf.sprintf "Total rows: %d" cnt;
    List.iter (fun (id, name, age) ->
      prerr_endline @@ Printf.sprintf "ID: %Ld, Name: %s, Age: %s" 
        id 
        name 
        (match age with Some a -> Int64.to_string a | None -> "NULL")
    ) result

Of course, for counting rows it would be better to write a dedicated SELECT COUNT(*) query — but here we use Fold to demonstrate how the generated code works. We immediately show both Db.Fold.select_all (for aggregation with an accumulator) and Db.List.select_all (for collecting results into a list).

If everything went successfully, you should see the following output:

Total rows: 3
ID: 1, Name: Alice, Age: 30
ID: 2, Name: Bob, Age: 25
ID: 3, Name: Charlie, Age: 40

Exactly as expected!