Database Management

Build production-ready databases through conversation. Create tables, manage data, and handle complex queries - all by describing what you need in plain English.

What You Can Build

Ask the AI to create:

• Database Tables with proper field types, validations, and constraints
• Relationships between tables (one-to-many, many-to-many)
• Indexes for fast queries on frequently searched fields
• Data Queries with filtering, search, pagination, and sorting
• Bulk Operations for creating, updating, or deleting multiple records

Creating Tables

Basic Table Creation

You say:

“Create a users table with email, name, and password fields”

You get:

• Users table created
• Email field (text, unique, required)
• Name field (text, required)
• Password field (password type, hashed automatically)
• ID field (auto-increment primary key)
• created_at and updated_at timestamps
• Email uniqueness index for fast lookups

Ready to use - Database deployed and accessible

Table with Validation

You say:

“Create a products table with name, description, price, stock, and category. Price must be positive and stock cannot be negative”

You get:

• Products table created
• Name (text, required, max 200 characters)
• Description (long text, optional)
• Price (decimal, required, must be > 0)
• Stock (integer, default 0, must be >= 0)
• Category (text, required)
• Proper indexes on category and price
• All validation rules enforced

Production-ready - Validates data automatically

Table with Relationships

You say:

“Create an orders table that relates to users. Each order should have a status, total, and created date”

You get:

• Orders table created
• user_id field (foreign key to users table)
• status field (text, default: ‘pending’)
• total field (decimal)
• created_at timestamp
• Relationship configured (one user has many orders)
• Index on user_id for fast queries

Smart relationships - AI understands table connections

Understanding the Transformation

When you ask for database features, here’s what actually happens behind the scenes.

Example: Products List with Pagination

What you say:

“Create a products list endpoint with pagination and search”

What the AI generates (SDK):

const endpoint = create('products/list', 'GET')
  .description('Get paginated list of products with optional search')
  .input('page', 'int', { default: 1 })
  .input('per_page', 'int', { default: 20, max: 100 })
  .input('search', 'text', { required: false })
  .dbQuery('products', {
    filters: [
      {
        field: 'name',
        operator: 'contains',
        value: '$input.search'
      }
    ],
    pagination: {
      page: '$input.page',
      per_page: '$input.per_page'
    },
    sort: [
      { field: 'created_at', direction: 'desc' }
    ]
  }, 'results')
  .response({
    items: '$results.items',
    pagination: {
      page: '$results.curPage',
      total: '$results.itemsTotal',
      per_page: '$input.per_page'
    }
  });
 
return endpoint.build().script;

What gets deployed (XanoScript):

/* Get paginated list of products with optional search */

/* Input parameters */
var page = $input.page
var per_page = $input.per_page
var search = $input.search

/* Build query filters */
var filters = []

if (search) {
  filters.push({
    field: "name",
    operator: "contains", 
    value: search
  })
}

/* Query products table */
var results = query_all_products({
  filter: filters,
  pagination: {
    page: page,
    per_page: per_page
  },
  sort: [
    { field: "created_at", direction: "desc" }
  ]
})

/* Return structured response */
return {
  items: results.items,
  pagination: {
    page: results.curPage,
    total: results.itemsTotal,
    per_page: per_page
  }
}

Why this matters:

• ✓ JavaScript-like SDK → AI understands this syntax naturally
• ✓ Filter-based XanoScript → Clean, reviewable in Xano GUI
• ✓ Proper pagination → Automatically includes curPage, itemsTotal
• ✓ No green expressions → Uses Xano’s query filters instead
• ✓ Maintainable → Non-developers can review and modify

Test it immediately:

curl "https://your-instance.xano.io/api:v1/products/list?page=1&per_page=20&search=laptop"

Expected response:

{
  "items": [
    {
      "id": 1,
      "name": "Dell Laptop Pro",
      "price": 899.99,
      "created_at": "2024-03-15T10:30:00Z"
    }
  ],
  "pagination": {
    "page": 1,
    "total": 5,
    "per_page": 20
  }
}

Querying Data

Simple Queries

You say:

“Show me all active products sorted by price”

AI does:

