# Database — SDK

> Query your Aerostack database with SQL -- CRUD patterns, parameterized queries, batch transactions, and schema inspection.

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

  
```ts

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']
)
```
  
  
```ts
// 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

```ts
// 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

```ts
// 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

```ts
// 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

```ts
// 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.

```ts
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.

```ts
// 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):

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

---

## Complete example: REST API for a task manager

```ts title="tasks-api.ts"

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` | 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` | Database schema |