volcano-sdk-js/docs/database.md at main · Kong/volcano-sdk-js

title	Database
description	Volcano lets you query your PostgreSQL database directly from the browser using a fluent query builder.

Volcano lets you query your PostgreSQL database directly from the browser using a fluent query builder. Combined with Row-Level Security (RLS), this provides a secure and powerful way to build data-driven applications without writing backend code.

Overview

The database module provides:

Query Builder - Chainable methods for building SELECT, INSERT, UPDATE, and DELETE queries
Row-Level Security - Automatic filtering based on the authenticated user
Type Safety - Full TypeScript support for query results
Error Handling - Consistent error responses across all operations

Setup

Before querying, set your database name:

import { VolcanoAuth } from '@volcano.dev/sdk';

const volcano = new VolcanoAuth({
  apiUrl: 'https://api.yourproject.volcano.dev',
  anonKey: 'your-anon-key',
});

// Set database name (do this once after initialization)
volcano.database('my-database');

// Sign in (required for most operations)
await volcano.auth.signIn({
  email: 'user@example.com',
  password: 'password123',
});

The database name is typically the name you gave your database when creating it in the Volcano dashboard. The SDK sends it as one URL path segment. Queries and mutations return an error without a request when the name is blank, is . or .., contains /, or has a lone UTF-16 surrogate. URL parsers resolve . and .. segments, and Volcano routes an encoded / as a separator, so such a name would reach a different API route.

Querying Data (SELECT)

Basic Select

Fetch all rows from a table:

const { data, error, count } = await volcano.from('posts').select('*');

if (error) {
  console.error('Query failed:', error.message);
  return;
}

console.log(`Found ${count} posts`);
data.forEach((post) => {
  console.log(`- ${post.title}`);
});

Select Specific Columns

Request only the columns you need:

const { data } = await volcano.from('posts').select('id, title, created_at');

// Each item only contains id, title, and created_at

This reduces data transfer and improves performance, especially for tables with many columns.

Projection includes only the requested columns. Filters apply before pagination; multiple order() clauses apply in the order you add them. Include a unique final sort column before using limit() and offset() so page boundaries are stable. Pass native null, true, or false to is(); string values are different SQL values.

Filtering

The query builder supports a variety of filter operators:

Equality

// Posts where status equals 'published'
const { data } = await volcano.from('posts').select('*').eq('status', 'published');

Not Equal

// Posts where status is not 'draft'
const { data } = await volcano.from('posts').select('*').neq('status', 'draft');

Comparison Operators

// Posts with more than 100 views
const { data } = await volcano.from('posts').select('*').gt('views', 100);

// Posts with at least 100 views
const { data } = await volcano.from('posts').select('*').gte('views', 100);

// Posts with fewer than 50 views
const { data } = await volcano.from('posts').select('*').lt('views', 50);

// Posts with 50 or fewer views
const { data } = await volcano.from('posts').select('*').lte('views', 50);

Pattern Matching

// Case-sensitive pattern matching
const { data } = await volcano.from('posts').select('*').like('title', 'Getting Started%'); // Starts with "Getting Started"

// Case-insensitive pattern matching
const { data } = await volcano.from('posts').select('*').ilike('title', '%javascript%'); // Contains "javascript" (any case)

Pattern syntax:

% matches any sequence of characters
_ matches any single character

NULL Checks

// Posts without a deleted timestamp
const { data } = await volcano.from('posts').select('*').is('deleted_at', null);

IN Operator

// Posts with specific statuses
const { data } = await volcano
  .from('posts')
  .select('*')
  .in('status', ['draft', 'published', 'archived']);

Combining Filters

Chain multiple filters for AND logic:

const { data } = await volcano
  .from('products')
  .select('id, name, price')
  .eq('category', 'electronics')
  .gte('price', 100)
  .lte('price', 500)
  .eq('in_stock', true);

// SQL equivalent:
// WHERE category = 'electronics'
//   AND price >= 100
//   AND price <= 500
//   AND in_stock = true

Ordering Results

Sort results by one or more columns:

