Database — SDK

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.

Caution

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

React

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']
)

Node.js

// Multiple rows
const { results } = await sdk.db.query('SELECT * FROM users WHERE active = 1')

// Single row (returns null if not found)
const user = await sdk.db.queryOne('SELECT * FROM users WHERE id = ?', [userId])

// Execute (INSERT, UPDATE, DELETE -- no result needed)
await sdk.db.exec('CREATE TABLE IF NOT EXISTS logs (id TEXT, message TEXT)')

// Batch (multiple statements, atomic transaction)
const results = await sdk.db.batch([
  { sql: 'INSERT INTO orders (id, user_id, total) VALUES (?, ?, ?)', params: [orderId, userId, total] },
  { sql: 'UPDATE inventory SET stock = stock - ? WHERE product_id = ?', params: [qty, productId] },
])

// Get database schema
const schema = await sdk.db.getSchema()

---

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(), 'jane@example.com', '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]
  },
])

Note

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