Slide 1
Slide 1 text
Thomas Hilzendegen
@hilzendegent
Consultant
Manuel Rauber
@ManuelRauber
Consultant
Architektur-Patterns & Implementierung
Offline First APIs & Backends
Slide 2
Slide 2 text
Thomas Hilzendegen
Speakers
Manuel Rauber
[email protected]
@hilzendegent
[email protected]
@manuelrauber
https://manuel-rauber.com
Microsoft MVP
Slide 3
Slide 3 text
Timetable
Time Doing
09:00 - 10:30 Part I
10:30 - 11:00 ☕
11:00 - 12:30 Part II
Slide 4
Slide 4 text
• Progressive Web App
• Offline availability
• Offline synchronization
• Change tracking
• Security considerations
• Pitfalls
Agenda
Slide 5
Slide 5 text
Intro
Slide 6
Slide 6 text
Offline… but why?
Slide 7
Slide 7 text
• Just because the phone has a connection,
e.g. Edge, it does not mean we are online
• Depends on the use case which connection
quality is required to determine, if the
app is reliable online
• Connection quality could be measured
by the time an exclusive request takes
• Duration < 150 ms: online
• Duration !>=150 ms: bad connection
What does “offline” mean?
Slide 8
Slide 8 text
• A lot of reasons to be offline
• Traveling, Train, Flights (bad or no signal)
• Server is not available
• Routing problems
• Roaming
• Costs
Motivation
Slide 9
Slide 9 text
Demo Application
Slide 10
Slide 10 text
• A little application to manage boardgames
• Angular, .NET Core, MS SQL Server
• Brownfield application, started as a pure online application
• As typical for any good demo: no security
• Available on Azure: https://tt-boardist.azurewebsites.net
• GitHub: https://github.com/thinktecture/thinktecture-boardist
Demo Application “Thinktecture Boardist”
Slide 11
Slide 11 text
• Backend
• .NET Core 3.0
• SQL Server
• Frontend
• Angular 8.2.13
Demo Application Specs
Slide 12
Slide 12 text
Demo Application Database Scheme
Slide 13
Slide 13 text
Live Demo
Slide 14
Slide 14 text
Now let’s go offline!
Slide 15
Slide 15 text
Does it work in flight mode?
NOPE!
Slide 16
Slide 16 text
Progressive Web App
Slide 17
Slide 17 text
Live Demo
Slide 18
Slide 18 text
Does it work offline now?
STILL NOPE!
Slide 19
Slide 19 text
Real offline availability
Slide 20
Slide 20 text
• ServiceWorker is only able to take data offline which has been requested by
the application
• If all the URLs are known beforehand, the ServiceWorker could cache them
all
• Data which was not requested, is not available offline (no real offline
synchronisation)
• But what about … the Background Sync API?
What’s the problem?
Slide 21
Slide 21 text
• Name is misleading
• Does not offer any data synchronisation possibilities
• Just raises an event periodically or after a delay (optionally based on the
network type)
• It’s totally up to the developer what to do in the onsync event
• Since the sync is done in the ServiceWorker, the page can be closed, the
sync will be fulfilled anyway
• It does not help anything with syncing your actual data!
Background Sync API
Slide 22
Slide 22 text
Background Sync API
Slide 23
Slide 23 text
• Cookies (not meant for large data or binaries)
• Web Storage like session storage or local storage (not meant for large data
or binaries)
• IndexedDB
• Cache Storage (based on request/response)
Storage capabilities
Slide 24
Slide 24 text
User can clear anything
at any time by
“Remove temporary internet files”
or “Clear website data”
Slide 25
Slide 25 text
• Key-value database of the browser
• Stores data permanently
• ServiceWorker and Web App share access to the same IndexedDB
• Possibility of scenarios, where the ServiceWorker (or Web App) stores
synchronised data in the IndexedDB and the Web App reads the data
• Like everything based on the origin
IndexedDB
Slide 26
Slide 26 text
IndexedDB Availability
Slide 27
Slide 27 text
• The standard API of IndexedDB is inconvenient to use (lots of callback)
• Dexie.js is a minimalistic wrapper for IndexedDB
• Operations are promise-based
• Near native performance (even for bulk inserts)
• Open Source @ GitHub: https://github.com/dfahlander/Dexie.js
IndexedDB API
Slide 28
Slide 28 text
Offline Sync
Slide 29
Slide 29 text
• Offline Sync means to download all data available to client into a persistent
offline storage, without the user having to explicitly request the data
• Depending on the scenario, client can do CRUD on the offline data
• Data will be synced back to the server, whenever a connection is possible
Offline Sync Basics
Slide 30
Slide 30 text
• Online/offline recognition
• Conflict management
• Binary data
• Local changes
• Deleting data
• Update interval (incoming new data)
• Error handling
• Primary key generation
Offline Sync Challenges
Slide 31
Slide 31 text
• Having Edge may not mean that we are online
• Being online could depend on several factors
• Connection quality (Edge, 3G, 4G, 5G)
• Connection speed (latency to your backend)
• Reachability of backend systems (database, 3rd party system, storages)
Offline Sync Challenges - Online/offline recognition
Slide 32
Slide 32 text
• Client needs to be online for write operations
• Locks the data, so no other client can overwrite it
• Data stays locked, until the client either saves or discards changes
• Last One (Write) Wins
• Visual conflict management (diffing like in Git, SVN, etc.)
Offline Sync Challenges - Conflict management
Slide 33
Slide 33 text
• Downloading large binary data may not be possible on all platforms
• A browser does not have a big enough storage for storing the data
• Native apps (Cordova, Electron) could be a solution to directly access the
platform’s file system
• Uploading large binary data
• Server needs to support chunk based upload with connection interruptions
• Client needs permanent access to the binary file being uploaded, which
could be hard in a browser-only scenario
Offline Sync Challenges - Binary data
Slide 34
Slide 34 text
• User opens an edit form and makes changes to data
• Incoming sync from server would change the data the user is editing
• Depending on your use-case, decide what to do
• Inform the user about the data change?
• Show a visual diff?
• Override the local changed data?
• “Do nothing” and override the server data on next sync
Offline Sync Challenges - Local changes
Slide 35
Slide 35 text
• Mark “deleted” data with a flag only
• Use a trigger to keep the deleted ID in a separate table
• Client needs some info about “deleted” data
• Simple list of IDs “deleted” since the last sync
Offline Sync Challenges - Deleting data
Slide 36
Slide 36 text
• Decide when you need to sync which data
• Not all data needs to be synced in the same interval
• An additional real time connection (WebSocket) could be established
• The server can send a signal when some data needs to be synchronised
Offline Sync Challenges - Update interval
Slide 37
Slide 37 text
• Show it, don’t hide them!
• The user needs to know that something has occurred which shouldn’t
• If you can’t sync to the client anymore, you may need to re-sync everything
• Depending on your use case, you may want to write all data in one
transaction on the server to not have partial data written into the database
Offline Sync Challenges - Error handling
Slide 38
Slide 38 text
Primary key generation
Slide 39
Slide 39 text
• Inserted offline data needs to be available in the client immediately (e.g. the
users reloads the application)
• Behaves like “real synced data”, even if it was not written to server yet
• Client is able to route to offline inserted data
• Therefor it needs some kind of primary key/id to route to
• Primary key can be generated on server-side and on client-side
Primary key generation - challenge
Slide 40
Slide 40 text
• Server is responsible for generating the primary key
• Client will generate a temporary offline primary key
• After syncing, the client needs to replace the temporary offline key with the
server generated primary key
• Allows to use sequential GUIDs on the server side (MSSQL)
Primary key generation - server-side
Slide 41
Slide 41 text
• Client is responsible for generating the primary key (GUIDs only)
• Server inserts the client generated key into the database as the primary key
• Server additionally needs to have a clustered key
• Otherwise inserting a client non-sequential GUID will lead to a
reclustering of the database, every time a client syncs new data
Primary key generation - client-side
Slide 42
Slide 42 text
Change Tracking
Slide 43
Slide 43 text
• Possibilities to determine the client’s current state
• Use highest rowversion within result
• Determine by result set or additional query
• Result of @@DBTS
• Returns the last used rowversion of the database
• Result of MIN_ACTIVE_ROWVERSION()
• Returns the lowest (active) rowversion of the database
Change Tracking - MS SQL Server’s ROWVERSION
Slide 44
Slide 44 text
• If you do a million updates a second, every second, the timestamp will wrap
around in about 585000 years
• Could be used for concurrency checks as well (see Conflict Management)
• Before writing data into a row, you could check if it still as the client’s
saved row version
• If yes: data was not modified, saving is easily possible
• If no: data was modified, decide per use case what to do!
Change Tracking - MS SQL Server’s ROWVERSION
Slide 45
Slide 45 text
Change Tracking - Highest ROWVERSION within result
Transaction #1
ID ROWVERSION
1 0x01
2 0x02
… …
41 0x41
Transaction #2
ID ROWVERSION
1 0x01
2 0x02
… …
41 0x41
42 0x42 43 0x43
SELECT MAX(ROWVERSION) FROM TABLE
Client’s state
Last known rowversion 41
43
NOT RELIABLE!
Slide 46
Slide 46 text
• A later started transaction but committed first results in a queryable
rowversion higher than the pending ones
• Data will be lost (never seen by the client’s delta request)
Change Tracking - Highest ROWVERSION within result
Slide 47
Slide 47 text
Change Tracking - Query @@DBTS
Transaction #1
ID ROWVERSION
1 0x01
2 0x02
… …
41 0x41
Transaction #2
ID ROWVERSION
1 0x01
2 0x02
… …
41 0x41
42 0x42 43 0x43
SELECT @@DBTS
Client’s state
Last known rowversion 41
42
43
NOT RELIABLE!
Slide 48
Slide 48 text
• Value is database global
• Running transaction updates the value instantly (before commit or rollback)
• Data will be lost (never seen by the client’s delta request)
Change Tracking - Query @@DBTS
Slide 49
Slide 49 text
Change Tracking - Query MIN_ACTIVE_ROWVERSION()
Transaction #1
ID ROWVERSION
1 0x01
2 0x02
… …
41 0x41
Transaction #2
ID ROWVERSION
1 0x01
2 0x02
… …
41 0x41
42 0x42 43 0x43
SELECT MIN_ACTIVE_ROWVERSION()
Client’s state
Last known rowversion 41
42
44
RELIABLE!
Slide 50
Slide 50 text
• Value is database global
• Running transaction updates the value instantly (before commit or rollback)
• Data will be safe
• Needs to be compared with greater or equal
• May query data multiple times
Change Tracking - Query MIN_ACTIVE_ROWVERSION()
Slide 51
Slide 51 text
Backend Preparation
Slide 52
Slide 52 text
• All syncable entities need to have rowversion column
• rowversion is updated by MS SQL Server automatically whenever the row
is changed (created & updated)
• For deleted entities
• Either set a IsDeleted flag to true (never delete any rows physically)
• Or save the deleted IDs of the entities somewhere else (by trigger)
Backend Preparation - MS SQL Server
Slide 53
Slide 53 text
• Use equivalents of rowversion and triggers
• Manual implement mechanism in business logic (error-prone!)
• Update tracking column manually by incrementing a database global
number (during one transaction!)
• Will be very hard für multi-row updates/inserts
• Manual implement mechanism in triggers (if available)
Backend Preparation - Other Database Systems
Slide 54
Slide 54 text
Frontend Preparation
Slide 55
Slide 55 text
• Choose storage area for data (e.g. IndexedDB)
• Write all the code
• Periodic data synchronization
• Binary synchronization when data changes
• Tracking of timestamps
Frontend Preparation
Slide 56
Slide 56 text
Security
Slide 57
Slide 57 text
Typical token-based security
Security - API level
Browser
Identity
Provider
Web API
Slide 58
Slide 58 text
• User is only able to see data based on his security level
• Permissions
• Roles
• Policies
• What happens, if the user permission change is leading to different data
visible to the user?
• What about data, which he does not see due to rights, but is connected to
other data?
• Remove frontend data after logout
Security - Data Level
Slide 59
Slide 59 text
Pitfalls
Slide 60
Slide 60 text
• De-normalize the relational data (document style)
• Results in multiple rowversion for one entry (use most recent one)
• Multiplies the data (more traffic)
• Consistent data for one entry
• Keep the relations up to the frontend
• Explicit rowversion for each entry
• Partial consistency (related data may not be synced yet)
Relational Data
Slide 61
Slide 61 text
• Keep some relational data and de-normalize some of it
• De-normalize many-to-many relations
• Needs trigger or business logic to change main entry’s rowversion when
relation changes
• Partial consistency (related data may not be synced yet)
Relational Data
Slide 62
Slide 62 text
• PWA helps taking offline the application, but not the data
• Online != Edge is available
• Offline data may be “temporary” (“Remove temporary internet files”)
• Use MIN_ACTIVE_ROWVERSION() and “greater or equal than” operator
• Think about data level security (permissions, roles, etc.)
• Think about conflict management
• Depends heavily on your use case
Summary
Slide 63
Slide 63 text
And… now?
• Slides: https://speakerdeck.com/manuelrauber
• Repository: https://github.com/thinktecture/thinktecture-boardist