How does it work?
Your application will use a modified version of the SQLite library containing the LiteSync code to access your database.
The modifications on the SQLite library are internal and the interface is the same.
The LiteSync libraries will communicate to each other, exchanging transaction data.

Replication
The first time the app is open it will connect to the other node(s) and download a fresh copy of the database.
In a centralized topology the primary node will send the database copy to the secondary nodes.
Once it is downloaded the node starts the synchronization.
Synchronization
Once the nodes have the same base db they exchange transactions that were executed when they were off-line.
After this they enter in on-line mode and once a new transaction is executed in a node it is transferred to be executed in the connected nodes.
If the node is off-line then the transaction is stored in a local log to be exchanged later.
DO I NEED TO CHANGE MY APP CODE?
There are a few steps but basically we must change the URI string in the database opening from this:
"file:app.db"
to something like this:
"file:app.db?node=secondary&connect=tcp://server.ip:1234"
The good news is that LiteSync uses the native SQLite3 interface. It means that we don't need to use another API.
Connection
Each node has 2 options:
bind to an address
connect to the peer address
So you can choose which side will connect to the other. This is useful when one side is behind a router or firewall.
Supported Topologies
Centralized, Star Topology

In this topology we have a node in which all the other nodes will be connected to, so it must be on-line for the synchronization to take place.
Here are some example configurations:
The primary node can bind to an address and the secondary nodes connect to it.
Primary node:
"file:/home/user/app.db?node=primary&bind=tcp://0.0.0.0:1234"
Secondary Node: (in another device)
"file:/home/user/app.db?node=secondary&connect=tcp://server:1234"
The primary node can also connect to secondary nodes.
Primary node:
"file:/home/user/app.db?node=primary&connect=tcp://address1:port1,tcp://address2:port2"
Secondary Nodes: (each on a separate device)
"file:/home/user/app.db?node=secondary&bind=tcp://0.0.0.0:1234"
We can even use a mix of these 2 options.
Primary node:
"file:/home/user/app.db?node=primary&bind=tcp://0.0.0.0:1234&connect=tcp://address1:port1"
Secondary Node 1:
"file:/home/user/app.db?node=secondary&connect=tcp://server:1234"
Secondary Node 2:
"file:/home/user/app.db?node=secondary&bind=tcp://0.0.0.0:1234"
Peer-to-Peer Topology

The fully-connected peer-to-peer network is made between primary nodes.
We need to inform the total number of nodes on the network manually on each node (for now)
The direction of connections must also be informed (which nodes will connect to which)
Here is an example of a network with 3 nodes:
Node 1:
"file:db1.db?node=primary&total_primary_nodes=3&bind=tcp://0.0.0.0:1201"
Node 2:
"file:db2.db?node=primary&total_primary_nodes=3&bind=tcp://0.0.0.0:1202& connect=tcp://127.0.0.1:1201"
Node 3:
"file:db3.db?node=primary&total_primary_nodes=3&bind=tcp://0.0.0.0:1203& connect=tcp://127.0.0.1:1201,tcp://127.0.0.1:1202"
Mixed Topology