Querying products table where:
- status = 'active'
- Sorted by: price (ascending)

Found 24 products

You get a list of products matching your criteria.

Filtered Search

You say:

“Find all electronics products under $100 in stock”

AI builds query:

Filtering products where:
- category = 'electronics'
- price < 100
- stock > 0

Found 12 products

Paginated Lists

You say:

“List products with 20 per page, show page 2”

You get:

• Page 2 of results (products 21-40)
• Total count across all pages
• Next/previous page indicators
• Consistent pagination

Complex Queries

You say:

“Find orders from the last 30 days for premium users, sorted by total, highest first”

AI creates:

Querying orders where:
- created_at > 30 days ago
- user.role = 'premium'
- Sorted by: total (descending)
- Includes user relationship data

Found 156 orders

Complex multi-table queries made simple.

Managing Data

Creating Records

You say:

“Add a product: Wireless Mouse, $29.99, electronics category, 50 in stock”

AI creates:

{
  "id": 123,
  "name": "Wireless Mouse",
  "price": 29.99,
  "category": "electronics",
  "stock": 50,
  "created_at": "2025-10-18T10:30:00Z"
}

Record created and validated.

Updating Records

You say:

“Update product #123: reduce stock by 5”

AI updates:

Product #123 updated:
- stock: 50 → 45
- updated_at: (current timestamp)

Deleting Records

You say:

“Delete all orders older than 2 years”

AI asks for confirmation:

⚠️ This will delete 1,234 orders from before 2023-10-18.
This action cannot be undone.

[Confirm Deletion] [Cancel]

Safety first - Destructive actions require confirmation.

Bulk Operations

Bulk Create

You say:

“Import these 100 products from my CSV file”

AI processes:

Importing 100 products...
✓ Validating data
✓ Checking for duplicates
✓ Creating records

Success: 100 products imported
Skipped: 0 (duplicates)
Failed: 0 (validation errors)

Bulk Update

You say:

“Increase price by 10% for all electronics products”

AI confirms:

This will update 45 products in category 'electronics'
Price increase: +10%

Example changes:
- Wireless Mouse: $29.99 → $32.99
- USB Cable: $9.99 → $10.99
- Keyboard: $79.99 → $87.99

[Confirm] [Cancel]

Bulk Delete

You say:

“Delete all draft blog posts older than 90 days”

AI shows:

Found 23 draft posts older than July 20, 2025

⚠️ This will permanently delete:
- 23 blog posts (status: draft)
- Average age: 145 days

[Confirm Deletion] [Cancel]

Advanced Features

Relationships

You say:

“Create a many-to-many relationship between posts and tags”

You get:

• posts_tags junction table
• Proper foreign keys to posts and tags
• Cascade delete configured
• Relationship methods ready to use

Complex relationships - AI handles the details.

Indexes for Performance

You say:

“Add an index to speed up product searches by name and category”

AI creates:

Created composite index on products:
- Fields: name, category
- Type: B-tree
- Estimated speedup: 10-15x for filtered searches

Your queries are now faster!

Full-Text Search

You say:

“Enable full-text search on product names and descriptions”

AI sets up:

Created search index on products:
- Searchable fields: name, description
- Language: English
- Features: Fuzzy matching, relevance ranking

Try it: "search products for 'wireless keyboard'"

Data Validation

Automatic Validation

When you create tables, AI automatically adds:

✓ Type validation (text, numbers, dates)
✓ Required field checks
✓ Unique constraints (emails, usernames)
✓ Range validation (min/max values)
✓ Format validation (emails, URLs)

Custom Validation

You say:

“Products must have either an SKU or a barcode, but not both”

AI implements:

Added custom validation rule:
- Must have: SKU OR barcode (not both)
- Error message: "Product must have exactly one identifier"

Validation enforced on create and update.

Common Patterns

User Profiles

You say:

“Create a user profiles table with bio, avatar, location, and social links”

You get:

• Profiles table with user_id foreign key
• Bio (long text)
• Avatar (image URL)
• Location (text)
• Social links (JSON object)
• One-to-one relationship with users

Audit Trails

You say:

