Skip to content

Latest commit

 

History

History
565 lines (351 loc) · 18 KB

API.md

File metadata and controls

565 lines (351 loc) · 18 KB

API Reference

This document provides a reference for the SQLite functions provided by the sqlite-sync extension.

Index

Configuration Functions

cloudsync_init(table_name, [crdt_algo], [force])

Description: Initializes a table for sqlite-sync synchronization. This function is idempotent and needs to be called only once per table on each site; configurations are stored in the database and automatically loaded with the extension.

Before initialization, cloudsync_init performs schema sanity checks to ensure compatibility with CRDT requirements and best practices. These checks include:

  • Primary keys should not be auto-incrementing integers; GUIDs (UUIDs, ULIDs) are highly recommended to prevent multi-node collisions.
  • All non-primary key NOT NULL columns must have a DEFAULT value.
  • Note: Any write operation that includes a NULL value for a primary key column will be rejected with an error, even if SQLite would normally allow it due to a legacy behavior.

Schema Design Considerations:

When designing your database schema for SQLite Sync, follow these essential requirements:

  • Primary Keys: Use TEXT primary keys with cloudsync_uuid() for globally unique identifiers. Avoid auto-incrementing integers.
  • Column Constraints: All NOT NULL columns (except primary keys) must have DEFAULT values to prevent synchronization errors.
  • UNIQUE Constraints: In multi-tenant scenarios, use composite UNIQUE constraints (e.g., UNIQUE(tenant_id, email)) instead of global uniqueness.
  • Foreign Key Compatibility: Be aware of potential conflicts during CRDT merge operations and RLS policy interactions.
  • Trigger Compatibility: Triggers may cause duplicate operations or be called multiple times due to column-by-column processing.

For comprehensive guidelines, see the Database Schema Recommendations section in the README.

The function supports three overloads:

  • cloudsync_init(table_name): Uses the default 'cls' CRDT algorithm.
  • cloudsync_init(table_name, crdt_algo): Specifies a CRDT algorithm ('cls', 'dws', 'aws', 'gos').
  • cloudsync_init(table_name, crdt_algo, force): Specifies an algorithm and, if force is true (or 1), skips the integer primary key check (use with caution, GUIDs are strongly recommended).

Parameters:

  • table_name (TEXT): The name of the table to initialize.
  • crdt_algo (TEXT, optional): The CRDT algorithm to use. Can be "cls", "dws", "aws", "gos". Defaults to "cls".
  • force (BOOLEAN, optional): If true (or 1), it skips the check that prevents the use of a single-column INTEGER primary key. Defaults to false. It is strongly recommended to use globally unique primary keys instead of integers.

Returns: None.

Example:

-- Initialize a single table for synchronization with the Causal-Length Set (CLS) Algorithm (default)
SELECT cloudsync_init('my_table');

-- Initialize a single table for synchronization with a different algorithm Delete-Wins Set (DWS)
SELECT cloudsync_init('my_table', 'dws');

cloudsync_enable(table_name)

Description: Enables synchronization for the specified table.

Parameters:

  • table_name (TEXT): The name of the table to enable.

Returns: None.

Example:

SELECT cloudsync_enable('my_table');

cloudsync_disable(table_name)

Description: Disables synchronization for the specified table.

Parameters:

  • table_name (TEXT): The name of the table to disable.

Returns: None.

Example:

SELECT cloudsync_disable('my_table');

cloudsync_is_enabled(table_name)

Description: Checks if synchronization is enabled for the specified table.

Parameters:

  • table_name (TEXT): The name of the table to check.

Returns: 1 if enabled, 0 otherwise.

Example:

SELECT cloudsync_is_enabled('my_table');

cloudsync_cleanup(table_name)

Description: Removes the sqlite-sync synchronization mechanism from a specified table or all tables. This operation drops the associated _cloudsync metadata table and removes triggers from the target table(s). Use this function when synchronization is no longer desired for a table.

Parameters:

  • table_name (TEXT): The name of the table to clean up.

Returns: None.

Example:

-- Clean up a single table
SELECT cloudsync_cleanup('my_table');

cloudsync_terminate()

Description: Releases all internal resources used by the sqlite-sync extension for the current database connection. This function should be called before closing the database connection to ensure that all prepared statements and allocated memory are freed. Failing to call this function can result in memory leaks or a failed sqlite3_close operation due to pending statements.

Parameters: None.

Returns: None.

Example:

-- Before closing the database connection
SELECT cloudsync_terminate();

Block-Level LWW Functions

cloudsync_set_column(table_name, col_name, key, value)

Description: Configures per-column settings for a synchronized table. This function is primarily used to enable block-level LWW on text columns, allowing fine-grained conflict resolution at the line (or paragraph) level instead of the entire cell.

