Managing Your Database with ByteMason

Learn how ByteMason helps you manage your Supabase database without writing complex SQL or dealing with migrations manually.

Understanding Database Management

ByteMason handles your database in three main ways:

Creates tables and relationships automatically
Manages changes through migrations
Provides type-safe database access

Setting Up Your Database

1. Initial Setup

First, you’ll need a Supabase project. Get these from your Supabase dashboard:

Project URL
Anon Key
Service Role Key

Then run:


berry db setup ./spec/specification.json

This will:

Connect to your Supabase project
Create necessary tables
Set up authentication
Configure security policies

2. Database Structure

ByteMason creates a clean database structure. For example, a todo app might have:


-- Users (handled by Supabase Auth)
auth.users
  - id
  - email
  - created_at
 
-- Todos
create table todos (
  id uuid primary key default uuid_generate_v4(),
  title text not null,
  description text,
  completed boolean default false,
  user_id uuid references auth.users(id),
  created_at timestamp with time zone default now()
);
 
-- Categories
create table categories (
  id uuid primary key default uuid_generate_v4(),
  name text not null,
  user_id uuid references auth.users(id)
);

3. Security Rules

ByteMason automatically sets up Row Level Security (RLS):


-- Users can only see their own todos
create policy "Users can view own todos"
  on todos for select
  using (auth.uid() = user_id);
 
-- Users can only modify their own todos
create policy "Users can modify own todos"
  on todos for all
  using (auth.uid() = user_id);

Making Database Changes

1. Update Your Specification

When you need new features, update your specification:


berry plan "Add priority levels to todos and ability to set due dates"

This creates a new specification with updated database schema.

2. Review Changes

Before applying changes, you can see what will change:


berry db push --dry-run

Example output:


-- Adding new columns to todos table
alter table todos
add column priority text check (priority in ('low', 'medium', 'high')),
add column due_date timestamp with time zone;

3. Apply Changes

When you’re ready, apply the changes:


berry db push

Working with Data

1. Type-Safe Queries

ByteMason generates type-safe database functions:


// Generated type-safe function
export async function getTodos() {
  const { data, error } = await supabase
    .from('todos')
    .select('*')
    .order('created_at', { ascending: false })
  
  if (error) throw error
  return data
}

2. Real-time Updates

Enable real-time updates in your app:


// Subscribe to changes
const todos = supabase
  .from('todos')
  .on('*', (payload) => {
    console.log('Change received!', payload)
    // Update your UI
  })
  .subscribe()

Common Tasks

Adding a New Table

Update your specification:


berry plan "Add comments to todos"

Review the changes:


berry db push --dry-run

Apply changes:


berry db push

Modifying Existing Tables

Update specification with new requirements
ByteMason generates safe migrations
Apply changes with berry db push

Handling Relations

ByteMason automatically:

Sets up foreign keys
Creates junction tables for many-to-many relations
Configures cascading deletes when appropriate

Best Practices

Regular Backups
- Use Supabase dashboard for backups
- Export data before major changes
Testing Changes
- Use --dry-run to preview changes
- Test with sample data first
- Back up before pushing changes
Security
- Review generated RLS policies
- Test access patterns
- Use environment variables for keys

Troubleshooting

Common Issues

Migration Failed
- Check Supabase connection
- Verify credentials
- Review error messages

Data Type Conflicts


# Fix data type issues
berry repair --db

Permission Issues
- Check service role key permissions
- Review RLS policies
- Verify user roles