Skip to main content

Databases

Create a Snap Cloud database table with row level security and insert, select, update, and delete its rows from a SPECS Lens.

  • Snapchat: not supported
  • SPECS: fully supported
  • Camera Kit Web: not supported
  • Camera Kit Mobile: not supported
  • SnapchatNot supported
  • SPECSFully supported
  • Camera Kit WebNot supported
  • Camera Kit MobileNot supported

Platform details

  • Snapchat — Not supported.

  • SPECS — Fully supported.

  • Camera Kit Web — Not supported.

  • Camera Kit Mobile — Not supported.

Each Snap Cloud project has a PostgreSQL database. This guide creates a table that keeps one message per row, restricts each user to their own rows with row level security (RLS), and runs all four database operations on it from a Lens: INSERT, SELECT, UPDATE, and DELETE.

For the full reference, see the Snap Cloud documentation site.

Before you start

Snap Cloud dashboard home page for a project

Create the test_table table in the Snap Cloud dashboard

The table is named test_table, the default the script on this page uses. Use underscores, not hyphens: a hyphenated table name has to be quoted in SQL.

  1. In the dashboard's left sidebar, select Table Editor.

    Dashboard sidebar with Table Editor
  2. Select New Table.

  3. Name the table test_table. RLS is turned on by default for a new table; it lets you control access to each row.

  4. Select Add Column and add a column named user_id of type uuid, with the default value auth.uid().

  5. Select Add Column again and add a column named message of type text.

  6. Save the table.

Columns of the new table: id, created_at, user_id with default auth.uid(), and message

Add row level security policies to test_table

With RLS on and no policies, nobody can read or write the table. These policies let each signed-in user insert, read, update, and delete only the rows whose user_id is their own.

  1. In the Table Editor, open test_table and select Add RLS policy.

  2. Select Create Policy.

  3. From the templates, select Enable insert for users based on user_id, then Save Policy.

    Policy editor with the Enable insert for users based on user_id template
  4. Select Create Policy again, select the template Enable users to view their own data only, then Save Policy.

  5. Add the UPDATE and DELETE policies: without them, updates and deletes from the Lens match no rows and return no data. Open SQL Editor in the left sidebar, run this SQL, and check that the table now lists four policies.

create policy "Enable update for users based on user_id"
on public.test_table for update
to authenticated
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "Enable delete for users based on user_id"
on public.test_table for delete
to authenticated
using ((select auth.uid()) = user_id);
Policies list for test_table

Read and write test_table from a Lens

The script inserts a row, selects the user's rows, updates one, and deletes one, half a second apart, and logs each result. Add these members to the SnapCloudStarter script from Get Started, replacing its empty onSignedIn(). They use the starter's client, uid and delay members.

@input
@hint('Table name to use for database operations')
tableName: string = 'test_table';

async onSignedIn() {
await this.runDatabaseExamples();
}

async runDatabaseExamples() {
this.log('--- DATABASE EXAMPLES START ---');

await this.testInsert();

await this.delay(500);
await this.testSelect();

await this.delay(500);
await this.testUpdate();

await this.delay(500);
await this.testDelete();

this.log('--- DATABASE EXAMPLES COMPLETE ---');
}

async testInsert() {
this.log('Testing INSERT operation...');

const testMessage = 'Hello Supabase DB ' + Date.now();

const { data, error } = await this.client
.from(this.tableName)
.insert([
{
user_id: this.uid,
message: testMessage,
},
])
.select();

if (error) {
this.log('INSERT FAILED: ' + JSON.stringify(error));
return;
}

if (data && data.length > 0) {
this.log('INSERT SUCCESS - ID: ' + data[0].id);
this.log('Message: ' + data[0].message);
} else {
this.log('INSERT FAILED: No data returned');
}
}

async testSelect() {
this.log('Testing SELECT operation...');

const { data, error } = await this.client
.from(this.tableName)
.select('*')
.eq('user_id', this.uid)
.limit(3);

if (error) {
this.log('SELECT FAILED: ' + JSON.stringify(error));
return;
}

if (data && data.length > 0) {
this.log('SELECT SUCCESS - Found ' + data.length + ' records');
data.forEach((record, index) => {
this.log(' Record ' + (index + 1) + ': ' + record.message);
});
} else {
this.log('SELECT SUCCESS - No records found');
}
}

