SQL Macros

mik-sdk includes a SQL query builder that generates parameterized queries with Mongo-style filter syntax. The generated SQL can be executed by your database sidecar service.

Required Imports

#[allow(warnings)]
mod bindings;

use bindings::exports::mik::core::handler::{self, Guest, Response};
use mik_sdk::prelude::*;

// For cursor pagination
use mik_sdk::query::{Cursor, PageInfo, Value};

The SQL macros (sql_read!, sql_create!, sql_update!, sql_delete!) are included in mik_sdk::prelude::* when the sql feature is enabled (enabled by default).

Overview

The SQL macros generate (sql_string, params) tuples:

let (sql, params) = sql_read!(users {
    select: [id, name],
    filter: { active: true },
});
// sql: "SELECT id, name FROM users WHERE active = $1"
// params: [Value::Bool(true)]

CRUD Operations

sql_read! - SELECT

// Basic select
let (sql, params) = sql_read!(users {
    select: [id, name, email],
});
// SELECT id, name, email FROM users

// With filter
let (sql, params) = sql_read!(users {
    select: [id, name],
    filter: { active: true, role: "admin" },
});
// SELECT id, name FROM users WHERE active = $1 AND role = $2

// With ordering
let (sql, params) = sql_read!(users {
    select: [id, name, created_at],
    order: [-created_at, name],  // DESC, ASC
});
// SELECT ... ORDER BY created_at DESC, name ASC

// With limit
let (sql, params) = sql_read!(users {
    select: [id, name],
    limit: 20,
});
// SELECT ... LIMIT 20

// With pagination
let (sql, params) = sql_read!(users {
    select: [id, name],
    page: 2,
    limit: 20,
});
// SELECT ... LIMIT 20 OFFSET 20

sql_create! - INSERT

let (sql, params) = sql_create!(users {
    name: "Alice",
    email: "alice@example.com",
    active: true,
});
// INSERT INTO users (name, email, active) VALUES ($1, $2, $3)

// With RETURNING clause
let (sql, params) = sql_create!(users {
    name: "Alice",
    email: "alice@example.com",
    returning: [id, created_at],
});
// INSERT INTO ... RETURNING id, created_at

sql_update! - UPDATE

let (sql, params) = sql_update!(users {
    set: {
        name: "Bob",
        updated_at: time::now_iso(),
    },
    filter: { id: 123 },
});
// UPDATE users SET name = $1, updated_at = $2 WHERE id = $3

Caution

Always include a filter in UPDATE queries to avoid updating all rows.

sql_delete! - DELETE

let (sql, params) = sql_delete!(users {
    filter: { id: 123 },
});
// DELETE FROM users WHERE id = $1

Filter Operators

Comparison

sql_read!(users {
    filter: {
        age: { $eq: 30 },      // age = 30
        age: { $ne: 30 },      // age != 30
        age: { $gt: 18 },      // age > 18
        age: { $gte: 18 },     // age >= 18
        age: { $lt: 65 },      // age < 65
        age: { $lte: 65 },     // age <= 65
    },
});

Operator	SQL	Description
$eq	=	Equal to
$ne	!=	Not equal to
$gt	>	Greater than
$gte	>=	Greater than or equal
$lt	<	Less than
$lte	<=	Less than or equal

List Operators

sql_read!(users {
    filter: {
        role: { $in: ["admin", "moderator", "editor"] },
        id: { $nin: [1, 2, 3] },
    },
});
// role IN ($1, $2, $3) AND id NOT IN ($4, $5, $6)

Operator	SQL	Description
$in	IN (...)	Value in list
$nin	NOT IN (...)	Value not in list

Text Operators

sql_read!(users {
    filter: {
        name: { $like: "%alice%" },
        email: { $starts_with: "admin" },
        email: { $ends_with: "@example.com" },
        bio: { $contains: "developer" },
        name: { $ilike: "%ALICE%" },  // Case-insensitive
    },
});

Operator	SQL	Description
$like	LIKE	Pattern match
$ilike	ILIKE	Case-insensitive LIKE
$starts_with	LIKE 'x%'	Starts with
$ends_with	LIKE '%x'	Ends with
$contains	LIKE '%x%'	Contains

Range Operator

sql_read!(users {
    filter: {
        age: { $between: [18, 65] },
    },
});
// age BETWEEN $1 AND $2

Logical Operators

// OR
sql_read!(users {
    filter: {
        $or: [
            { role: "admin" },
            { verified: true }
        ]
    },
});
// (role = $1) OR (verified = $2)

// AND (explicit)
sql_read!(users {
    filter: {
        $and: [
            { active: true },
            { age: { $gte: 18 } }
        ]
    },
});
// (active = $1) AND (age >= $2)

// NOT
sql_read!(users {
    filter: {
        $not: { banned: true }
    },
});
// NOT (banned = $1)

Ordering

sql_read!(posts {
    select: [id, title, created_at, views],
    order: [-created_at, -views, title],  // DESC, DESC, ASC
});
// ORDER BY created_at DESC, views DESC, title ASC

Prefix with - for descending order.

Pagination

Offset Pagination

let (sql, params) = sql_read!(users {
    select: [id, name],
    order: id,
    page: 3,      // Page number (1-indexed)
    limit: 20,    // Items per page
});
// SELECT ... ORDER BY id LIMIT 20 OFFSET 40

Cursor Pagination

For large datasets, cursor pagination is more efficient:

use mik_sdk::query::Cursor;

// First page
let (sql, params) = sql_read!(posts {
    select: [id, title, created_at],
    order: [-created_at, -id],
    limit: 20,
});

