Val Town uses Turso (libSQL), which is mostly SQLite-compatible but has some syntax preferences:

-- ✅ CORRECT
create table if not exists items (...)

-- ❌ AVOID
CREATE TABLE IF NOT EXISTS items (...)

Use lowercase type names:

-- ✅ CORRECT
id text primary key
name text not null
count integer default 0

-- ❌ AVOID
id TEXT PRIMARY KEY
name TEXT NOT NULL
count INTEGER DEFAULT 0

SQLite automatically increments INTEGER PRIMARY KEY:

-- ✅ CORRECT (auto-increments)
id integer primary key

-- ❌ UNNECESSARY
id integer primary key autoincrement

Store UUIDs as text, generate with crypto.randomUUID():

-- ✅ CORRECT
id text primary key

-- In JavaScript:
const id = crypto.randomUUID();
await sqlite.execute({
  sql: 'insert into items (id, name) values (?, ?)',
  args: [id, 'Item name']
});

-- ✅ CORRECT - Simple foreign key
create table if not exists items (
  id text primary key,
  category_id text not null,
  foreign key (category_id) references categories(id)
)

-- ✅ CORRECT - With cascade
create table if not exists occurrences (
  id text primary key,
  item_id text not null,
  foreign key (item_id) references items(id) on delete cascade
)

-- ✅ CORRECT
create index if not exists idx_items_category on items(category_id);

-- ✅ CORRECT - Composite index
create index if not exists idx_items_cat_arch on items(category_id, archived);

-- ✅ CORRECT - Unique index
create unique index if not exists idx_categories_name on categories(name);

-- ✅ CORRECT
archived integer default 0
created_at text not null

-- For timestamps, use datetime in queries:
await sqlite.execute({
  sql: 'insert into items (created_at) values (?)',
  args: [new Date().toISOString()]
});

-- ✅ CORRECT - Column constraint
name text not null unique

-- ✅ CORRECT - Table constraint
create table categories (
  id text primary key,
  name text not null,
  unique(name)
)

-- Categories
create table if not exists categories (
  id text primary key,
  name text not null unique,
  created_at text not null
);

-- Items
create table if not exists items (
  id text primary key,
  category_id text not null,
  name text not null,
  description text,
  created_at text not null,
  archived integer default 0,
  foreign key (category_id) references categories(id)
);

-- Occurrences
create table if not exists occurrences (
  id text primary key,
  item_id text not null,
  occurrence_date text not null,
  note text,
  created_at text not null,
  foreign key (item_id) references items(id) on delete cascade
);

-- Indexes
create index if not exists idx_items_category_id on items(category_id);
create index if not exists idx_items_archived on items(archived);
create index if not exists idx_occurrences_item_id on occurrences(item_id);
create index if not exists idx_occurrences_date on occurrences(occurrence_date);

import { sqlite } from "https://esm.town/v/std/sqlite";

// Create tables (idempotent - safe to run multiple times)
await sqlite.execute(`
  create table if not exists categories (
    id text primary key,
    name text not null unique,
    created_at text not null
  )
`);

// Method 1: Positional parameters (?)
await sqlite.execute({
  sql: 'insert into categories (id, name, created_at) values (?, ?, ?)',
  args: [crypto.randomUUID(), 'Health', new Date().toISOString()]
});

// Method 2: Named parameters (:name)
await sqlite.execute({
  sql: 'insert into categories (id, name, created_at) values (:id, :name, :created_at)',
  args: {
    id: crypto.randomUUID(),
    name: 'Health',
    created_at: new Date().toISOString()
  }
});

// Simple query
const result = await sqlite.execute('select * from categories');
console.log(result.columns); // ['id', 'name', 'created_at']
console.log(result.rows);    // Array of arrays

// With parameters
const result = await sqlite.execute({
  sql: 'select * from items where category_id = ?',
  args: [categoryId]
});

// Convert rows to objects
const items = result.rows.map(row => 
  Object.fromEntries(
    row.map((value, index) => [result.columns[index], value])
  )
);

await sqlite.execute({
  sql: 'update items set name = ?, description = ? where id = ?',
  args: [newName, newDescription, itemId]
});

await sqlite.execute({
  sql: 'delete from occurrences where id = ?',
  args: [occurrenceId]
});

await sqlite.batch([
  'create table if not exists items (...)',
  {
    sql: 'insert into items (id, name) values (?, ?)',
    args: [id1, name1]
  },
  {
    sql: 'insert into items (id, name) values (?, ?)',
    args: [id2, name2]
  }
]);

const result = await sqlite.execute(`
  select 
    i.*,
    c.name as category_name,
    max(o.occurrence_date) as latest_occurrence,
    count(o.id) as total_occurrences
  from items i
  join categories c on c.id = i.category_id
  left join occurrences o on o.item_id = i.id
  where i.archived = 0
  group by i.id
  order by latest_occurrence desc
`);

const result = await sqlite.execute({
  sql: `
    select 
      o.id,
      o.occurrence_date,
      o.note,
      o.created_at
    from occurrences o
    where o.item_id = ?
    order by o.occurrence_date desc
  `,
  args: [itemId]
});

const searchTerm = '%' + query + '%';
const result = await sqlite.execute({
  sql: `
    select distinct i.*
    from items i
    left join occurrences o on o.item_id = i.id
    where i.archived = 0
      and (
        i.name like ? or
        i.description like ? or
        o.note like ?
      )
  `,
  args: [searchTerm, searchTerm, searchTerm]
});

Cause: Table hasn't been created yet Solution: Run your CREATE TABLE statements first

Cause: Trying to insert duplicate value in unique column Solution: Check for existing record first, or use INSERT OR REPLACE

Cause: Referenced record doesn't exist Solution: Create parent record first (e.g., category before item)

Cause: Using uppercase keywords (Val Town prefers lowercase) Solution: Use lowercase: text not TEXT

1. Always use if not exists in CREATE statements
2. Always use parameterized queries - never string concatenation
3. Use transactions (batch) for multiple related operations
4. Create indexes on foreign keys and frequently queried columns
5. Use ISO 8601 dates - new Date().toISOString()
6. Generate UUIDs - crypto.randomUUID()
7. Test your queries in isolation before integrating

// Store dates as ISO 8601 strings
const now = new Date().toISOString(); // "2024-11-14T10:30:45.123Z"

// For date-only (YYYY-MM-DD)
const today = new Date().toISOString().split('T')[0]; // "2024-11-14"

// Query by date
await sqlite.execute({
  sql: 'select * from occurrences where occurrence_date = ?',
  args: [today]
});

// Date range query
await sqlite.execute({
  sql: 'select * from occurrences where occurrence_date between ? and ?',
  args: [startDate, endDate]
});

1. Use indexes on columns you filter/sort by
2. Limit result sets for large tables
3. Use batch operations instead of many individual queries
4. Avoid SELECT * - select only needed columns
5. Use prepared statements (automatic with Val Town sqlite)

// Log query results
const result = await sqlite.execute('select * from items');
console.log('Columns:', result.columns);
console.log('Rows:', result.rows);
console.log('Rows affected:', result.rowsAffected);

// Check table schema
const schema = await sqlite.execute(`
  select sql from sqlite_master 
  where type='table' and name='items'
`);
console.log(schema.rows[0][0]);