In this topology we have more than one primary nodes connected as peers and many secondary nodes connected to them.
The configuration for the primary nodes is the same as above, in the peer-to-peer topology.
Each secondary node will be connected to a single primary node at a given time. We can inform the address of many primary nodes so they choose one randomly. If the connection to a primary node drops, it will connect to another one.
Here is an example URI for a secondary node:
"file:db4.db?node=secondary&connect=tcp://127.0.0.1:1201,tcp://127.0.0.1:1202,tcp://127.0.0.1:1203"
Synchronization Status
We can check the synchronization status using this command:
PRAGMA sync_status
It returns a JSON string.
Synchronization Notification
Your application can be notified when the local database is updated due to synchronization with remote nodes. The notification is done via a user-defined function.
Select a language -->
static void on_db_update(sqlite3_context *context, int argc, sqlite3_value **argv){ char* changes = sqlite3_value_text(argv[0]); printf("update received: %s\n", changes); } sqlite3_create_function(db, "update_notification", 1, SQLITE_UTF8, NULL, &on_db_update, NULL, NULL);
db.define("update_notification", [](std::string changes) { std::cout << "update received: " << changes << std::endl; });
def on_db_update(changes): print("update received:", changes) con.create_function("update_notification", 1, on_db_update)
// using better-sqlite3: db.on('sync', function(changes) { console.log('update received: ' + changes); });
Function.create(conn, "update_notification", new Function() { protected void xFunc(changes) { System.out.println("update received: " + changes); } });
// using SQLite.NET: db.OnSync((changes) => { // the db received an update. update the screen with new data UpdateScreen(db); }); // using Microsoft.Data.SQLite: db.CreateFunction("update_notification", (changes) => { // notification received on the worker thread // do not access the db connection here // transfer the notification to the main thread Console.WriteLine("update received: " + changes); return 0; });
' Using SQLite.NET: db.OnSync(Function(changes) As Integer ' the db received an update. update the screen with new data UpdateScreen(db) End Function) ' Using Microsoft.Data.SQLite: db.CreateFunction("update_notification", Function(changes) As Integer ' notification received on the worker thread ' do not access the db connection here ' transfer the notification to the main thread Console.WriteLine("update received: " & changes) End Function)
function on_db_update($changes) { echo 'update received: $changes'; } // with sqlite3: $db->createFunction('update_notification', 'on_db_update'); // with pdo_sqlite: $db->sqliteCreateFunction('update_notification', 'on_db_update', 1);
$dbh->sqlite_create_function( 'update_notification', 1, sub { my $changes = shift; # retrieve the argument passed to the function print "update received: $changes"; });
db.create_function "update_notification", 1 do |func, changes| puts "update received: #{changes}" func.result = null end
db.create(function: "update_notification", argc: 1) { args in let changes = args.first as! String println("update received: \(changes)") return nil }
db:create_function('update_notification',1,function(ctx,changes) print('update received: ' .. changes) ctx:result_null() end)
func on_db_update(changes string) int64 { print('update received: ' + changes) return null } sql.Register("sqlite3_custom", &sqlite.SQLiteDriver{ ConnectHook: func(conn *sqlite.SQLiteConn) error { if err := conn.RegisterFunc("update_notification", on_db_update, true); err != nil { return err } return nil }, }) db, err := sql.Open("sqlite3_custom", "file:data.db?node=...")
Important: On some languages the notification function is called by the worker thread. The application should NOT use the db connection inside the notification function and it must return as fast as possible! The application can transfer the notification to the main thread before returning.
Checking if the db is ready
If the app is being open for the first time on a device it can download a new copy of the database from another node. Until it is done we cannot access the db.
We can retrieve the sync status and check the db_is_ready variable.
Check the basic app examples bellow.
How to use it in my app?
There are 3 steps:
1 Replace the SQLite library with the one containing LiteSync
2 Change the URI connection string
3 Check for the db ready status
When compiling C and C++ apps you must link your application to the LiteSync library.
For other languages you must have the proper wrapper installed.
Primary node example
The primary node can be a normal application, exactly the same app as the secondary nodes but using a different URI.
Or we can use an app dedicated to be the primary node.
A basic standalone application used solely for the purpose of keeping a centralized db node would look like this:
Select a language -->
#include <sqlite3.h> char *uri = "file:app.db?node=primary&bind=tcp://0.0.0.0:1234"; int main() { sqlite3 *db; sqlite3_open(uri, &db); /* open the database */ while(1) sleep(1); /* keep the app open */ }
#include <sqlite_modern_cpp.h> #include <thread> #include <chrono> #include <iostream> using namespace sqlite; int main() { try { // open the database database db("file:app.db?node=primary&bind=tcp://0.0.0.0:1234"); // keep the app open while(1) { std::this_thread::sleep_for(std::chrono::seconds(1)); } } catch (std::exception& e) { std::cerr << e.what() << std::endl; } }
import litesync conn = litesync.connect('file:app.db?node=primary&bind=tcp://0.0.0.0:1234') # keep the app open import time while True: time.sleep(60) # in seconds
const uri = 'file:app.db?node=primary&bind=tcp://0.0.0.0:1234'; const options = { verbose: console.log }; const db = require('better-sqlite3-litesync')(uri, options); // keep the app open setInterval(function(){}, 5000);
import java.sql.Connection; import java.sql.DriverManager; public class Sample { public static void main(String[] args) { String uri = "file:app.db?node=primary&bind=tcp://0.0.0.0:1234"; Connection connection = DriverManager.getConnection("jdbc:sqlite:" + uri); // keep the app open while (true) { Thread.sleep(5000); } } }
using SQLite; public class Program { public static void Main() { // open the database var uri = "file:app.db?node=primary&bind=tcp://0.0.0.0:1234"; var db = new SQLiteConnection(uri); // keep the app open while(true) { System.Threading.Thread.Sleep(5000); } } }
Imports SQLite Public Class Program Public Shared Sub Main() ' open the database Dim db As New SQLiteConnection("file:app.db?node=primary&bind=tcp://0.0.0.0:1234") ' keep the app open Do System.Threading.Thread.Sleep(5000) Loop End Sub End Class
Option Explicit Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long) Public Sub Main() Dim URI As String Dim Conn As New ADODB.Connection ' Open the connection URI = "file:C:\app\mydb.db?node=primary&bind=tcp://0.0.0.0:1234" Conn.Open "DRIVER=SQLite3 ODBC Driver;Database=" & URI ' Keep the app open Do: Sleep(5000): Loop End Sub
<?php // with sqlite3: $db = new SQLite3("file:app.db?node=primary&bind=tcp://0.0.0.0:1234"); // with pdo_sqlite: $pdo = new PDO("sqlite:file:app.db?node=primary&bind=tcp://0.0.0.0:1234"); // keep the app open - it should not be used with apache while(1) sleep(5); ?>
use DBI; my $dbh = DBI->connect("dbi:SQLite:uri=file:app.db?node=primary&bind=tcp://0.0.0.0:1234"); // keep the app open - it should not be used with apache sleep;
require 'sqlite3' db = SQLite3::Database.new "file:app.db?node=primary&bind=tcp://0.0.0.0:1234" # keep the app open loop do sleep(1) end
local sqlite3 = require("lsqlite3") local db = sqlite3.open('file:app.db?node=primary&bind=tcp://0.0.0.0:1234') -- keep the app open local lsocket = require("lsocket") while true do lsocket.select(5000) end
package main import ( "database/sql" _ "github.com/litesync/go-sqlite3" "time" ) func main() { db, err := sql.Open("sqlite3", "file:app.db?node=primary&bind=tcp://0.0.0.0:1234") // keep the app open for { time.Sleep(1000 * time.Millisecond) } }
Basic app example
A basic app that writes to the local database would look like this:
Select a language -->
#include <sqlite3.h> char *uri = "file:app.db?node=secondary&connect=tcp://server:port"; int main() { sqlite3 *db; /* open the database */ sqlite3_open(uri, &db); /* check if the db is ready */ while(1){ char *json_str = sqlite3_query_value_str(db, "PRAGMA sync_status", NULL); bool db_is_ready = strstr(json_str, "\"db_is_ready\": true") > 0; sqlite3_free(json_str); if (db_is_ready) break; sleep_ms(250); } /* access the database */ start_access(db); } char * sqlite3_query_value_str(sqlite3 *db, char *sql, char **ppErrMsg) { char *ptr = NULL; sqlite3_stmt *stmt; int rc; if (ppErrMsg) *ppErrMsg = NULL; rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL); if (rc != SQLITE_OK) { if (ppErrMsg) *ppErrMsg = sqlite3_strdup(sqlite3_errmsg(db)); return NULL; } if (sqlite3_step(stmt) == SQLITE_ROW) { char *text = (char *)sqlite3_column_text(stmt, 0); if (text) { ptr = sqlite3_strdup(text); } } sqlite3_finalize(stmt); return ptr; }
#include <iostream> #include <sqlite_modern_cpp.h> #include <unistd.h> using namespace sqlite; using namespace std; int main() { try { // open the database database db("file:app.db?node=secondary&connect=tcp://myserver.ddns.net:1234"); // wait until the database is ready while(1) { string status; db << "pragma sync_status" >> status; cout << "status : " << status << endl; if (status.find("\"db_is_ready\": true") != string::npos) break; sleep(1); } // now the application can access the database // check examples here: // https://github.com/SqliteModernCpp/sqlite_modern_cpp ... } catch (exception& e) { cout << e.what() << endl; } }
import litesync import json import time conn = litesync.connect('file:app.db?node=secondary&connect=tcp://server:port') # check if the db is ready while not conn.is_ready(): time.sleep(0.250) # now we can use the db connection start_access(conn)
const uri = 'file:test.db?node=secondary&connect=tcp://127.0.0.1:1234'; const options = { verbose: console.log }; const db = require('better-sqlite3-litesync')(uri, options); db.on('ready', function() { // the database is ready to be accessed db.exec('CREATE TABLE IF NOT EXISTS users (name, email)'); ... });
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; import org.json.*; public class Sample { public static void main(String[] args) { String uri = "file:app.db?node=secondary&connect=tcp://server:port"; Connection connection = DriverManager.getConnection("jdbc:sqlite:" + uri); Statement statement = connection.createStatement(); // check if the db is ready while (true) { ResultSet rs = statement.executeQuery("PRAGMA sync_status"); rs.next(); JSONObject obj = new JSONObject(rs.getString(1)); if (obj.getBoolean("db_is_ready")) break; Thread.sleep(250); } // now we can access the db start_access(connection); } }
using SQLite; public class Program { public static void Main() { // open the database var uri = "file:app.db?node=secondary&connect=tcp://server:port"; var db = new SQLiteConnection(uri); // wait until the db is ready while (!db.IsReady()) { System.Threading.Thread.Sleep(250); } // now we can use the database db.CreateTable<TodoItem>(CreateFlags.AutoIncPK); ... } }
Imports SQLite Public Class Program Public Shared Sub Main() ' open the database Dim db As New SQLiteConnection("file:app.db?node=secondary&connect=tcp://server:port") ' wait until the db is ready While Not db.IsReady() System.Threading.Thread.Sleep(250) End While ' now we can use the database db.CreateTable(Of TodoItem)(CreateFlags.AutoIncPK) ' ... End Sub End Class
Option Explicit Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long) Public Sub Main() Dim Conn As New ADODB.Connection Dim Rst As ADODB.Recordset Dim URI As String URI = "file:C:\app\mydb.db?node=secondary&connect=tcp://server:port" Conn.Open "DRIVER=SQLite3 ODBC Driver;Database=" & URI ' Check if the database is ready Do Set Rst = New ADODB.Recordset Rst.Open "PRAGMA sync_status", Conn, , , adCmdText If InStr(Rst!sync_status, """db_is_ready"": true") > 0 Then Exit Do Sleep 200 Loop ' Now we can access the db StartDbAccess(Conn) End Sub
<?php // with sqlite3: $db = new SQLite3("file:app.db?node=secondary&connect=tcp://server:port"); // with pdo_sqlite: $pdo = new PDO("sqlite:file:app.db?node=secondary&connect=tcp://server:port"); // check if the db is ready while(1) { $results = $db->query('PRAGMA sync_status'); $row = $results->fetchArray(); $status = json_decode($row[0], true); if ($status['db_is_ready'] == true) break; sleep(0.25); } // now we can access the db start_access($db); ?>
use DBI; use JSON qw( decode_json ); my $dbh = DBI->connect("dbi:SQLite:uri=file:app.db?node=secondary&connect=tcp://server:port"); // check if the db is ready - it should not be used with apache while (1) { my ($result) = $dbh->selectrow_array("PRAGMA sync_status"); my $status = decode_json($result); if ($status->{'db_is_ready'}) last; sleep; } // now we can access the db ...
require 'sqlite3' require 'json' db = SQLite3::Database.new "file:app.db?node=secondary&connect=tcp://server:port" # check if the db is ready loop do result = db.get_first_value "PRAGMA sync_status" status = JSON.parse(result) break if status["db_is_ready"] == true sleep 0.25 end # now we can access the db start_access(db)
local sqlite3 = require "lsqlite3" local json = require "json" local db = sqlite3.open('file:app.db?node=secondary&connect=tcp://server:port') -- check if the db is ready local lsocket = require("lsocket") while true do local result = db:rows("PRAGMA sync_status") local status = json:decode(result[0]) if status["db_is_ready"] == true then break end lsocket.select(250) end -- now we can access the db start_access(db)
package main import ( "database/sql" _ "github.com/litesync/go-sqlite3" "time" ) func main() { db, err := sql.Open("sqlite3", "file:app.db?node=secondary&connect=tcp://server:port") // wait until the db is ready for !db.IsReady() { time.Sleep(1000 * time.Millisecond) } // now we can access the db start_access(db) }
SECURITY
LiteSync uses the "shared secret" method to control which nodes can be part of the network, via encryption with a secret key
It is possible (and recommended) to enable encryption on the database and on the communication between the nodes
Check the instructions about Encryption
CURRENT LIMITATIONS
1 Non-deterministic functions (which return different values each time they are called) are blocked, like random() and date('now'). Use explicit values generated on your app
2 AUTOINCREMENT keyword is not supported - but you don't need it! (check video for details)
3 Only a single application can access the database at the same time. Each instance must use its own database, and then they will be replicated and synchronized using LiteSync