PocketSync provides a set of classes to define and manage your database schema in a type-safe way. This approach gives you better control over your database structure and helps prevent errors.

​
Overview

The schema system allows you to:
  • Define table structures with strongly-typed columns
  • Create indexes for better query performance
  • Define relationships between tables
  • Generate SQL statements for creating tables and indexes

​
Core components

​
ColumnType

An enum representing the supported data types for database columns. 
enum ColumnType {
  /// Text data type.
  text,

  /// Integer data type.
  integer,

  /// Real (floating point) data type.
  real,

  /// Binary large object data type.
  blob,

  /// Boolean data type (stored as INTEGER in SQLite).
  boolean,

  /// Date time data type (stored as INTEGER timestamp in SQLite).
  datetime,
}

​
TableColumn

Represents a column in a database table with its properties and constraints. 
// Create a primary key column
final idColumn = TableColumn.primaryKey(
  name: 'id',
  type: ColumnType.integer,
  isAutoIncrement: true,
);

// Create a text column
final titleColumn = TableColumn.text(
  name: 'title',
  isNullable: false,
);

// Create a boolean column
final completedColumn = TableColumn.boolean(
  name: 'completed',
  defaultValue: false,
);

// Create a datetime column
final createdAtColumn = TableColumn.datetime(
  name: 'created_at',
  isNullable: false,
);

​
Factory constructors

TableColumn provides several factory constructors for creating columns of different types:
ConstructorDescription
TableColumn.primaryKey()Creates a primary key column
TableColumn.text()Creates a text column
TableColumn.integer()Creates an integer column
TableColumn.real()Creates a real (floating point) column
TableColumn.boolean()Creates a boolean column (stored as INTEGER)
TableColumn.blob()Creates a blob column
TableColumn.datetime()Creates a datetime column (stored as INTEGER timestamp)
TableColumn.foreignKey()Creates a foreign key column

​
TableReference

Represents a reference to another table and column, used for defining foreign keys. 
// Create a reference to the users table
final userReference = TableReference(
  table: 'users',
  column: 'id',
  onDelete: 'CASCADE',
);

// Create a foreign key column
final userIdColumn = TableColumn.foreignKey(
  name: 'user_id',
  type: ColumnType.integer,
  references: userReference,
  isNullable: false,
);

​
Index

Represents an index on a database table for improved query performance. 
// Create a simple index
final titleIndex = Index(
  name: 'idx_todos_title',
  columns: ['title'],
);

// Create a unique index
final uniqueIndex = Index.unique(
  name: 'idx_users_email',
  columns: ['email'],
);

// Create an index with a WHERE clause
final activeUsersIndex = Index(
  name: 'idx_users_active',
  columns: ['name', 'email'],
  where: 'is_active = 1',
);

​
TableSchema

Represents the schema for a database table, including its columns and indexes. 
// Create a table schema
final todosTable = TableSchema(
  name: 'todos',
  columns: [
    TableColumn.primaryKey(
      name: 'id',
      type: ColumnType.integer,
      isAutoIncrement: true,
    ),
    TableColumn.text(
      name: 'title',
      isNullable: false,
    ),
    TableColumn.boolean(
      name: 'completed',
      defaultValue: false,
    ),
    TableColumn.datetime(
      name: 'created_at',
      isNullable: false,
    ),
    TableColumn.foreignKey(
      name: 'user_id',
      type: ColumnType.integer,
      references: TableReference(
        table: 'users',
        column: 'id',
        onDelete: 'CASCADE',
      ),
      isNullable: false,
    ),
  ],
  indexes: [
    Index(
      name: 'idx_todos_title',
      columns: ['title'],
    ),
    Index(
      name: 'idx_todos_user',
      columns: ['user_id'],
    ),
  ],
);

​
Virtual Tables

SQLite supports virtual tables for special use cases like full-text search. You can create a virtual table schema using the TableSchema.virtual() constructor: 
// Create a FTS5 virtual table for full-text search
final searchTable = TableSchema.virtual(
  name: 'todos_fts',
  module: 'fts5',
  moduleArgs: ['title', 'content'],
);

​
DatabaseSchema

Represents the complete schema for a database, including all tables. 
// Create a database schema
final schema = DatabaseSchema(
  tables: [
    usersTable,
    todosTable,
    searchTable,
  ],
);

// Get a table schema by name
final table = schema.getTable('todos');

​
Using schemas with PocketSync

You can use the schema classes to define your database structure and generate SQL statements for creating tables and indexes: 
final schema = DatabaseSchema(
  tables: [
    TableSchema(
      name: 'todos',
      columns: [
        TableColumn.primaryKey(name: 'id', type: ColumnType.integer),
        TableColumn.text(name: 'title'),
        TableColumn.boolean(name: 'isCompleted'),
      ],
      indexes: [
        Index(
          name: 'idx_todos_title',
          columns: ['title'],
        ),
      ],
    ),
  ],
);

final databaseOptions = DatabaseOptions(
  version: 1,
  dbPath: 'path/to/database.db',
  schema: schema,
);

​
Best practices

  1. Define schemas in a central location: Keep all your table schemas in a dedicated file or class for better organization.
  2. Use meaningful names: Choose clear, descriptive names for tables, columns, and indexes.
  3. Add indexes for frequently queried columns: This improves query performance, especially for large tables.
  4. Use foreign keys for relationships: This helps maintain data integrity between related tables.
  5. Consider using a repository pattern: Combine schemas with a repository pattern for a clean, maintainable data access layer.