Dexie MySQL Synchronization

Synchronization between local IndexedDB and MySQL Database.

With optional user authentication. Powered by Dexie.js and PHP CRUD API.

Demo

1. Install Docker and Node.js

2. Open the Terminal and copy paste:

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

3. Open the Demo App at http://localhost:5173 in multiple browsers and play with the synchronization.

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

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

Installation

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 install
   npm install dexie
   npm install dexie-mysql-sync

Usage

Based on the installation path above.

1. Modify the schema.sql file:

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

2. Create a store.js file:

   // Import Dexie.js
   import Dexie from 'dexie'
   
   // Import the sync hook
   import Sync from 'dexie-mysql-sync'
   
   // Setup the local database
   // Adding $created and $deleted as index allows to query on these fields
   const db = new Dexie('databaseName')
   db.version(1).stores({
     tasks: '++id, title, $created, $deleted'
   })
   
   // Start the synchronization
   const sync = new Sync()
   sync.add(db.tasks, 'tasks')
   
   // Export the database and sync objects
   export { db, sync }

3. Use the database according to the Dexie.js documentation, example main.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)
     )

Run npm run dev, open http://localhost:5173 and see how the task list is logged to the console.

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

The required properties id, 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 with login(), new records will get the userId property automatically and all read, list, update and delete requests are limited to the users records (see Multi Tenancy Documentation).

Class Details

Sync(endpoint)

Intializes the synchronization API.

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

import Sync from 'dexie-mysql-sync'
const sync = new Sync()

Function Details

Synchronization

sync.add(table, path, options)

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
    • prefix to: to sync only from local to remote, example: to:tasks
    • prefix from: to sync only from remote to local, example: from:tasks
• options: <object> optional
  • interval: <number>, default 1000 milliseconds
  • batchSize: <number>, default 10 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.

sync.emptyTable(table)

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

• table: Dexie.js Table

sync.reset()

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

• database: Dexie.js Database

Authentication

See: Database Authentication Documentation for the PHP CRUD API

sync.register(username, password)

Creates a new user.

sync.login(username, password)

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

sync.password(username, password, newPassword)

Updates the password of the user.

sync.user(callback)

Returns the use details or null.

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

sync.logout()

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

Flowcharts

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

Table Hooks

Synchronization

Maintainer

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