async testUpdate() {
this.log('Testing UPDATE operation...');

const { data: selectData, error: selectError } = await this.client
.from(this.tableName)
.select('id')
.eq('user_id', this.uid)
.limit(1);

if (selectError || !selectData || selectData.length === 0) {
this.log('UPDATE SKIPPED: No records to update');
return;
}

const recordId = selectData[0].id;
const updatedMessage = 'Updated message ' + Date.now();

const { data, error } = await this.client
.from(this.tableName)
.update({
message: updatedMessage,
})
.eq('id', recordId)
.select();

if (error) {
this.log('UPDATE FAILED: ' + JSON.stringify(error));
return;
}

if (data && data.length > 0) {
this.log('UPDATE SUCCESS - ID: ' + data[0].id);
this.log('New message: ' + data[0].message);
} else {
// No matching row: check the UPDATE policy
this.log('UPDATE FAILED: No data returned');
}
}

async testDelete() {
this.log('Testing DELETE operation...');

const { data: selectData, error: selectError } = await this.client
.from(this.tableName)
.select('id')
.eq('user_id', this.uid)
.limit(1);

if (selectError || !selectData || selectData.length === 0) {
this.log('DELETE SKIPPED: No records to delete');
return;
}

const recordId = selectData[0].id;

const { data, error } = await this.client
.from(this.tableName)
.delete()
.eq('id', recordId)
.select();

if (error) {
this.log('DELETE FAILED: ' + JSON.stringify(error));
return;
}

if (data && data.length > 0) {
this.log('DELETE SUCCESS - Deleted ID: ' + data[0].id);
} else {
// No matching row: check the DELETE policy
this.log('DELETE FAILED: No data returned');
}
}

private log(message: string) {
print('[DatabaseExample] ' + message);
}

Run the Lens. The Logger shows each operation's result, from --- DATABASE EXAMPLES START --- to --- DATABASE EXAMPLES COMPLETE ---. To see the rows the script wrote, open test_table in the Table Editor and refresh it.

test_table in the Table Editor with rows written by the Lens

Save per-user settings with upsert

To keep one row of settings per user, such as volume or color mode, give the table a unique user_id column and write with upsert. With { onConflict: 'user_id' }, the first call inserts the row and every later call updates it, so you never check first whether the row exists.

The Snap Cloud Examples package uses a user_preferences table with these columns:

ColumnType
iduuid, primary key
user_idtext, unique
preferencestext (a JSON string)
updated_attext

Because user_id is text, the RLS policies compare it with auth.uid()::text. An upsert needs INSERT, UPDATE, and SELECT policies:

create policy "Users insert their own preferences"
on public.user_preferences for insert to authenticated
with check ((select auth.uid())::text = user_id);

create policy "Users update their own preferences"
on public.user_preferences for update to authenticated
using ((select auth.uid())::text = user_id)
with check ((select auth.uid())::text = user_id);

create policy "Users read their own preferences"
on public.user_preferences for select to authenticated
using ((select auth.uid())::text = user_id);

Add these methods to the SnapCloudStarter script and call them from onSignedIn():

async savePreferences(preferences: any) {
await this.client.from('user_preferences').upsert(
{
user_id: this.uid,
preferences: JSON.stringify(preferences),
updated_at: new Date().toISOString(),
},
{ onConflict: 'user_id' }
);
}

async loadPreferences(): Promise<any> {
const { data } = await this.client
.from('user_preferences')
.select('preferences')
.eq('user_id', this.uid)
.limit(1);
return data?.length ? JSON.parse(data[0].preferences) : null;
}

// For example, in onSignedIn():
// await this.savePreferences({ volume: 0.8, colorMode: 'vivid' });
// const prefs = await this.loadPreferences();
// if (prefs) print('Loaded preferences: volume=' + prefs.volume);

Log events to a Snap Cloud table

An event log is a table you only append to: one row per action, with its data and the session it came from. The Snap Cloud Examples package uses a user_interactions table with the columns id (uuid, primary key), action, data, timestamp, and session_id (all text). It has no user column, so its INSERT policy allows any signed-in user:

create policy "Signed-in users log interactions"
on public.user_interactions for insert to authenticated
with check (true);

Add this method to the SnapCloudStarter script and call it whenever something happens, for example await this.logInteraction('session_start', { source: 'lens' }):

async logInteraction(action: string, data: any) {
await this.client.from('user_interactions').insert({
action,
data: JSON.stringify(data),
timestamp: new Date().toISOString(),
session_id: 'lens_' + Date.now(),
});
}