// Sort by created_at descending (newest first)
const { data } = await volcano.from('posts').select('*').order('created_at', { ascending: false });

// Sort by multiple columns
const { data } = await volcano
  .from('posts')
  .select('*')
  .order('category', { ascending: true })
  .order('created_at', { ascending: false });

Pagination

Limit and offset results for pagination:

const pageSize = 10;
const page = 3;

const { data, count } = await volcano
  .from('posts')
  .select('id, title')
  .order('created_at', { ascending: false })
  .limit(pageSize)
  .offset((page - 1) * pageSize); // Skip first 20 rows

console.log(`Showing page ${page} of ${Math.ceil(count / pageSize)}`);

Complete Example

Here's a realistic query combining multiple features:

async function getPublishedPosts(category, page = 1) {
  const pageSize = 20;

  const { data, error, count } = await volcano
    .from('posts')
    .select('id, title, excerpt, author_name, created_at')
    .eq('status', 'published')
    .eq('category', category)
    .is('deleted_at', null)
    .order('created_at', { ascending: false })
    .limit(pageSize)
    .offset((page - 1) * pageSize);

  if (error) {
    throw new Error(`Failed to fetch posts: ${error.message}`);
  }

  return {
    posts: data,
    totalPages: Math.ceil(count / pageSize),
    currentPage: page,
  };
}

Inserting Data

Single Insert

const { data, error } = await volcano.insert('posts', {
  title: 'My New Post',
  content: 'This is the content of my post.',
  status: 'draft',
});

if (error) {
  console.error('Insert failed:', error.message);
  return;
}

const newPost = data[0];
console.log('Created post with ID:', newPost.id);

The insert returns the created row(s), including any auto-generated values like id or created_at.

Insert with User Context

If your table has a user_id column and RLS policies, the user ID is automatically associated:

// Assuming posts table has user_id column with default value auth.uid()
const { data } = await volcano.insert('posts', {
  title: 'My Post',
  content: 'Content here',
});

// The returned post includes user_id set to the current user
console.log('Author ID:', data[0].user_id);

This works because Volcano injects the authenticated user's context into every database request.

Updating Data

Update with Filter

Always use a filter to specify which rows to update:

const { data, error } = await volcano
  .update('posts', {
    title: 'Updated Title',
    status: 'published',
    updated_at: new Date().toISOString(),
  })
  .eq('id', postId);

if (error) {
  console.error('Update failed:', error.message);
  return;
}

const updatedPost = data[0];
if (updatedPost) {
  console.log('Post updated:', updatedPost.title);
}

Updates return a list of changed rows. When the filter matches no rows, the operation succeeds with data: [] and leaves existing rows unchanged.

Update Multiple Rows

Filter conditions can match multiple rows:

// Archive all posts older than 1 year
const oneYearAgo = new Date();
oneYearAgo.setFullYear(oneYearAgo.getFullYear() - 1);

const { data, error } = await volcano
  .update('posts', {
    status: 'archived',
  })
  .lt('created_at', oneYearAgo.toISOString())
  .eq('status', 'published');

console.log(`Archived ${data.length} posts`);

Deleting Data

Delete with Filter

const { data, error } = await volcano.delete('posts').eq('id', postId);

if (error) {
  console.error('Delete failed:', error.message);
  return;
}

if (data.length > 0) {
  console.log('Deleted post:', data[0].id);
}

Deletes return a list of removed rows. When the filter matches no rows, the operation succeeds with data: [] and leaves existing rows unchanged.

Soft Delete Pattern

Many applications prefer soft deletes (marking records as deleted rather than removing them):

// Soft delete
const { data } = await volcano
  .update('posts', {
    deleted_at: new Date().toISOString(),
  })
  .eq('id', postId);

// Query excludes soft-deleted records
const { data: activePosts } = await volcano.from('posts').select('*').is('deleted_at', null);

Row-Level Security

Row-Level Security (RLS) is PostgreSQL's mechanism for controlling which rows users can access. Volcano automatically passes the authenticated user's context to the database, enabling RLS policies to work seamlessly.

How It Works