When block-level LWW is enabled on a column, INSERT and UPDATE operations automatically split the text into blocks using a delimiter (default: newline \n) and track each block independently. During sync, changes are merged block-by-block, so concurrent edits to different parts of the same text are preserved.

Parameters:

  • table_name (TEXT): The name of the synchronized table.
  • col_name (TEXT): The name of the text column to configure.
  • key (TEXT): The setting key. Supported keys:
    • 'algo' — Set the column algorithm. Use value 'block' to enable block-level LWW.
    • 'delimiter' — Set the block delimiter string. Only applies to columns with block-level LWW enabled.
  • value (TEXT): The setting value.

Returns: None.

Example:

-- Enable block-level LWW on a column (splits text by newline by default)
SELECT cloudsync_set_column('notes', 'body', 'algo', 'block');

-- Set a custom delimiter (e.g., double newline for paragraph-level tracking)
SELECT cloudsync_set_column('notes', 'body', 'delimiter', '

');

cloudsync_text_materialize(table_name, col_name, pk_values...)

Description: Reconstructs the full text of a block-level LWW column from its individual blocks and writes the result back to the base table column. This is useful after a merge operation to ensure the column contains the up-to-date materialized text.

After a sync/merge, the column is updated automatically. This function is primarily useful for manual materialization or debugging.

Parameters:

  • table_name (TEXT): The name of the table.
  • col_name (TEXT): The name of the block-level LWW column.
  • pk_values... (variadic): The primary key values identifying the row. For composite primary keys, pass each key value as a separate argument in declaration order.

Returns: 1 on success.

Example:

-- Materialize the body column for a specific row
SELECT cloudsync_text_materialize('notes', 'body', 'note-001');

-- With a composite primary key (e.g., PRIMARY KEY (tenant_id, doc_id))
SELECT cloudsync_text_materialize('docs', 'body', 'tenant-1', 'doc-001');

-- Read the materialized text
SELECT body FROM notes WHERE id = 'note-001';

Helper Functions

cloudsync_version()

Description: Returns the version of the sqlite-sync library.

Parameters: None.

Returns: The library version as a string.

Example:

SELECT cloudsync_version();
-- e.g., '1.0.0'

cloudsync_siteid()

Description: Returns the unique ID of the local site.

Parameters: None.

Returns: The site ID as a BLOB.

Example:

SELECT cloudsync_siteid();

cloudsync_db_version()

Description: Returns the current database version.

Parameters: None.

Returns: The database version as an INTEGER.

Example:

SELECT cloudsync_db_version();

cloudsync_uuid()

Description: Generates a new universally unique identifier (UUIDv7). This is useful for creating globally unique primary keys for new records, which is a best practice for CRDTs.

Parameters: None.

Returns: A new UUID as a TEXT value.

Example:

INSERT INTO products (id, name) VALUES (cloudsync_uuid(), 'New Product');

Schema Alteration Functions

cloudsync_begin_alter(table_name)

Description: Prepares a synchronized table for schema changes. This function must be called before altering the table. Failure to use cloudsync_begin_alter and cloudsync_commit_alter can lead to synchronization errors and data divergence.

Parameters:

  • table_name (TEXT): The name of the table that will be altered.

Returns: None.

Example:

SELECT cloudsync_init('my_table');
-- ... later
SELECT cloudsync_begin_alter('my_table');
ALTER TABLE my_table ADD COLUMN new_column TEXT;
SELECT cloudsync_commit_alter('my_table');

cloudsync_commit_alter(table_name)

Description: Finalizes schema changes for a synchronized table. This function must be called after altering the table's schema, completing the process initiated by cloudsync_begin_alter and ensuring CRDT data consistency.

Parameters:

  • table_name (TEXT): The name of the table that was altered.

Returns: None.

Example:

SELECT cloudsync_init('my_table');
-- ... later
SELECT cloudsync_begin_alter('my_type');
ALTER TABLE my_table ADD COLUMN new_column TEXT;
SELECT cloudsync_commit_alter('my_table');

Network Functions

cloudsync_network_init(managedDatabaseId)

Description: Initializes the sqlite-sync network component. This function configures the endpoints for the CloudSync service and initializes the cURL library.

Parameters:

  • managedDatabaseId (TEXT): The managed database identifier returned by the CloudSync service when a new database is registered for sync. For SQLiteCloud projects, this value can be obtained from the project's OffSync page on the dashboard.

Returns: None.

Example:

SELECT cloudsync_network_init('your-managed-database-id');

cloudsync_network_cleanup()

Description: Cleans up the sqlite-sync network component, releasing all resources allocated by cloudsync_network_init (memory, cURL handles).

Parameters: None.

Returns: None.

Example:

SELECT cloudsync_network_cleanup();

cloudsync_network_set_token(token)

Description: Sets the authentication token to be used for network requests. This token will be included in the Authorization header of all subsequent requests. For more information, refer to the Access Tokens documentation.

Parameters:

  • token (TEXT): The authentication token.

Returns: None.

Example:

SELECT cloudsync_network_set_token('your_auth_token');

cloudsync_network_set_apikey(apikey)

Description: Sets the API key for network requests. This key is included in the Authorization header of all subsequent requests.

Parameters:

  • apikey (TEXT): The API key.

Returns: None.

Example:

SELECT cloudsync_network_set_apikey('your_api_key');

cloudsync_network_send_changes()

Description: Sends all unsent local changes to the remote server.

Parameters: None.

Returns: A JSON string with the send result:

{"send": {"status": "synced|syncing|out-of-sync|error", "localVersion": N, "serverVersion": N}}
  • send.status: The current sync state — "synced" (all changes confirmed), "syncing" (changes sent but not yet confirmed), "out-of-sync" (local changes pending or gaps detected), or "error".
  • send.localVersion: The latest local database version.
  • send.serverVersion: The latest version confirmed by the server.

Example:

SELECT cloudsync_network_send_changes();
-- '{"send":{"status":"synced","localVersion":5,"serverVersion":5}}'

cloudsync_network_check_changes()

Description: Checks the remote server for new changes and applies them to the local database.

If a package of new changes is already available for the local site, the server returns it immediately, and the changes are applied. If no package is ready, the server returns an empty response and starts an asynchronous process to prepare a new package. This new package can be retrieved with a subsequent call to this function.

This function is designed to be called periodically to keep the local database in sync. To force an update and wait for changes (with a timeout), use [cloudsync_network_sync(wait_ms, max_retries)].

If the network is misconfigured or the remote server is unreachable, the function returns an error.

Parameters: None.

Returns: A JSON string with the receive result:

{"receive": {"rows": N, "tables": ["table1", "table2"]}}
  • receive.rows: The number of rows received and applied to the local database.
  • receive.tables: An array of table names that received changes. Empty ([]) if no changes were applied.

Example:

SELECT cloudsync_network_check_changes();
-- '{"receive":{"rows":3,"tables":["tasks"]}}'

cloudsync_network_sync([wait_ms], [max_retries])

Description: Performs a full synchronization cycle. This function has two overloads:

  • cloudsync_network_sync(): Performs one send operation and one check operation.
  • cloudsync_network_sync(wait_ms, max_retries): Performs one send operation and then repeatedly tries to download remote changes until at least one change is downloaded or max_retries times has been reached, waiting wait_ms between retries.

Parameters:

  • wait_ms (INTEGER, optional): The time to wait in milliseconds between retries. Defaults to 100.
  • max_retries (INTEGER, optional): The maximum number of times to retry the synchronization. Defaults to 1.

Returns: A JSON string with the full sync result, combining send and receive:

{
  "send": {"status": "synced|syncing|out-of-sync|error", "localVersion": N, "serverVersion": N},
  "receive": {"rows": N, "tables": ["table1", "table2"]}
}
  • send.status: The current sync state — "synced", "syncing", "out-of-sync", or "error".
  • send.localVersion: The latest local database version.
  • send.serverVersion: The latest version confirmed by the server.
  • receive.rows: The number of rows received and applied during the check phase.
  • receive.tables: An array of table names that received changes. Empty ([]) if no changes were applied.

Example:

-- Perform a single synchronization cycle
SELECT cloudsync_network_sync();
-- '{"send":{"status":"synced","localVersion":5,"serverVersion":5},"receive":{"rows":3,"tables":["tasks"]}}'

-- Perform a synchronization cycle with custom retry settings
SELECT cloudsync_network_sync(500, 3);

cloudsync_network_reset_sync_version()

Description: Resets local synchronization version numbers, forcing the next sync to fetch all changes from the server.

Parameters: None.

Returns: None.

Example:

SELECT cloudsync_network_reset_sync_version();

cloudsync_network_has_unsent_changes()

Description: Checks if there are any local changes that have not yet been sent to the remote server.

Parameters: None.

Returns: 1 if there are unsent changes, 0 otherwise.

Example:

SELECT cloudsync_network_has_unsent_changes();

cloudsync_network_logout()

Description: Logs out the current user and cleans up all local data from synchronized tables. This function deletes and then re-initializes synchronized tables, useful for switching users or resetting the local database. Warning: This function deletes all data from synchronized tables. Use with caution. Consider calling cloudsync_network_has_unsent_changes() before logout to check for unsent local changes and warn the user before data that has not been fully synchronized to the remote server is deleted.

Parameters: None.

Returns: None.

Example:

SELECT cloudsync_network_logout();