Database

INSTROC provides a PostgreSQL database for storing your application's data.

Overview

Your database is:

• PostgreSQL - Industry-standard relational database
• Managed - No maintenance required
• Secure - Row Level Security enabled
• Fast - Optimized queries and indexing

Creating Tables

Using AI

Ask the AI to create tables:

Create a database table for blog posts with title,
content, author, published date, and a published flag

The AI generates the table with appropriate types and constraints.

From Dashboard

1. Go to Project Settings → Database
2. Click New Table
3. Add columns with types
4. Set primary keys and constraints
5. Click Create

Using SQL

Write SQL directly:

CREATE TABLE posts (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  title TEXT NOT NULL,
  content TEXT,
  author_id UUID REFERENCES auth.users(id),
  published BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT now()
);

Data Types

Common PostgreSQL types:

Type	Description	Example
UUID	Unique identifier	550e8400-e29b-41d4-a716-446655440000
TEXT	Variable-length string	"Hello World"
INTEGER	Whole number	42
BOOLEAN	True/false	true
TIMESTAMP	Date and time	2024-01-15 10:30:00
JSONB	JSON data	{"key": "value"}
ARRAY	List of values	["a", "b", "c"]

Querying Data

Basic Queries

import { db } from '@/lib/backend';

// Select all
const { data: posts } = await db
  .from('posts')
  .select('*');

// Select specific columns
const { data: titles } = await db
  .from('posts')
  .select('id, title');

// With filter
const { data: published } = await db
  .from('posts')
  .select('*')
  .eq('published', true);

Filtering

// Equals
.eq('status', 'active')

// Not equals
.neq('status', 'deleted')

// Greater than
.gt('price', 100)

// Less than or equal
.lte('quantity', 10)

// Contains (text)
.ilike('title', '%search%')

// In list
.in('category', ['tech', 'design'])

// Is null
.is('deleted_at', null)

Sorting and Pagination

// Order by
const { data } = await db
  .from('posts')
  .select('*')
  .order('created_at', { ascending: false });

// Limit and offset
const { data } = await db
  .from('posts')
  .select('*')
  .range(0, 9); // First 10 items

Joins

// Join related data
const { data } = await db
  .from('posts')
  .select(`
    *,
    author:users(name, avatar)
  `);

Inserting Data

// Single insert
const { data, error } = await db
  .from('posts')
  .insert({
    title: 'My First Post',
    content: 'Hello world!',
    published: true
  })
  .select();

// Multiple inserts
const { data } = await db
  .from('posts')
  .insert([
    { title: 'Post 1' },
    { title: 'Post 2' }
  ])
  .select();

Updating Data

const { data, error } = await db
  .from('posts')
  .update({ published: true })
  .eq('id', postId)
  .select();

Deleting Data

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

Row Level Security

Control who can access data at the database level.

Enable RLS

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

Example Policies

Users can only see their own posts:

CREATE POLICY "Users view own posts"
  ON posts FOR SELECT
  USING (author_id = auth.uid());

Anyone can view published posts:

CREATE POLICY "Public view published"
  ON posts FOR SELECT
  USING (published = true);

Users can only edit their own posts:

CREATE POLICY "Users edit own posts"
  ON posts FOR UPDATE
  USING (author_id = auth.uid());

Relationships

One-to-Many

A user has many posts:

CREATE TABLE posts (
  id UUID PRIMARY KEY,
  author_id UUID REFERENCES auth.users(id)
);

Many-to-Many

Posts can have many tags, tags can be on many posts:

CREATE TABLE tags (
  id UUID PRIMARY KEY,
  name TEXT UNIQUE
);

CREATE TABLE post_tags (
  post_id UUID REFERENCES posts(id),
  tag_id UUID REFERENCES tags(id),
  PRIMARY KEY (post_id, tag_id)
);

Real-time Subscriptions

Listen for database changes:

const subscription = db
  .channel('posts')
  .on('postgres_changes',
    { event: '*', schema: 'public', table: 'posts' },
    (payload) => {
      console.log('Change:', payload);
    }
  )
  .subscribe();

// Cleanup
subscription.unsubscribe();

Best Practices

Use UUIDs

Prefer UUIDs over sequential IDs for security and scalability.

Add Timestamps

Include created_at and updated_at on most tables.

Index Frequently Queried Columns

CREATE INDEX posts_author_id_idx ON posts(author_id);

Validate with Constraints

ALTER TABLE posts
  ADD CONSTRAINT title_not_empty
  CHECK (char_length(title) > 0);

Use Transactions

For operations that must succeed together:

const { error } = await db.rpc('transfer_funds', {
  from_account: accountA,
  to_account: accountB,
  amount: 100
});