---
title: "Recipe: User Preferences (Upsert)"
description: "Store per-user settings in teenybase with upsert: the edit/:id endpoint with or: 'INSERT' creates the row if it is missing and updates it if it exists."
url: https://teenybase.com/docs/recipes/upsert
last_updated: 2026-04-19
---

> Documentation index: https://teenybase.com/llms.txt lists every teenybase docs page with a link to its Markdown version. All docs in one file: https://teenybase.com/llms-full.txt

# User Preferences (Upsert)

Store per-user settings as key-value pairs. The `edit/:id` endpoint with `or: 'INSERT'` gives you upsert behavior -- create if missing, update if exists.

### Config

```typescript
{
    name: 'preferences',
    autoSetUid: false,  // we'll use a composite key pattern
    fields: [
        { name: 'id', type: 'text', sqlType: 'text', primary: true, notNull: true,
          usage: 'record_uid', noUpdate: true },
        { name: 'created', type: 'date', sqlType: 'timestamp',
          default: sql`CURRENT_TIMESTAMP`, notNull: true, usage: 'record_created',
          noInsert: true, noUpdate: true },
        { name: 'updated', type: 'date', sqlType: 'timestamp',
          default: sql`CURRENT_TIMESTAMP`, notNull: true, usage: 'record_updated',
          noInsert: true, noUpdate: true },
        { name: 'user_id', type: 'relation', sqlType: 'text', notNull: true,
          foreignKey: { table: 'users', column: 'id', onDelete: 'CASCADE' } },
        { name: 'key', type: 'text', sqlType: 'text', notNull: true },
        { name: 'val', type: 'text', sqlType: 'text' },
    ],
    triggers: [createdTrigger, updatedTrigger],
    indexes: [
        { fields: 'user_id' },
        { fields: 'key' },
    ],
    extensions: [{
        name: 'rules',
        listRule: 'auth.uid == user_id',
        viewRule: 'auth.uid == user_id',
        createRule: 'auth.uid != null & user_id == auth.uid',
        updateRule: 'auth.uid == user_id',
        deleteRule: 'auth.uid == user_id',
    } as TableRulesExtensionData],
}
```

### API calls

```bash
# Upsert a preference — creates if the ID doesn't exist, replaces if it does
curl -X POST http://localhost:8787/api/v1/table/preferences/edit/USER_ID_theme \
  -H 'Authorization: Bearer <token>' \
  -H 'Content-Type: application/json' \
  -d '{"setValues": {"user_id": "USER_ID", "key": "theme", "val": "dark"}, "or": "INSERT"}'

# Get all preferences for a user
curl 'http://localhost:8787/api/v1/table/preferences/select?where=user_id%3D%22USER_ID%22' \
  -H 'Authorization: Bearer <token>'
```