Tutorial: Building a CRUD API

Learn how to build a complete REST API for managing products using Lua scripts. This tutorial covers folder-based routing, HTTP methods, validation, and error handling.

Prerequisites

1. SoliDB server running on localhost:6745
2. A database created (e.g., "myapp")
3. The SoliDB CLI installed

# Initialize your scripts directory

$ solidb scripts init --db myapp --host localhost --port 6745

# Login to save authentication

$ solidb scripts login

Project Structure

SoliDB uses folder-based routing. The file path determines the API endpoint URL.

scripts/
├── solidb-scripts.toml      # Configuration
├── .env                      # Environment variables
├── products.lua              # GET/POST /api/default/myapp/products
├── products/
│   └── _id.lua               # GET/PUT/DELETE /api/default/myapp/products/:id
└── tests/                    # Test files (not synced)
    └── products_test.lua

Routing Rules:

• products.lua → /api/default/myapp/products
• products/_id.lua → /api/default/myapp/products/:id (underscore = URL parameter)
• Nested folders create nested routes

Step 1: List & Create Products

Create products.lua to handle listing and creating products.

products.lua

-- @methods GET, POST
-- @description List or create products
-- @collection products

local products = db:collection("products")

-- Handle GET: List all products
if request.method == "GET" then
  -- Optional filtering by category
  local category = request.query.category

  local results
  if category then
    results = products:find({ category = category })
  else
    results = products:find({})
  end

  return {
    products = results,
    count = #results
  }
end

-- Handle POST: Create a new product
if request.method == "POST" then
  local body = request.body

  -- Validate required fields
  if not body.name or body.name == "" then
    solidb.error("Name is required", 400)
  end

  if not body.price or body.price <= 0 then
    solidb.error("Price must be a positive number", 400)
  end

  -- Create the product
  local product = products:insert({
    name = body.name,
    price = body.price,
    category = body.category or "uncategorized",
    description = body.description or "",
    in_stock = body.in_stock ~= false,
    created_at = solidb.now()
  })

  -- Return 201 Created
  solidb.status(201)
  return {
    message = "Product created",
    product = product
  }
end

Step 2: Get, Update & Delete

Create products/_id.lua for single product operations.

products/_id.lua

-- @methods GET, PUT, DELETE
-- @description Get, update or delete a product
-- @collection products

local products = db:collection("products")
local id = request.params.id

-- Find the product first
local product = products:get(id)

if not product then
  solidb.error("Product not found", 404)
end

-- Handle GET: Return the product
if request.method == "GET" then
  return product
end

-- Handle PUT: Update the product
if request.method == "PUT" then
  local body = request.body

  -- Build update object with only provided fields
  local updates = {
    updated_at = solidb.now()
  }

  if body.name then updates.name = body.name end
  if body.price then
    if body.price <= 0 then
      solidb.error("Price must be positive", 400)
    end
    updates.price = body.price
  end
  if body.category then updates.category = body.category end
  if body.description then updates.description = body.description end
  if body.in_stock ~= nil then updates.in_stock = body.in_stock end

  local updated = products:update(id, updates)

  return {
    message = "Product updated",
    product = updated
  }
end

-- Handle DELETE: Remove the product
if request.method == "DELETE" then
  products:delete(id)

  return {
    message = "Product deleted",
    id = id
  }
end

Step 3: Deploy & Test

Push your scripts to the server and test the API.

Deploy Scripts

# Push all scripts to server

$ solidb scripts push

Created: products.lua -> products [GET, POST]
Created: products/_id.lua -> products/:id [GET, PUT, DELETE]

Test with cURL

# Create a product

$ curl -X POST http://localhost:6745/api/default/myapp/products \ -H "Content-Type: application/json" \ -d '{"name": "Widget", "price": 29.99, "category": "gadgets"}'

# List all products

$ curl http://localhost:6745/api/default/myapp/products

# Get a specific product

$ curl http://localhost:6745/api/default/myapp/products/abc123

# Update a product

$ curl -X PUT http://localhost:6745/api/default/myapp/products/abc123 \ -H "Content-Type: application/json" \ -d '{"price": 24.99}'

# Delete a product

$ curl -X DELETE http://localhost:6745/api/default/myapp/products/abc123

Watch Mode

During development, use watch mode to auto-sync changes:

$ solidb scripts watch

Watching /path/to/scripts for changes...

Step 4: Enhanced Validation

Use SoliDB's built-in validation helpers for cleaner code.

-- Using solidb.validate for cleaner validation
local body = request.body

-- Validate all fields at once
solidb.validate(body, {
  name = { required = true, type = "string", min_length = 1 },
  price = { required = true, type = "number", min = 0.01 },
  category = { type = "string" },
  in_stock = { type = "boolean" }
})

-- Sanitize user input
local clean_name = solidb.sanitize.trim(body.name)
local slug = solidb.sanitize.slug(body.name)

Tutorial: Building a CRUD API

Prerequisites

Project Structure

Step 1: List & Create Products

Step 2: Get, Update & Delete

Step 3: Deploy & Test

Deploy Scripts

Test with cURL

Watch Mode

Step 4: Enhanced Validation

Next Steps

Tutorial: Testing

Validation API

Database Access

Core API