Quick Start

Setting Up the Schema

To generate queries, the system needs to understand your database schema. This includes:

• A list of relevant tables (those you will query, and those needed for joins).
• The relevant fields within each table.
• Optionally, aliases for tables and fields to make legacy or unintuitive names more meaningful without changing the underlying database.

Example Schema Definition

{
  "tables": [
    {
      "name": "users",
      "typing": "strict",
      "fields": [
        { "name": "id", "type": "uuid" },
        { "name": "name", "type": "string" },
        { "name": "created_at", "type": "datetime" }
      ]
    }
  ]
}

{
  "tables": [
    {
      "name": "usr_tbl",
      "alias": "Users",
      "typing": "strict",
      "fields": [
        { "name": "usr_id", "type": "uuid", "alias": "User ID" },
        { "name": "fn", "type": "string", "alias": "Full Name" },
        { "name": "cr_dt", "type": "datetime", "alias": "Created Date" },
        { "name": "acc_st", "type": "string", "alias": "Account Status" }
      ]
    }
  ]
}

Schema Typing Modes

Each table must specify a typing mode that controls how the query generator accesses fields:

• strict - The model can only access fields explicitly defined in the schema. This is the recommended mode for SQL databases where the schema is fixed.

Supported Data Types

To generate type-safe queries, you must specify the data types for each field. The system supports both primitive and composite types.

Primitive Types

The following primitive types are currently supported:

• string - Text data
• integer - Signed 64-bit integers
• unsignedinteger - Unsigned 64-bit integers
• float - 64-bit floating point numbers
• datetime - Date and time with timezone
• date - Date only values
• time - Time only values
• bool - Boolean true/false values
• oid - Database object identifiers
• uuid - Universally unique identifiers (UUID v4)

Composite Types

• nullable - Wraps any type to allow null values
• enum - Predefined set of allowed values with optional aliases
• object - Nested structure with typed fields
• array - List of items of a single type

Nullable Fields

Wrap any type with nullable to allow null values:

{
  "name": "deleted_at",
  "type": "nullable",
  "value": { "type": "datetime" }
}

Enum Fields

Define a fixed set of allowed values. Enum variants can have aliases to map legacy or cryptic database values to human-readable names:

{
  "name": "status",
  "type": "enum",
  "variants": [
    { "value": "A", "alias": "Active", "description": "User is active" },
    { "value": "I", "alias": "Inactive" },
    { "value": "P", "alias": "Pending" }
  ]
}

Enum values can be strings, integers, booleans, or floats:

{
  "name": "priority",
  "type": "enum",
  "variants": [
    { "value": 1, "alias": "Low" },
    { "value": 2, "alias": "Medium" },
    { "value": 3, "alias": "High" }
  ]
}

Object Fields

Define nested structures with their own typed fields:

{
  "name": "address",
  "type": "object",
  "fields": [
    { "name": "street", "type": "string" },
    { "name": "city", "type": "string" },
    { "name": "zip", "type": "string" }
  ]
}

Array Fields

Define lists of items of a single type:

{
  "name": "tags",
  "type": "array",
  "item": { "type": "string" }
}

Arrays can contain any type, including objects:

{
  "name": "addresses",
  "type": "array",
  "item": {
    "type": "object",
    "fields": [
      { "name": "street", "type": "string" },
      { "name": "city", "type": "string" }
    ]
  }
}

Example with Data Types

{
  "tables": [
    {
      "name": "products",
      "typing": "strict",
      "fields": [
        { "name": "id", "type": "uuid" },
        { "name": "name", "type": "string" },
        { "name": "price", "type": "float" },
        { "name": "in_stock", "type": "bool" },
        { "name": "category", "type": "string" },
        { "name": "created_date", "type": "date" },
        { "name": "last_updated", "type": "datetime" }
      ]
    }
  ]
}

Supported Database Engines

To generate accurate queries for your specific database, you need to specify which database engine you’re using. Currently supported engines include:

• PostgreSQL - Full-featured relational database
• ClickHouse - Column-oriented OLAP database for analytics
• BigQuery - Google Cloud’s serverless data warehouse

Complete Schema Example

Here’s a complete schema configuration example:

{
  "default_engine": { "type": "postgres", "version": "16.0.0" },
  "schema": {
    "tables": [
      {
        "name": "customers",
        "typing": "strict",
        "fields": [
          { "name": "id", "type": "integer" },
          { "name": "name", "type": "string" },
          { "name": "email", "type": "string" },
          { "name": "registered_at", "type": "datetime" },
          { "name": "active", "type": "bool" }
        ]
      },
      {
        "name": "orders",
        "typing": "strict",
        "fields": [
          { "name": "id", "type": "uuid" },
          { "name": "customer_id", "type": "integer" },
          { "name": "total", "type": "float" },
          { "name": "status", "type": "string" }
        ]
      }
    ]
  }
}

{
  "default_engine": { "type": "postgres", "version": "16.0.0" },
  "schema": {
    "tables": [
      {
        "name": "cust_master",
        "alias": "Customers",
        "typing": "strict",
        "fields": [
          { "name": "cust_id", "type": "integer", "alias": "Customer ID" },
          { "name": "full_nm", "type": "string", "alias": "Full Name" },
          { "name": "email_addr", "type": "string", "alias": "Email Address" },
          { "name": "reg_dt", "type": "datetime", "alias": "Registration Date" },
          { "name": "is_actv", "type": "bool", "alias": "Is Active" }
        ]
      },
      {
        "name": "purch_ord",
        "alias": "Purchase Orders",
        "typing": "strict",
        "fields": [
          { "name": "ord_id", "type": "uuid", "alias": "Order ID" },
          { "name": "cust_ref", "type": "integer", "alias": "Customer Reference" },
          { "name": "ord_tot", "type": "float", "alias": "Order Total" },
          { "name": "ord_sts", "type": "string", "alias": "Order Status" }
        ]
      }
    ]
  }
}

Adding Your Schema

You can add your schema using one of the following methods:

• Using our REST API 
• Through the dashboard, which currently supports importing the JSON schema format shown above. Future releases will feature a comprehensive GUI for schema management.

Agent Setup

The primary methods to use KaiwaDB are through our REST API or admin dashboard. For impatient users, we offer agents intended primarily for demo purposes.

The agent can be installed using:

curl --proto '=https' --tlsv1.2 -LsSf https://github.com/kaiwadb/agent/releases/download/v0.2.1/kaiwadb-agent-installer.sh | sh

or for Windows users:

powershell -c "irm https://github.com/kaiwadb/agent/releases/download/v0.2.1/kaiwadb-agent-installer.ps1 | iex"

Once installed, the agent can be started under a VPN where it can reach the database. The admin can then add database connections in the admin dashboard and send queries directly from the web app without requiring any additional infrastructure setup.

You can create an agent token in the settings of the admin dashboard to authenticate the agent.

Basic CLI Usage

After installing the agent, you can use the CLI to interact with the KaiwaDB service:

kaiwadb-agent --token "<token>"

Replace <token> with your agent token generated from the admin dashboard.