// Subsequent pages
let (sql, params) = sql_read!(posts {
    select: [id, title, created_at],
    order: [-created_at, -id],
    after: Some(&cursor_string),  // From previous response
    limit: 20,
});

Building Cursors

use mik_sdk::query::Cursor;

// After fetching results, build next cursor from last item
let next_cursor = Cursor::new()
    .string("created_at", &last_item.created_at)
    .int("id", last_item.id)
    .encode();

// Return in response
ok!({
    "items": items,
    "next_cursor": next_cursor
})

SQLite Dialect

For SQLite, add sqlite as the first parameter:

let (sql, params) = sql_read!(sqlite, users {
    filter: { active: true },
});
// SELECT * FROM users WHERE active = ?1
// (Uses ?1, ?2 placeholders instead of $1, $2)

Batched Loading

Use the ids! macro to extract IDs for batch loading:

fn list_posts(_req: &Request) -> Response {
    // Get posts
    let (sql, params) = sql_read!(posts {
        select: [id, title, author_id],
        limit: 20,
    });
    // Execute: let posts = db.query(sql, params);

    // Extract author IDs
    // let author_ids = ids!(posts, author_id);

    // Batch load authors (single query)
    // let (sql, params) = sql_read!(users {
    //     filter: { id: { $in: author_ids } },
    // });
    // let authors = db.query(sql, params);

    ok!({ "pattern": "batched loading" })
}

Query Policy

One query per table per request. Never execute per-row queries. Use $in with ids! for related data.

Runtime Filter Parsing

Allow users to provide filters at runtime with validation and security controls.

Parsing User Input

use mik_sql::{parse_filter, sql_read};

fn search(query: SearchQuery, req: &Request) -> Response {
    // Extract body as text, early return if missing
    let body = ensure!(req.text(), 400, "Filter body required");

    // Parse filter, early return if invalid
    let filter = ensure!(parse_filter(body), 400, "Invalid filter");

    // Use with merge: option
    let (sql, params) = ensure!(sql_read!(users {
        select: [id, name, email],
        filter: { active: true },              // Always applied (trusted)
        merge: filter,                          // User's filter (validated)
        allow: [name, email, status],           // Field whitelist
        deny_ops: [$like, $ilike],              // Blocked operators
        page: query.page,
        limit: query.limit,
    }), 400, "Invalid filter field");

    // Execute query...
}

merge: Options

Option	Description
merge:	User-provided FilterExpr to merge
allow:	Whitelist of fields users can filter on
deny_ops:	Operators to block (e.g., $like, $regex)
max_depth:	Maximum nesting depth (default: 5)

Tip

When using merge:, the macro returns Result<(String, Vec<Value>), String>. Use ensure! to handle validation errors gracefully.

Example Request

POST /users/search?page=1&limit=20
Content-Type: application/json

{"name": {"$starts_with": "A"}, "status": "active"}

Supported Filter Syntax

Syntax	Example	SQL
Implicit $eq	{"name": "Alice"}	name = 'Alice'
Explicit operator	{"age": {"$gte": 18}}	age >= 18
Multiple fields	{"a": 1, "b": 2}	a = 1 AND b = 2
$and	{"$and": [{...}, {...}]}	(...) AND (...)
$or	{"$or": [{...}, {...}]}	(...) OR (...)
$not	{"$not": {...}}	NOT (...)
$in	{"status": {"$in": ["a", "b"]}}	status IN ('a', 'b')
$between	{"age": {"$between": [18, 65]}}	age BETWEEN 18 AND 65

Security

Always use allow: to whitelist fields. Without it, users could filter on any column. Use deny_ops: to block regex-like operators ($like, $ilike, $regex) if not needed.

Type Hints

Use type hints in values for clarity:

sql_create!(users {
    name: str(name_var),
    age: int(age_var),
    active: bool(is_active),
    score: float(score_var),
});

Complete Example

#[derive(Query)]
pub struct ListQuery {
    pub search: Option<String>,
    #[field(default = 1)]
    pub page: u32,
    #[field(default = 20, max = 100)]
    pub limit: u32,
}

fn list_users(query: ListQuery, _req: &Request) -> Response {
    let (sql, params) = if let Some(ref search) = query.search {
        sql_read!(users {
            select: [id, name, email, created_at],
            filter: {
                active: true,
                $or: [
                    { name: { $contains: search } },
                    { email: { $contains: search } }
                ]
            },
            order: [-created_at, id],
            page: query.page,
            limit: query.limit,
        })
    } else {
        sql_read!(users {
            select: [id, name, email, created_at],
            filter: { active: true },
            order: [-created_at, id],
            page: query.page,
            limit: query.limit,
        })
    };

    // Execute via sidecar: let result = db.query(sql, params);

    ok!({
        "sql": sql,
        "page": query.page,
        "limit": query.limit
    })
}

API Summary

Macros

Macro	Purpose
sql_read!(table { ... })	SELECT query
sql_create!(table { ... })	INSERT query
sql_update!(table { ... })	UPDATE query
sql_delete!(table { ... })	DELETE query
ids!(collection)	Extract field values
ids!(collection, field)	Extract specific field

sql_read! Options

Option	Description
select: [fields]	Columns to select
filter: { ... }	WHERE conditions (compile-time)
merge: expr	Runtime filter to merge (returns Result)
allow: [fields]	Whitelist for merge fields
deny_ops: [ops]	Blocked operators for merge
order: [fields]	ORDER BY (prefix - for DESC)
limit: n	LIMIT clause
page: n	Page number (with limit)
after: cursor	Cursor for keyset pagination

Cursor Methods

Method	Description
Cursor::new()	Create new cursor builder
.string(key, val)	Add string field
.int(key, val)	Add integer field
.encode()	Encode to base64 string