How to create unique index?

[AlexanderBykin]

Hi

Is there a way to create UNIQUE index on some table's field?

When i try to create unique index with execute mehod i've receive SqliteException(1): while preparing statement, views may not be indexed, SQL logic error (code 1)

[kobiebotha]

Hi there, unfortunately not using the schema definition API. Under the hood PowerSync creates views using the client schema definition which is why you are getting that error.

Are you looking to create unique indexes before enabling sync with a backend database (e.g. in a use case similar to our optional sync demo)? Or do you want to add it for a PowerSyncDatabase that is already connected/synced?

[AlexanderBykin]

i already created local table with schema command Table.localOnly and try to create unique index after database initialization

_db = PowerSyncDatabase(
        schema: databaseSchema,
        path: _databasePath,
        logger: attachedLogger,
      );
...
 await _db.initialize();
 await _updateDatabase(); // <- here is my index creation query

with command CREATE UNIQUE INDEX IF NOT EXISTS ux_recent_search ON recent_search(query) before connection. Yes in my project i use PowerSyncDatabase. My idea is to store users unique search queries (without sync to backend) to show them later when user input something in the fields to make suggestions. As i see with current implementation it's not possible becasue all queries looks like prepared statements and i need some access to low-level API to do that. Any idea how to do that?

[rkistner]

If you're not syncing these queries at all, I'd recommend just creating a standard SQLite table for that. The PowerSyncDatabase is a standard SQLite database, so you can do pretty-much anything you can with normal SQLite.

You can see an example of creating tables using migrations here: https://github.com/powersync-ja/powersync.dart/blob/main/demos/supabase-todolist/lib/migrations/fts_setup.dart

[AlexanderBykin]

@rkistner @kobiebotha thank you, i will do that with migrations