KidsBuild
Sign Up

Database Guide

Learn about protected tables, migrations, and the declarative schema system.

Protected by Design: KidsBuild uses a declarative schema system. You define what you want in JSON, and the platform handles the SQL safely. This prevents accidental data loss and ensures all apps run in a secure sandbox.

Why Protected Tables?

Traditional databases let you run any SQL query, including dangerous ones likeDROP TABLE. On KidsBuild, we protect both you and your users:

  • No raw SQL - You can't accidentally delete all your data
  • Automatic backups - State is always recoverable
  • Schema validation - Changes are reviewed before applying
  • Sandboxed data - Apps can only access their own tables
  • AI-friendly - JSON schemas are easy for AI assistants to generate

How It Works

Instead of writing SQL migrations, you define your schema in a schema.json file:

schema.jsonjson
{
  "version": 2,
  "tables": {
    "high_scores": {
      "columns": {
        "score": { "type": "integer", "nullable": false },
        "level": { "type": "integer", "default": 1 },
        "achieved_at": { "type": "timestamp", "default": "now()" }
      },
      "indexes": ["score", "achieved_at"]
    },
    "achievements": {
      "columns": {
        "name": { "type": "text", "nullable": false },
        "unlocked_at": { "type": "timestamp", "default": "now()" },
        "metadata": { "type": "json", "nullable": true }
      }
    }
  }
}

When you run kb migrations:create, the CLI compares your schema to the current database and generates a safe migration. An admin reviews and approves the migration before it's applied.

Column Types

Supported column types:

TypeDescriptionExample
textVariable-length string"Hello World"
integerWhole numbers42
bigintLarge numbers9007199254740991
decimalPrecise decimals19.99
booleanTrue/falsetrue
timestampDate and time2026-03-07T10:30:00Z
jsonJSON objects/arrays{"key": "value"}
uuidUnique identifiers550e8400-e29b...

Column Options

{
  "name": {
    "type": "text",
    "nullable": false,           // Required field
    "default": "Anonymous",      // Default value
    "unique": true               // Must be unique
  },
  "score": {
    "type": "integer",
    "nullable": false,
    "default": 0,
    "check": "score >= 0"        // Validation rule
  }
}

Auto-Generated Columns

Every table automatically gets these columns (you don't need to define them):

  • id - Unique identifier (UUID)
  • app_id - Your app's ID (for data isolation)
  • user_id - The user who owns this row
  • created_at - When the row was created
  • updated_at - When the row was last modified

Creating a Schema

Let's create a schema for a simple game with scores and achievements:

Terminal
$kb init --template game

This creates a starter schema.json. Edit it to match your needs:

schema.jsonjson
{
  "version": 1,
  "tables": {
    "game_sessions": {
      "columns": {
        "score": { "type": "integer", "nullable": false, "default": 0 },
        "level_reached": { "type": "integer", "default": 1 },
        "duration_seconds": { "type": "integer" },
        "completed": { "type": "boolean", "default": false }
      },
      "indexes": ["score", "completed"]
    },
    "power_ups": {
      "columns": {
        "type": { "type": "text", "nullable": false },
        "quantity": { "type": "integer", "default": 0 }
      },
      "indexes": ["type"]
    }
  }
}

Migrations Workflow

1. Make Schema Changes

Edit your schema.json to add a new table or column:

schema.json (updated)json
{
  "version": 2,
  "tables": {
    "game_sessions": { ... },
    "power_ups": { ... },
    "achievements": {
      "columns": {
        "name": { "type": "text", "nullable": false },
        "description": { "type": "text" },
        "icon": { "type": "text" },
        "unlocked": { "type": "boolean", "default": false }
      }
    }
  }
}

2. Generate Migration

Terminal
$kb migrations:create

The CLI shows you what will change:

Comparing schema.json (v2) to database (v1)...

Changes detected:
  + Add table: achievements
    + column: name (text, NOT NULL)
    + column: description (text)
    + column: icon (text)
    + column: unlocked (boolean, DEFAULT false)

? Create migration? Yes

✓ Created: migrations/002_add_achievements.json

3. Apply Migration (Dev)

Terminal
$kb migrations:apply

In development, migrations apply immediately to your local/dev database.

4. Publish with Migration

When you publish, the migration is included and reviewed:

Terminal
$kb publish
Publishing my-game v1.1.0...

✓ Build verified
✓ Assets uploaded
✓ Schema changes detected:
    - Add table: achievements (4 columns)
  
  Schema changes will be reviewed before applying.

✓ Submitted for review

Querying Data

Use the SDK to query your tables. The SDK handles all the security and ensures users can only access their own data:

src/App.tsxtsx
import { useQuery, useMutation } from '@kidsbuild/sdk'

function Leaderboard() {
  // Fetch data
  const { data: scores, isLoading } = useQuery('game_sessions', {
    select: ['score', 'level_reached', 'created_at'],
    where: { completed: true },
    orderBy: { score: 'desc' },
    limit: 10
  })

  // Insert data
  const { mutate: saveScore } = useMutation('game_sessions')
  
  const handleGameEnd = (score: number, level: number) => {
    saveScore({
      score,
      level_reached: level,
      completed: true,
      duration_seconds: 120
    })
  }

  if (isLoading) return <div>Loading...</div>

  return (
    <ol>
      {scores?.map((session) => (
        <li key={session.id}>
          Level {session.level_reached}: {session.score} pts
        </li>
      ))}
    </ol>
  )
}

AI Prompt for Schema Design

Use this prompt with Claude Code or Cursor to generate schemas:

Prompttext
I need a KidsBuild database schema for my app. My app is:
[DESCRIBE YOUR APP]

I need to store:
[DESCRIBE YOUR DATA]

Create a schema.json file following KidsBuild conventions:
- Use version numbers starting at 1
- Available types: text, integer, bigint, decimal, boolean, timestamp, json, uuid
- Include nullable, default, unique options as needed
- Add indexes for frequently queried columns
- Remember: id, app_id, user_id, created_at, updated_at are auto-generated

Output only the schema.json content.

Best Practices

  • Start simple - Add tables and columns as you need them
  • Use indexes wisely - Index columns you filter or sort by often
  • Version your schema - Bump version number with each change
  • Test migrations locally - Use kb dev before publishing
  • Use json for flexible data - Great for settings or metadata

Limitations

The protected database system has some intentional limitations:

  • No raw SQL queries - use the SDK query builder
  • No table drops - tables can only be deprecated, not deleted
  • No cross-app queries - apps can only access their own data
  • Max 20 tables per app
  • Max 50 columns per table
  • Max 1MB per row

Next Steps