Synchronization between local IndexedDB and MySQL Database.
With optional user authentication. Powered by Dexie.js and PHP CRUD API.
-
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.
![Bildschirmfoto 2024-03-01 um 11 07 20](https://private-user-images.githubusercontent.com/19615586/309223476-4d801668-698d-4c2b-b9b1-99838085bb7f.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjMyNzIyNjcsIm5iZiI6MTcyMzI3MTk2NywicGF0aCI6Ii8xOTYxNTU4Ni8zMDkyMjM0NzYtNGQ4MDE2NjgtNjk4ZC00YzJiLWI5YjEtOTk4MzgwODViYjdmLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA4MTAlMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwODEwVDA2MzkyN1omWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPWVhZDZmZTljYmIwNWVjNmQxYzBjY2Q5YjJjMDQ4NmQzMGQyZTRlNjIwNDEwZmUzYzIwNjM2NjFhOTczNDM3OWImWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.WV1fVfL1wrn19Vm5rTNhF1u89KxeA_UL7WEsiFVJ60o)
-
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
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).
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()
Starts the synchronization to and from remote. Multiple browser windows are supported.
table
: Dexie.js Tablepath
:<string>
- basic usage
- MySQL table name, example:
tasks
- MySQL table name, example:
- 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
- prefix
- basic usage
options
:<object>
optionalinterval
:<number>
, default1000
millisecondsbatchSize
:<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.
- Apply changes to the code
- Apply changes to the
README.md
file, flowcharts and screenshots - Commit changes with an issue (closure) reference
- Run
npm version patch | minor | major
and push changes - Let the workflow manage the release to GitHub and NPM