When you sign in, the SDK receives an access token containing your user ID
Every database query includes this token in the Authorization header
Volcano's proxy extracts the user ID and sets it in the database session
Your RLS policies use auth.uid() to reference the current user

Example RLS Policy

In your database, create policies like:

-- Users can only read their own posts
CREATE POLICY "Users can read own posts"
ON posts FOR SELECT
USING (user_id = auth.uid());

-- Users can only update their own posts
CREATE POLICY "Users can update own posts"
ON posts FOR UPDATE
USING (user_id = auth.uid());

-- Users can insert posts as themselves
CREATE POLICY "Users can insert own posts"
ON posts FOR INSERT
WITH CHECK (user_id = auth.uid());

Automatic Filtering

With these policies, queries are automatically filtered:

// Alice signs in
await volcano.auth.signIn({ email: 'alice@example.com', password: '...' });

// This returns only Alice's posts
const { data: alicePosts } = await volcano.from('posts').select('*');

// Bob signs in (different session)
await volcano.auth.signIn({ email: 'bob@example.com', password: '...' });

// This returns only Bob's posts
const { data: bobPosts } = await volcano.from('posts').select('*');

Same query, different results based on who's authenticated.

Public Data

For data that should be readable by anyone (even unauthenticated users), create policies with true:

-- Anyone can read published posts
CREATE POLICY "Published posts are public"
ON posts FOR SELECT
USING (status = 'published');

TypeScript Support

The query builder supports TypeScript generics for type-safe results:

interface Post {
  id: string;
  title: string;
  content: string;
  status: 'draft' | 'published' | 'archived';
  created_at: string;
}

const { data, error } = await volcano.from<Post>('posts').select('*').eq('status', 'published');

if (data) {
  // data is typed as Post[]
  data.forEach((post) => {
    console.log(post.title); // TypeScript knows this is a string
  });
}

Error Handling

All database operations return an error object instead of throwing:

const { data, error } = await volcano.from('posts').select('*').eq('invalid_column', 'value');

if (error) {
  // Handle the error
  console.error('Query failed:', error.message);

  // Common error types:
  // - "column does not exist" - Invalid column name
  // - "permission denied" - RLS policy violation
  // - "No active session" - User not authenticated
  // - "Database name not set" - Forgot to call volcano.database()
  // - "Database name ..." - Blank, ".", "..", contains "/", or not well-formed; nothing was sent
}

Best Practices

Always Set Database Name

Call volcano.database() before any queries:

const volcano = new VolcanoAuth({ ... });
volcano.database('my-database');  // Do this once

Sign In Before Querying

Most applications require authentication:

// Check for existing session first
const { user } = await volcano.initialize();

if (!user) {
  // Redirect to login
  return;
}

// Now safe to query
const { data } = await volcano.from('posts').select('*');

Use Specific Columns

Select only the columns you need:

// Good - only fetches needed columns
const { data } = await volcano.from('posts').select('id, title, created_at');

// Avoid - fetches all columns including potentially large ones
const { data } = await volcano.from('posts').select('*');

Paginate Large Results

Don't fetch thousands of rows at once:

const { data } = await volcano
  .from('posts')
  .select('*')
  .order('created_at', { ascending: false })
  .limit(50); // Reasonable page size

Use Filters to Limit Data

Let RLS and filters reduce the data server-side:

// Good - filters on server
const { data } = await volcano
  .from('posts')
  .select('*')
  .eq('status', 'published')
  .eq('category', category);

// Avoid - fetches everything then filters client-side
const { data } = await volcano.from('posts').select('*');
const filtered = data.filter((p) => p.status === 'published');

Functions

For complex queries that aren't possible with the query builder (JOINs, CTEs, stored procedures), run them in a Volcano function with the standard pg library:

// Call a function for complex queries
const { data, error } = await volcano.functions.invoke('get-dashboard-stats', {
  timeframe: 'last-30-days',
});

See the Functions guide for more details on functions.

Next Steps

Storage - Upload and manage files
Realtime - Subscribe to database changes
Functions - Complex queries in functions

FilesExpand file tree

database.md

Latest commit

History