Queries

The queries block inside an entity defines custom data operations. Terranova supports two types:

• Raw queries – Direct SQL with typed parameters.
• Composed queries – Batches of multiple queries executed in a single call, returning all results together under named keys.

entity "User" {
    queries {
        // Raw query
        get "user_by_id" sql="SELECT * FROM user WHERE id = :id" {
            param "id" type="int"
        }

        // Composed query
        get "user_with_posts" {
            param "id" type="int"
            data "user" query="user_by_id" {
                bind "id" from="id"
            }
            data "posts" query="posts_by_user" {
                bind "user_id" from="id"
            }
        }
    }
}

Each query is exposed as an HTTP endpoint following the pattern {namespace}/{entity}/{query_name}. For example, get "user_by_id" becomes GET /user/user_by_id. The HTTP method corresponds to the query type (get, post, put, delete).

---

Raw Queries

A raw query executes a single SQL statement. The sql attribute contains the SQL text, and param nodes define typed parameters.

Syntax

<method> "<name>" sql="<SQL>" {
    param "name" type="<type>"
    // additional param nodes...
}

<method> is one of: get, post, put, delete.

Parameters (param)

Attribute	Type	Required	Description
name	string	✓	Parameter name (matches :name in SQL)
type	string	✓	One of: int, float, string, bool, datetime, date, blob

Parameter binding occurs automatically from the request:

• For GET and DELETE: from URL query string.

• For POST and PUT: from JSON request body.

All parameters appearing in the SQL statement must have a corresponding param declaration, and vice‑versa.

Example:

get "user_by_id" sql="SELECT id, name, email FROM user WHERE id = :id" {
    param "id" type="int"
}

This generates GET /user/user_by_id?id=123.

SQL placeholder syntax: Colon‑prefixed names (:id, :name) are used. Terranova employs SQLite named parameters.

---

Composed Queries

A composed query executes multiple sub‑queries in a single HTTP call. The result is a JSON object whose keys correspond to each data block. Each sub‑query result contains the fields data, count, error, and modified.

Limitation: Composed queries can bind values only from top‑level param declarations. Using the output of one data block as input to another (e.g., from="user.id") is not supported. All sub‑queries receive the same set of top‑level parameters.

Syntax

<method> "<name>" {
    param "name" type="<type>"   // optional parameters
    data "<key>" query="<query_name>" {
        bind "<target>" from="<source>"
        // additional bind nodes...
    }
    // additional data blocks...
}

Data Block (data)

Attribute	Type	Required	Description
name	string	✓	Key name in the result JSON
query	string		Name of the raw sub‑query to execute. If entity is omitted, the sub‑query is looked up within the same entity. If entity is provided, the sub‑query is looked up in that entity. (defaults to name).
entity	string		Name of another entity whose query to use (optional)

Important: Sub‑queries in a composed query must be raw queries (i.e., defined with sql). Composed queries cannot be nested; attempting to use a composed query as a sub‑query is not supported.

Bind Block (bind)

Binds values from the parent request to a sub‑query’s parameters.

Attribute	Type	Required	Description
name	string	✓	Parameter name in the sub‑query (e.g., "id")
from	string		The top‑level parameter name declared in this composed query (defaults to name)

Shorthand notation: When the sub‑query parameter name equals the top‑level parameter name, the from attribute may be omitted. For example, bind "id" is equivalent to bind "id" from="id".

Example (explicit bind):

queries {
    get "user_by_id" sql="SELECT id, name FROM user WHERE id = :id" {
        param "id" type="int"
    }

    get "posts_by_user" sql="SELECT * FROM post WHERE user_id = :user_id" {
        param "user_id" type="int"
    }

    get "user_with_posts" {
        param "id" type="int"
        data "user" query="user_by_id" {
            bind "id" from="id"
        }
        data "posts" query="posts_by_user" {
            bind "user_id" from="id"
        }
    }
}

Example using shorthand:

get "user_with_posts_shorthand" {
    param "id" type="int"
    data "user" query="user_by_id" {
        bind "id"          // from="id" implied
    }
    data "posts" query="posts_by_user" {
        bind "user_id" from="id"   // explicit required because names differ
    }
}

Invoking GET /user/user_with_posts?id=1 executes:

• user_by_id with :id = 1

• posts_by_user with :user_id = 1

Response:

{
    "user": { "data": [{ "id": 1, "name": "Alice" }], "count": 1, "error": null, "modified": 0 },
    "posts": { "data": [...], "count": 5, "error": null, "modified": 0 }
}

Using Queries from Other Entities

The entity attribute references a query defined in a different entity.

entity "User" {
    queries {
        get "profile" sql="SELECT * FROM user WHERE id = :id" {
            param "id" type="int"
        }
    }
}

entity "Post" {
    queries {
        get "post_by_id" sql="SELECT * FROM post WHERE id = :id" {
            param "id" type="int"
        }
        get "post_with_author" {
            param "post_id" type="int"
            data "post" query="post_by_id" {
                bind "id" from="post_id"
            }
            data "author" query="profile" entity="User" {
                bind "id" from="post_id"
            }
        }
    }
}

---

Query Types and HTTP Methods

Query type	HTTP method	Typical use
get	GET	Read data
post	POST	Create data
put	PUT	Update data (full replace)
delete	DELETE	Remove data

The generated endpoint is {namespace}/{entity}/{query_name} for all methods. For example, a post "create_user" becomes POST /user/create_user.

---

Using Queries in Views

A view may reference a query using the query attribute. The query’s result becomes the data context for the HTML template.

views {
    template "/user/" query="user_by_id" html="<h1>{{data.name}}</h1>"
}

See Views for details.

---

Complete Example

application "BlogApp" version="0.1" {
    entity "User" {
        schema {
            pk "id" type="int"
            field "name" type="string"
            has-many "Post" as="posts" on-delete="cascade"
            has-one "Profile" as="profile"
        }
        queries {
            get "profile" sql="SELECT * FROM user WHERE id = :id" {
                param "id" type="int"
            }
        }
    }

    entity "Post" {
        schema {
            pk "id" type="int"
            field "title" type="string"
            field "content" type="string"
            belongs-to "User" as="author" on="id"
            has-many "Comment" as="comments"
        }

        queries {
            get "post_by_id" sql="SELECT * FROM post WHERE id = :id" {
                param "id" type="int"
            }
            get "post_with_author" {
                param "post_id" type="int"
                data "post" query="post_by_id" {
                    bind "id" from="post_id"
                }
                data "author" query="profile" entity="User" {
                    bind "id" from="post_id"
                }
            }
        }
    }

    entity "Comment" {
        schema {
            pk "id" type="int"
            field "body" type="string"
            belongs-to "Post" as="post" on="id"
        }
    }
    profile "dev" default=true {
        listen address="127.0.0.1" port=8080
    }
}

---

What Terranova Does With Queries

• Validation – SQL syntax and parameter types are validated.
• Statement preparation – All queries are prepared once at server startup.
• Parameter binding – Automatic from request data (query string for GET/DELETE, JSON body for POST/PUT).
• Execution – For composed queries, sub‑queries execute using only the top‑level parameters. Nested dependencies (using results of one sub‑query in another) are not supported.
• Return format – Raw queries return an array of objects. Composed queries return an object with keys from data blocks, each containing data, count, error, and modified.
• View integration – Query results can be rendered as HTML.

---

See Also

• Views – Using queries in templates
• Entities – Entity container
• Schemas – Data model definition
• Profiles – Server configuration