Developer Documentation/Extensions/Database

Database

Learn how to use SQLite databases in your extension with migrations, CRDT sync, and Drizzle ORM.

Overview

Extensions have access to a SQLite database with automatic table namespacing. Your tables are prefixed with your public key to prevent conflicts with other extensions.

Extensions can create, read, and modify their own tables within their namespace without any permissions. Permissions are only required when accessing tables from other extensions.

System tables (prefixed with haex_) are protected and cannot be accessed by extensions, even with wildcard patterns.

Full SQLite

Complete SQLite support with all standard SQL features

Namespaced Tables

Tables are automatically prefixed to prevent conflicts

Migrations

Version-controlled schema changes with automatic tracking

CRDT Sync

Conflict-free sync across devices using CRDTs

Table Naming

Tables are automatically namespaced with the pattern:

"{public_key}__{extension_name}__{table_name}"

Use the getTableName() helper to get the full prefixed name:

import { useHaexClient } from '@haex-space/vault-sdk/vue'

const client = useHaexClient()

// Get the full prefixed table name
const tableName = client.getTableName('users')
// Returns: "a1b2c3d4__my-extension__users"

// Use in SQL queries
const users = await client.query(`SELECT * FROM ${tableName}`)

Creating Tables

Create tables using the onSetup hook to ensure they exist before your extension runs:

// Using the setup hook (recommended)
client.onSetup(async () => {
  const tableName = client.getTableName('passwords')

  await client.execute(`
    CREATE TABLE IF NOT EXISTS ${tableName} (
      id TEXT PRIMARY KEY,
      title TEXT NOT NULL,
      username TEXT,
      password TEXT NOT NULL,
      url TEXT,
      notes TEXT,
      created_at TEXT DEFAULT CURRENT_TIMESTAMP,
      updated_at TEXT DEFAULT CURRENT_TIMESTAMP
    )
  `)
})

// Don't forget to call setupComplete()
await client.setupComplete()

The onSetup callback runs once when your extension initializes. Always call setupComplete() after registering your setup hooks.

Migrations

Use migrations to evolve your database schema over time. Migrations are tracked and only run once:

import { useHaexClient } from '@haex-space/vault-sdk/vue'

const client = useHaexClient()

client.onSetup(async () => {
  const tableName = client.getTableName('passwords')

  await client.registerMigrationsAsync('1.0.0', [
    {
      name: '001_create_passwords_table',
      sql: `
        CREATE TABLE IF NOT EXISTS ${tableName} (
          id TEXT PRIMARY KEY,
          title TEXT NOT NULL,
          password TEXT NOT NULL,
          created_at TEXT DEFAULT CURRENT_TIMESTAMP
        )
      `
    },
    {
      name: '002_add_category_column',
      sql: `
        ALTER TABLE ${tableName}
        ADD COLUMN category TEXT DEFAULT 'general'
      `
    },
    {
      name: '003_add_url_column',
      sql: `
        ALTER TABLE ${tableName}
        ADD COLUMN url TEXT
      `
    }
  ])
})

await client.setupComplete()

Migration names must be unique. Once a migration has run, its SQL cannot be changed. Add a new migration for schema changes.

Queries & Mutations

The SDK provides methods for all common database operations:

SELECT

// Simple SELECT
const users = await client.query<User>('SELECT * FROM users')

// With parameters (prevents SQL injection)
const user = await client.query<User>(
  'SELECT * FROM users WHERE id = ?',
  [userId]
)

// Using select() alias
const activeUsers = await client.select<User>(
  'SELECT * FROM users WHERE active = ?',
  [true]
)

INSERT

// Using execute() for INSERT
const result = await client.execute(
  'INSERT INTO users (id, name, email) VALUES (?, ?, ?)',
  [crypto.randomUUID(), 'John Doe', 'john@example.com']
)
console.log('Inserted ID:', result.lastInsertId)

// Using the insert() helper
const id = await client.insert('users', {
  id: crypto.randomUUID(),
  name: 'Jane Doe',
  email: 'jane@example.com'
})

UPDATE

// Using execute() for UPDATE
const result = await client.execute(
  'UPDATE users SET name = ? WHERE id = ?',
  ['John Smith', userId]
)
console.log('Rows affected:', result.rowsAffected)

// Using the update() helper
const rowsAffected = await client.update(
  'users',
  { name: 'John Smith', updated_at: new Date().toISOString() },
  'id = ?',
  [userId]
)

DELETE

// Using execute() for DELETE
await client.execute('DELETE FROM users WHERE id = ?', [userId])

// Using the delete() helper
const rowsDeleted = await client.delete('users', 'id = ?', [userId])

Transactions

// Execute multiple statements in a transaction
await client.transaction([
  'INSERT INTO users (id, name) VALUES ("1", "John")',
  'INSERT INTO profiles (user_id, bio) VALUES ("1", "Hello!")',
  'UPDATE stats SET user_count = user_count + 1'
])

CRDT Sync

haex-vault automatically adds these columns to all tables for sync support. You don't need to define them:

Column	Type	Purpose
`haex_timestamp`	INTEGER	Logical timestamp for ordering operations
`haex_column_hlcs`	TEXT	Hybrid logical clock state for each column
`haex_deleted`	INTEGER	Soft delete flag (0 = active, 1 = deleted)

// haex-vault automatically adds CRDT columns to all tables
// You don't need to define them in your schema

await client.execute(`
  CREATE TABLE IF NOT EXISTS ${tableName} (
    id TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT
  )
`)

// The following columns are automatically managed by haex-vault:
// - haex_timestamp: Row-level timestamp for sync ordering
// - haex_column_hlcs: Per-column hybrid logical clocks
// - haex_deleted: Soft delete flag for sync

// Important: Do NOT create columns starting with 'haex_'
// They will be overwritten by the sync system

Do not create columns starting with 'haex_' in your schema. These are reserved and managed automatically by haex-vault.

Drizzle ORM

For type-safe database access, use the built-in Drizzle ORM integration:

Schema Definition

import { useHaexClient } from '@haex-space/vault-sdk/vue'
import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core'

const client = useHaexClient()

// Define your schema
const users = sqliteTable('users', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').unique(),
  createdAt: text('created_at').default('CURRENT_TIMESTAMP'),
})

const posts = sqliteTable('posts', {
  id: text('id').primaryKey(),
  userId: text('user_id').references(() => users.id),
  title: text('title').notNull(),
  content: text('content'),
})

// Initialize Drizzle ORM
const db = client.initializeDatabase({ users, posts })

Type-Safe Queries

// Type-safe queries with Drizzle
import { eq, like, and, desc } from 'drizzle-orm'

// Select all users
const allUsers = await db.select().from(users)

// Select with conditions
const activeUsers = await db
  .select()
  .from(users)
  .where(eq(users.active, true))

// Join tables
const postsWithAuthors = await db
  .select({
    postTitle: posts.title,
    authorName: users.name
  })
  .from(posts)
  .leftJoin(users, eq(posts.userId, users.id))
  .orderBy(desc(posts.createdAt))

// Insert with type safety
await db.insert(users).values({
  id: crypto.randomUUID(),
  name: 'John Doe',
  email: 'john@example.com'
})

// Update
await db
  .update(users)
  .set({ name: 'Jane Doe' })
  .where(eq(users.id, userId))

←Permissions Publishing→