Repository files navigation

Synchronization between local IndexedDB and MySQL Database.

With optional user authentication. Powered byDexie.js andPHP CRUD API.

1. InstallDocker andNode.js

2. Open the Terminal and copy paste:

   git clone https://github.com/scriptPilot/dexie-mysql-sync.gitcd dexie-mysql-sync&& npm install&&cd demo-app&& npm install&& npm run dev

3. Open theDemo App athttp://localhost:5173 in multiple browsers and play with the synchronization.

   Test theuser management. Register, login, see how personal data is managed well, logout.

   OpenphpMyAdmin athttp://localhost:8080, login withroot:root and take a look at the database.

1. Create a new app project:

   npm create vite

2. Add a PHP and MySQL backend:

   npx add-php-backend

3. Install required dependencies:

   npm installnpm install dexienpm install dexie-mysql-sync

Based on the installation path above.

1. Modify theschema.sql file:

   CREATETABLEIF NOT EXISTS`tasks` (-- Required columns per table`id`VARCHAR(36)NOT NULLPRIMARY KEY,`userId`INTEGER(8)NOT NULL DEFAULT0,`$created`BIGINT(14)NOT NULL DEFAULT0,`$updated`BIGINT(14)NOT NULL DEFAULT0,`$deleted`INTEGER(1)NOT NULL DEFAULT0,`$synchronized`BIGINT(14)NOT NULL DEFAULT0,-- Optional customized columns per table`title`VARCHAR(255)NOT NULL,`done`INTEGER(1)NOT NULL DEFAULT0) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

2. Create astore.js file:

   // Import Dexie.jsimportDexiefrom'dexie'// Import the sync hookimportSyncfrom'dexie-mysql-sync'// Setup the local database// Adding $created and $deleted as index allows to query on these fieldsconstdb=newDexie('databaseName')db.version(1).stores({tasks:'++id, title, $created, $deleted'})// Start the synchronizationconstsync=newSync()sync.add(db.tasks,'tasks')// Export the database and sync objectsexport{db,sync}

3. Use the database according to theDexie.js documentation, examplemain.js file:

   import{db}from'./store'db.tasks.add({title:'New Task'}).then(db.tasks.where('$deleted').notEqual(1).reverse().sortBy('$created').then(console.log))

Runnpm run dev, openhttp://localhost:5173 and see how the task list is logged to the console.

Open phpMyAdmin athttp://localhost:8080, login withroot:root and take a look at the database.

The required propertiesid,userId,$created,$updated,$deleted and$synchronized are set and updated automatically, you do not need to modify them manually. By default, UUIDv4 is used for new ids.

When the user is authenticated withlogin(), new records will get theuserId property automatically and allread,list,update anddelete requests are limited to the users records (seeMulti Tenancy Documentation).

Intializes the synchronization API.

• endpoint:<string>,optional,PHP CRUD API endpoint, internal or external, default/api.php

importSyncfrom'dexie-mysql-sync'constsync=newSync()

Starts the synchronization to and from remote. Multiple browser windows are supported.

• table:Dexie.js Table
• path:<string>
  • basic usage
    • MySQL table name, example:tasks
  • with sync direction
    • prefixto: to sync only from local to remote, example:to:tasks
    • prefixfrom: to sync only from remote to local, example:from:tasks
• options:<object>optional
  • interval:<number>, default1000 milliseconds
  • batchSize:<number>, default10 documents, decrease to avoid memory issues with large documents

A local table can be synchronized with only one remote table.

A remote table can be synchronized with one or more local tables.

Removes all records from a local table without synchronizing them as deleted to the server.

• table:Dexie.js Table

Resets all synchronizations. All local and remote documents are synchronized again.

• database:Dexie.js Database

See:Database Authentication Documentation for the PHP CRUD API

Creates a new user.

Logs the user in, clears all local tables and resets the synchronization.

Updates the password of the user.

Returns the use details or null.

• callback:<function>optional, callback on any user change with user details or null

Logs the user out, clears all local tables and resets the synchronization.

If you are interested in the internal flows, here you are.

1. Apply changes to the code
2. Apply changes to theREADME.md file, flowcharts and screenshots
3. Commit changes with an issue (closure) reference
4. Runnpm version patch | minor | major and push changes
5. Let the workflow manage the release to GitHub and NPM