This requires creating a separate FTS5 table(s) to index the data, and updating the table(s) using SQLite triggers.
Note that the availability of FTS is dependent on the underlying sqlite package used, as it is an extension that must first be enabled in the package.
Full-text search is currently available in the following client SDKs, and we plan to extend support to all SDKs in the near future:
Example Implementations
FTS is implemented in the following demo apps:
We explain these implementations in more detail below. Example code is shown mainly in Dart, but references to the React or React Native equivalents are included where relevant, so you should be able to cross-reference.
Walkthrough: Full-text search in the To-Do List Demo App
Setup
FTS tables are created when instantiating the client-side PowerSync database (DB).
// https://github.com/powersync-ja/powersync.dart/blob/master/demos/supabase-todolist/lib/powersync.dart#L186
Future<void> openDatabase() async {
...
await configureFts(db);
}
// https://github.com/powersync-ja/powersync.dart/blob/master/demos/supabase-todolist/lib/powersync.dart#L186
Future<void> openDatabase() async {
...
await configureFts(db);
}
// https://github.com/powersync-ja/powersync-js/blob/main/demos/react-supabase-todolist/src/components/providers/SystemProvider.tsx#L41
SystemProvider = ({ children }: { children: React.ReactNode }) => {
...
React.useEffect(() => {
...
configureFts();
})
}
// https://github.com/powersync-ja/powersync-js/blob/main/demos/react-native-supabase-todolist/library/powersync/system.ts#L75
export class System {
...
powersync: PowerSyncDatabase;
...
async init() {
...
await configureFts(this.powersync);
}
}
First, we need to set up the FTS tables to match the lists and todos tables already created in this demo app. Don’t worry if you already have data in the tables, as it will be copied into the new FTS tables.
To simplify implementation these examples make use of SQLite migrations. The migrations are run in migrations/fts_setup.dart in the Flutter implementation. Here we use the sqlite_async Dart package to generate the migrations.
Dart example:
// migrations/fts_setup.dart
/// This is where you can add more migrations to generate FTS tables
/// that correspond to the tables in your schema and populate them
/// with the data you would like to search on
Future<void> configureFts(PowerSyncDatabase db) async {
migrations
..add(createFtsMigration(
migrationVersion: 1,
tableName: 'lists',
columns: ['name'],
tokenizationMethod: 'porter unicode61'))
..add(createFtsMigration(
migrationVersion: 2,
tableName: 'todos',
columns: ['description', 'list_id'],
));
await migrations.migrate(db);
}
The createFtsMigration function is key and corresponds to the below (Dart example):
// migrations/fts_setup.dart
/// Create a Full Text Search table for the given table and columns
/// with an option to use a different tokenizer otherwise it defaults
/// to unicode61. It also creates the triggers that keep the FTS table
/// and the PowerSync table in sync.
SqliteMigration createFtsMigration(
{required int migrationVersion,
required String tableName,
required List<String> columns,
String tokenizationMethod = 'unicode61'}) {
String internalName =
schema.tables.firstWhere((table) => table.name == tableName).internalName;
String stringColumns = columns.join(', ');
return SqliteMigration(migrationVersion, (tx) async {
// Add FTS table
await tx.execute('''
CREATE VIRTUAL TABLE IF NOT EXISTS fts_$tableName
USING fts5(id UNINDEXED, $stringColumns, tokenize='$tokenizationMethod');
''');
// Copy over records already in table
await tx.execute('''
INSERT INTO fts_$tableName(rowid, id, $stringColumns)
SELECT rowid, id, ${generateJsonExtracts(ExtractType.columnOnly, 'data', columns)}
FROM $internalName;
''');
// Add INSERT, UPDATE and DELETE and triggers to keep fts table in sync with table
await tx.execute('''
CREATE TRIGGER IF NOT EXISTS fts_insert_trigger_$tableName AFTER INSERT
ON $internalName
BEGIN
INSERT INTO fts_$tableName(rowid, id, $stringColumns)
VALUES (
NEW.rowid,
NEW.id,
${generateJsonExtracts(ExtractType.columnOnly, 'NEW.data', columns)}
);
END;
''');
await tx.execute('''
CREATE TRIGGER IF NOT EXISTS fts_update_trigger_$tableName AFTER UPDATE
ON $internalName BEGIN
UPDATE fts_$tableName
SET ${generateJsonExtracts(ExtractType.columnInOperation, 'NEW.data', columns)}
WHERE rowid = NEW.rowid;
END;
''');
await tx.execute('''
CREATE TRIGGER IF NOT EXISTS fts_delete_trigger_$tableName AFTER DELETE
ON $internalName BEGIN
DELETE FROM fts_$tableName WHERE rowid = OLD.rowid;
END;
''');
});
}
After this is run, you should have the following tables and triggers in your SQLite DB:
FTS tables and migrations
FTS Search Delegate
To show off this new functionality, we have incorporated FTS into the search button at the top of the screen in the To-Do List demo app:
Clicking on the search icon will open a search bar which will allow you to search for lists or todos that you have generated.
It uses a custom search delegate widget found in widgets/fts_search_delegate.dart (Flutter) and widgets/SearchBarWidget.tsx (Web) to display the search results.
FTS Helper
We added a helper in lib/fts_helpers.dart (Flutter) and utils/fts_helpers.ts (Web) that allows you to add additional search functionality which can be found in the SQLite FTS5 extension documentation.
Dart example:
// lib/fts_helpers.dart
String _createSearchTermWithOptions(String searchTerm) {
// adding * to the end of the search term will match any word that starts with the search term
// e.g. searching bl will match blue, black, etc.
// consult FTS5 Full-text Query Syntax documentation for more options
String searchTermWithOptions = '$searchTerm*';
return searchTermWithOptions;
}
/// Search the FTS table for the given searchTerm and return results ordered by the
/// rank of their relevance
Future<List> search(String searchTerm, String tableName) async {
String searchTermWithOptions = _createSearchTermWithOptions(searchTerm);
return await db.execute(
'SELECT * FROM fts_$tableName WHERE fts_$tableName MATCH ? ORDER BY rank',
[searchTermWithOptions]);
}
