Defining Schemas

A schema is the blueprint for your database. It defines the tables, columns, and relationships that structure your data. In Tauri SQLite ORM, your schema is defined declaratively using TypeScript objects and helper functions.

It's best practice to keep all your table definitions in a dedicated file, like src/lib/db/schema.ts, and export them.

The sqliteTable Helper

Every table is defined using the sqliteTable function. It takes two arguments:

1. Table Name: A string representing the actual table name in the SQLite database.
2. Columns Object: An object where each key-value pair defines a column.

import { sqliteTable, text, integer } from '@type32/tauri-sqlite-orm';

export const users = sqliteTable('users', {
  // Column definitions go here
  id: integer('id'),
  fullName: text('full_name'),
});

Column Naming Convention

Notice the pattern in the example above:

• The object key (fullName) is the property name you will use in your TypeScript code. The recommended convention is camelCase.
• The argument to the column helper ('full_name') is the column name in the database. The recommended convention for SQL is snake_case.

This separation allows you to adhere to best practices in both environments while the ORM handles the mapping.

A Complete Schema Example

Here is what a more complete schema file with multiple related tables might look like. We define users and posts tables, where each post belongs to a user.

import { sqliteTable, text, integer } from '@type32/tauri-sqlite-orm';

// ---- Users Table ----
export const users = sqliteTable('users', {
  id: integer('id').primaryKey().autoincrement(),
  fullName: text('full_name').notNull(),
  email: text('email').notNull().unique(),
  createdAt: integer('created_at', { mode: 'timestamp' })
    .notNull()
    .$defaultFn(() => new Date()),
});


// ---- Posts Table ----
export const posts = sqliteTable('posts', {
  id: integer('id').primaryKey().autoincrement(),
  title: text('title').notNull(),
  content: text('content'),
  
  // Foreign key relationship
  authorId: integer('author_id')
    .notNull()
    .references(() => users._.columns.id), // <-- This creates the link (Drizzle-style lazy getter)
});

Don't worry about all the modifiers like .primaryKey() and .references() just yet. They are covered in detail on the next page, Column Types & Modifiers.