Querying

Basic queries

// All rows
const users = await sdk.db.query('SELECT * FROM users')

// With parameters (always use parameterized queries to prevent SQL injection)
const user = await sdk.db.queryOne(
  'SELECT * FROM users WHERE email = ?',
  [email]
)

// Insert
const result = await sdk.db.query(
  'INSERT INTO posts (id, title, body, created_at) VALUES (?, ?, ?, ?)',
  [crypto.randomUUID(), title, body, Date.now()]
)

// Update
await sdk.db.query(
  'UPDATE users SET name = ? WHERE id = ?',
  [newName, userId]
)

// Delete
await sdk.db.query(
  'DELETE FROM posts WHERE id = ? AND user_id = ?',
  [postId, userId]
)

Caution

Always use ? placeholders and pass values as the second argument. Never interpolate user input directly into SQL strings.

Query with JOIN

const postsWithAuthors = await sdk.db.query(`
  SELECT p.id, p.title, p.body, p.created_at,
         u.id as author_id, u.name as author_name, u.avatar_url
  FROM posts p
  JOIN users u ON u.id = p.user_id
  WHERE p.published = 1
  ORDER BY p.created_at DESC
  LIMIT 20
`)

Pagination

const { results: posts } = await sdk.db.query(
  'SELECT * FROM posts WHERE published = 1 ORDER BY created_at DESC LIMIT ? OFFSET ?',
  [limit, offset]
)

const total = await sdk.db.queryOne<{ count: number }>(
  'SELECT COUNT(*) as count FROM posts WHERE published = 1'
)

Full-text search (FTS5)

If you’ve set up a FTS5 virtual table:

await sdk.db.exec(`
  CREATE VIRTUAL TABLE IF NOT EXISTS posts_fts
  USING fts5(title, body, content='posts', content_rowid='rowid')
`)

const results = await sdk.db.query(
  'SELECT p.* FROM posts p JOIN posts_fts f ON p.rowid = f.rowid WHERE posts_fts MATCH ?',
  [searchQuery]
)