Database

The Aerostack database module gives you full SQL access to your project’s database. All queries are parameterized to prevent SQL injection. Batch operations run as atomic transactions.

⚠️

Beta — Database APIs are stable but may receive non-breaking additions.

What you can build

Product catalogs — CRUD for products, categories, inventory with search and filtering
User profiles — Store and update user data, preferences, and settings
Analytics dashboards — Aggregate queries for metrics, time-series data, and reports
Order management — Atomic batch inserts for orders + inventory updates in one transaction
Multi-tenant apps — Isolated data per tenant using SQL WHERE clauses (project-scoped by default)
Content management — Blog posts, comments, tags with relational queries

Methods

import { useDb } from '@aerostack/react'
 
const { query, loading, error } = useDb()
 
// Multiple rows
const { results } = await query('SELECT * FROM users WHERE active = 1')
 
// With parameters
const { results } = await query(
  'SELECT * FROM users WHERE role = ? AND created_at > ?',
  ['admin', '2026-01-01']
)

CRUD patterns

SELECT — read data

// All rows with ordering and limit
const { results: users } = await sdk.db.query(
  'SELECT id, email, name FROM users ORDER BY created_at DESC LIMIT ?',
  [20]
)
 
// Single row by ID
const user = await sdk.db.queryOne(
  'SELECT * FROM users WHERE id = ?',
  [userId]
)
// Returns null if not found
 
// Filtered search
const { results: posts } = await sdk.db.query(
  'SELECT * FROM posts WHERE title LIKE ? AND published = 1 ORDER BY created_at DESC',
  [`%${searchTerm}%`]
)
 
// Aggregations
const stats = await sdk.db.queryOne(
  'SELECT COUNT(*) as total, SUM(amount) as revenue FROM orders WHERE created_at > ?',
  [startDate]
)

INSERT — create data

// Insert with RETURNING to get the new row back
const { results: [newUser] } = await sdk.db.query(
  'INSERT INTO users (id, email, name, created_at) VALUES (?, ?, ?, ?) RETURNING *',
  [crypto.randomUUID(), '[email protected]', 'Jane Doe', Date.now()]
)
 
// Insert without RETURNING
await sdk.db.query(
  'INSERT INTO logs (id, event, data, timestamp) VALUES (?, ?, ?, ?)',
  [crypto.randomUUID(), 'user.signup', JSON.stringify({ email }), Date.now()]
)

UPDATE — modify data

// Update specific fields
await sdk.db.query(
  'UPDATE users SET name = ?, updated_at = ? WHERE id = ?',
  ['Jane Smith', Date.now(), userId]
)
 
// Conditional update
await sdk.db.query(
  'UPDATE products SET stock = stock - ? WHERE id = ? AND stock >= ?',
  [quantity, productId, quantity]
)

DELETE — remove data

// Delete by ID
await sdk.db.query('DELETE FROM sessions WHERE id = ?', [sessionId])
 
// Delete with condition
await sdk.db.query(
  'DELETE FROM logs WHERE created_at < ?',
  [Date.now() - 30 * 24 * 60 * 60 * 1000] // older than 30 days
)

Batch operations (transactions)

Batch operations execute multiple SQL statements in a single round-trip. They run as an atomic transaction — if any statement fails, all changes are rolled back.

await sdk.db.batch([
  {
    sql: 'INSERT INTO orders (id, user_id, total, status) VALUES (?, ?, ?, ?)',
    params: [orderId, userId, 99.99, 'pending']
  },
  {
    sql: 'INSERT INTO order_items (id, order_id, product_id, quantity, price) VALUES (?, ?, ?, ?, ?)',
    params: [itemId, orderId, productId, 2, 49.99]
  },
  {
    sql: 'UPDATE products SET stock = stock - ? WHERE id = ? AND stock >= ?',
    params: [2, productId, 2]
  },
])

Batch operations are essential for data consistency. Use them whenever you need to update multiple tables as part of one logical operation (e.g., placing an order, transferring funds, updating a user and their associated records).

Parameterized queries

Always use ? placeholders for dynamic values. Never concatenate user input into SQL strings.

// CORRECT -- parameterized, safe from SQL injection
const { results } = await sdk.db.query(
  'SELECT * FROM users WHERE email = ? AND role = ?',
  [userEmail, 'admin']
)
 
// WRONG -- vulnerable to SQL injection
const { results } = await sdk.db.query(
  `SELECT * FROM users WHERE email = '${userEmail}'` // NEVER do this
)

Schema inspection

Retrieve the full database schema (table names, columns, types):

const schema = await sdk.db.getSchema()
// Returns table definitions with column names and types

Complete example: REST API for a task manager

tasks-api.ts

import { AerostackClient } from '@aerostack/sdk'
import { Hono } from 'hono'
 
const { sdk } = new AerostackClient({ projectId, apiKey })
const app = new Hono()
 
// List tasks for a user
app.get('/tasks', async (c) => {
  const userId = c.get('user').id
  const { results: tasks } = await sdk.db.query(
    'SELECT * FROM tasks WHERE user_id = ? ORDER BY created_at DESC',
    [userId]
  )
  return c.json(tasks)
})
 
// Create a task
app.post('/tasks', async (c) => {
  const { title, description } = await c.req.json()
  const userId = c.get('user').id
  const id = crypto.randomUUID()
 
  const { results: [task] } = await sdk.db.query(
    'INSERT INTO tasks (id, user_id, title, description, status, created_at) VALUES (?, ?, ?, ?, ?, ?) RETURNING *',
    [id, userId, title, description, 'todo', Date.now()]
  )
  return c.json(task, 201)
})
 
// Update task status
app.patch('/tasks/:id', async (c) => {
  const { status } = await c.req.json()
  const userId = c.get('user').id
 
  await sdk.db.query(
    'UPDATE tasks SET status = ?, updated_at = ? WHERE id = ? AND user_id = ?',
    [status, Date.now(), c.req.param('id'), userId]
  )
  return c.json({ success: true })
})
 
// Delete a task
app.delete('/tasks/:id', async (c) => {
  const userId = c.get('user').id
 
  await sdk.db.query(
    'DELETE FROM tasks WHERE id = ? AND user_id = ?',
    [c.req.param('id'), userId]
  )
  return c.json({ success: true })
})

API reference

Server SDK (`@aerostack/sdk`)

Method	Signature	Returns
`sdk.db.query`	`(sql: string, params?: any[]) => Promise<{ results: T[] }>`	Array of rows
`sdk.db.queryOne`	`(sql: string, params?: any[]) => Promise<T \| null>`	Single row or null
`sdk.db.exec`	`(sql: string) => Promise<void>`	Nothing
`sdk.db.batch`	`(stmts: { sql: string, params?: any[] }[]) => Promise<{ results: T[] }[]>`	Array of results per statement
`sdk.db.getSchema`	`() => Promise<Schema>`	Database schema

Authentication Realtime