Database Schema

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:

Constructor	Description
`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

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

Get Started

Core concepts

Database

SDK Reference

Advanced

Troubleshooting

Who's using PocketSync

Overview

Core components

ColumnType

TableColumn

Factory constructors

TableReference

Index

TableSchema

Virtual Tables

DatabaseSchema

Using schemas with PocketSync

Best practices

​Overview

​Core components

​ColumnType

​TableColumn

​Factory constructors

​TableReference

​Index

​TableSchema

​Virtual Tables

​DatabaseSchema

​Using schemas with PocketSync

​Best practices

Overview

Core components

ColumnType

TableColumn

Factory constructors

TableReference

Index

TableSchema

Virtual Tables

DatabaseSchema

Using schemas with PocketSync

Best practices