“Track all changes to products with an audit log”

AI creates:

Created audit_log table:
- Records: table, record_id, action, changes
- Automatic triggers on products table
- Captures: create, update, delete events
- Stores: old values, new values, user, timestamp

All product changes now logged.

Soft Deletes

You say:

“Don’t actually delete users, just mark them as deleted”

AI implements:

Updated users table:
- Added: deleted_at (timestamp, nullable)
- Modified: delete operations set deleted_at
- Modified: queries exclude deleted records
- Added: restore function

Users are now soft-deleted by default.

Performance Tips

The AI automatically optimizes your database:

• Indexes on foreign keys for fast joins
• Indexes on commonly filtered fields (status, category, dates)
• Pagination by default on list queries (prevents slow queries)
• Proper field types (saves storage, improves speed)
• Efficient bulk operations (batches large updates)

You don’t need to know these details - the AI handles optimization automatically.

What Happens Behind the Scenes

Safety Features

Automatic Protections

The AI always:

• ✅ Validates data types before inserting
• ✅ Checks unique constraints (prevents duplicates)
• ✅ Enforces required fields
• ✅ Validates number ranges
• ✅ Formats dates correctly

Confirmation Required

You’ll be asked to confirm before:

• ⚠️ Deleting multiple records (> 10)
• ⚠️ Dropping tables
• ⚠️ Removing fields from tables
• ⚠️ Bulk updates affecting many records

Rollback Available

Made a mistake? Just ask:

• “Undo the last deletion”
• “Restore the users table”
• “Revert the price update”

The AI can roll back recent changes.

Example Conversations

Building a Blog Database

Full conversation:

You: "Create tables for a blog with posts, authors, and comments"

AI: I'll create three related tables:

1. authors (id, name, email, bio)
2. posts (id, author_id, title, content, status, published_at)
3. comments (id, post_id, author_name, content, approved)

[Deploy]

---

You: "Add a tags table with many-to-many to posts"

AI: Creating tags table and junction table...
✓ tags table created
✓ posts_tags junction table created
✓ Relationships configured

---

You: "Enable search on post titles and content"

AI: Adding full-text search index...
✓ Search enabled on posts.title and posts.content

Your blog database is ready!

Building an E-commerce Database

Full conversation:

You: "Create tables for products, categories, and orders"

AI: Creating e-commerce database with:
- categories (hierarchical)
- products (with category relationship)
- orders (with user relationship)
- order_items (junction table)

[Deploy]

---

You: "Track inventory changes"

AI: Adding inventory tracking...
✓ inventory_log table created
✓ Triggers added to track stock changes
✓ Low stock alerts configured

---

You: "Products should have multiple images"

AI: Adding product images...
✓ product_images table created
✓ One-to-many relationship configured
✓ Primary image field added to products

Done!

Common Questions

Q: Can I change a table after it’s created?

A: Yes! Just describe the change:

• “Add a phone field to users”
• “Make email required on users”
• “Remove the old_field from products”

Q: What if I delete data by mistake?

A: Ask the AI to:

• “Undo that deletion”
• “Restore deleted users”
• “Show me what I deleted in the last hour”

Q: Can I import data from a CSV?

A: Yes! Say:

• “Import this CSV into products table”
• “Bulk create users from this file”

The AI handles parsing, validation, and error reporting.

Q: How do I see my data?

A: Ask:

• “Show me all users”
• “List the first 10 products”
• “Display orders from today”

Q: Can I export data?

A: Yes:

• “Export all products as CSV”
• “Download user data as JSON”
• “Export orders from this month”

Next Steps

Now that you know how to build databases, explore related capabilities:

🔗 Create APIs for Your Data

Generate complete CRUD endpoints for any table.

Learn About API Creation →

🔐 Secure Your Data

Add authentication and permissions to protect sensitive information.

Learn About Security →

📊 Advanced Queries

Master filtering, search, and complex relationships.

See Filter Patterns →

📚 Complete Examples

See full database implementations for real projects.

View Examples →

Ready to Build?

The fastest way to learn is to create something real. What database will you build?

Start a conversation and describe your first table.