Databases

# Fork main_db for pull request 482; it expires on its own after 72 hours
volcano cloud databases branches create main_db pr_482 --ttl 72h

# Once it reports active, read its connection string
volcano cloud databases branches get main_db pr_482 --show-connection-string

# Rewind it to the parent's current data, or delete it when you are done
volcano cloud databases branches reset main_db pr_482 --yes
volcano cloud databases branches delete main_db pr_482 --yes

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

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

// Sign in first
await volcano.auth.signIn({ email: 'user@example.com', password: 'password' });

// Query with the SDK
const { data, error } = await volcano
  .from('posts')
  .select('id, title, content')
  .eq('status', 'published')
  .order('created_at', { ascending: false })
  .limit(10);

const { Client } = require('pg');

exports.handler = async (event) => {
  const client = new Client({
    connectionString: process.env.DATABASE_URL,
  });

  await client.connect();

  const { rows } = await client.query(`
    SELECT id, title, content
    FROM posts
    WHERE status = 'published'
    ORDER BY created_at DESC
    LIMIT 10
  `);

  await client.end();

  return {
    statusCode: 200,
    body: JSON.stringify(rows),
  };
};

curl -X POST "https://api.volcano.dev/projects/$PROJECT_ID/databases" \
  -H "Authorization: Bearer $PLATFORM_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"name": "main"}'

{
  "id": "db_abc123",
  "name": "main",
  "status": "provisioning",
  "region": "us-east-1",
  "pg_version": "18"
}

curl -X POST "https://api.volcano.dev/projects/$PROJECT_ID/variables" \
  -H "Authorization: Bearer $PLATFORM_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"name": "DATABASE_URL", "value": "<connection_string from GET /databases/{id}>"}'

const { Pool } = require('pg');

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

exports.handler = async (event) => {
  const { rows } = await pool.query('SELECT NOW()');
  return {
    statusCode: 200,
    body: JSON.stringify({ time: rows[0].now }),
  };
};

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

-- Enable RLS on the table
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Users can read all published posts
CREATE POLICY "public_posts" ON posts
  FOR SELECT USING (status = 'published');

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

// User is signed in
await volcano.auth.signIn({ email: 'user@example.com', password: 'password' });

// Query returns only this user's data
const { data } = await volcano.from('posts').select('*');

// Server-side only
const volcanoAdmin = new VolcanoAuth({
  apiUrl: process.env.VOLCANO_API_URL,
  anonKey: process.env.VOLCANO_ANON_KEY,
  accessToken: process.env.VOLCANO_SERVICE_KEY,
});
volcanoAdmin.database('main');

// Returns all posts, not filtered by RLS
const { data } = await volcanoAdmin.from('posts').select('*');

curl "https://api.volcano.dev/databases/regions"

[
  { "id": "ap-northeast-1", "name": "Asia Pacific (Tokyo)" },
  { "id": "us-east-1", "name": "US East (N. Virginia)" }
]

curl -X POST "https://api.volcano.dev/projects/$PROJECT_ID/databases" \
  -H "Authorization: Bearer $PLATFORM_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"name": "main", "region": "eu-central-1", "pg_version": "18"}'

-- Several rows in one INSERT
INSERT INTO chat_messages (conversation_id, role, body)
VALUES ($1, 'user', $2), ($1, 'assistant', $3);

-- Dependent writes in one statement
WITH turn AS (
  INSERT INTO chat_turns (conversation_id) VALUES ($1) RETURNING id
)
INSERT INTO chat_messages (turn_id, role, body)
SELECT id, 'user', $2 FROM turn;

CREATE FUNCTION record_turn(conversation UUID, question TEXT, answer TEXT)
RETURNS UUID
LANGUAGE plpgsql
SECURITY INVOKER
AS $$
DECLARE
  turn_id UUID;
BEGIN
  INSERT INTO chat_turns (conversation_id) VALUES (conversation) RETURNING id INTO turn_id;
  INSERT INTO chat_messages (turn_id, role, body)
  VALUES (turn_id, 'user', question), (turn_id, 'assistant', answer);
  UPDATE conversations SET updated_at = now() WHERE id = conversation;
  RETURN turn_id;
END;
$$;

const { rows } = await client.query('SELECT record_turn($1, $2, $3) AS id', [conversationId, question, answer]);