Dexie-mysql-sync NPM

Dexie MySQL Synchronization

Synchronization between local IndexedDB and MySQL Database.

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

Demo

Install Docker and Node.js

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

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

Create a new app project:
```
npm create vite
```
Add a PHP and MySQL backend:
```
npx add-php-backend
```

Install required dependencies:

npm install
npm install dexie
npm install dexie-mysql-sync

Usage

Based on the installation path above.

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;

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 }

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