Offline use with Sync to Server

[nevf]

I just found Nano-SQL, congratulations on your work, it looks interesting. Oh and good Docs.

I am especially interested in a Database which works both in the Browser and on the Server with synchronization between them. The Browser app needs to be able to work offline and sync to the server when back online. ie. Eventual consistency.

PouchDB/CouchDB does this, however I don't want to use these.

This is for my Web Knowledge Base app Clibu which currently uses MongoDB on the server and Dexie.js, in a limited way in the Browser. It doesn't have the sync/offline capability I am referring to.

Clibu can also be used On Premise, which requires the user to install MongoDB. An embedded DB would be nice in this scenario.

[only-cliches]

Thanks, glad you're getting some use out of it!

I think this is a perfect use case for the plugin system.

Reading some of the documentation on PouchDB it's clear to me that CouchDB/PouchDB were specifically designed to handle the problem of syncing between databases. If we chose a subset of those features it should be doable.

Mostly the conflict resolution stuff gets really complicated. Wed'd need to just go with last write wins for conflicts to keep thing simple.

I'm thinking the plugin config/setup might look something like this:

// client side setup
nSQL().use(new nanoSQLSyncPlugin({
    serverURL: "https://mywebsite.com",
    // which tables to sync
    tables: ["users", "posts"],
    // how often to poll the server to check for connection
    pollEvery: 5000,
    // get data needed to authenticate this user (optional)
    getAuthData: (onComplete) => {
        // whatever you give onComplete() will get passed as authData to server
        // called before every request to server
    },
    // connected to remote server
    onSyncConnected: () => {

    },
    // no longer connected to remote server
    onSyncDisconnected: () => {

    },
    // syncing with remote started
    onSyncStart: () => {

    },
    // remote and local are in sync
    onSyncEnd: () => {

    }
}));
// stop syncing/polling at any time
nSQL().extend("disconnectSync");
// start syncing/polling again
nSQL().extend("connectSync");

// server side setup
nSQL().use(new nanoSQLSyncPluginServer({
    expressApp: app,
     // which tables to sync
    tables: ["users", "posts"],
    authRequest: (updateOrDelete, table, primaryKey, rowData, authData, onComplete) => {
        // pass true to onComplete to allow the update/read
        // pass false to onComplete prevent the request.
    }
}));

Let me know what you think!

[nevf]

@ClickSimply Scott, thanks for the prompt reply. Unfortunately implementing offline db use and eventual consistency is a fairly complex beast. Multiple users can have updated the server while you are offline, so the server needs to keep track of this. The clients need a way of finding out what has changed since they were last online and if there updates are newer, push them to the server. Deletes also need to be handled. Couch uses sequence numbers for part of this.

Some articles etc. which will help here are: http://docs.couchdb.org/en/2.1.1/replication/protocol.html https://github.com/couchbase/couchbase-lite-ios/wiki/Replication-Algorithm (maybe out of date) http://offlinefirst.org/sync/ https://www.npmjs.com/package/dexie-syncable (possibly what I'll end up using) https://github.com/share/sharedb https://github.com/paldepind/synceddb https://kinto.readthedocs.io/en/stable/ https://github.com/forbesmyester/SyncIt

Browser/Server communication should be abstracted so either http or websockets can be used. In Clibu I use Websockets.

FYI I am not actually using Nano-SQL, just an interested observer.

[only-cliches]

Thanks for the info! This might be something I tackle in the future but there's a few other things I'd like to see in place before this.

[nevf]

@ClickSimply No problem and understood. Keep up the good work.

PS. It would be nice to see a NoSQL query api like MongoDB vs. SQL style. ;-)

[ghost]

There is a good CRDT imlemenation that allows 100% synchronisation. https://github.com/y-js/yjs Its used in production and very easy to use.

I really think you should look at this, because its a leap frog technology. The way CouchDB and others work is to use the Last Write Wins which does not guarantee that all changes on a type ( or datbase row as it were) that are from many offline users does resolve without anyones data being overwritten.

The interesting thing about y.js is that all the reconciliation happens clientside. This measn that server side you can either hold the "last know version of a type" or hold all versions known to be out there. You can do either depending on the use case.

Anyway CRDT is pretty hard stuff and y.js is quite an achievement.. Have a look and see what you think.

BTW Nano-SQL looks awesome. Thank you for putting this out in open source !

[atifsyedali]

There is also:

1. https://github.com/automerge/automerge
2. https://github.com/mafintosh/hypercore

[ghost]

@atifsyedali Yes automerge is very good too i agree. Its CRDT based. Very good people behind it. The hard part with CRDT is the garbage collection. Its deletes stay with the document for example.

[nevf]

@gedw99 ys-js is interesting and works well, however I have several concerns. The changes stored in IndexedDb etc. appear to grow forever. I posted on Gitter back on Mar 13 and have not had a response. This level of support which just seems to be a single developer is another concern.

@atifsyedali Automerge is impressive but really only suitable for in memory objects. So for example if you want to merge database doc's for offline use it isn't suitable. Also I think it's memory use keeps growing with every change. ie. No garbage collection as @gedw99 mentioned.

hypercore which is part of DAT seems only suitable for synchronizing files, not JS Objects or Database documents.

[plentylife]

There's also an actor based model of sync and state. I can't really write much more about it right now (gotta run) but hopefully I'll remember to later.

Here's a project that implements this system http://ceptr.org/projects/holochain#local-source-chain

[ghost]

@nevf Regarding y.js. Its a shame you did not get a response. I used it and when i hit issues i emailed the developer directly and got feedback in a day or two normally.

Its only 2 devs, but its actually a whole team and its used for lots of 3D systems by the team at a big university in Germany - cant remember the one. Its been going for quite a long time. Its true thats its risky to use something that does not have a huge company behind it. But, also its been chugging along for many years and has so far had very consistent pace to it.

Also there are really hardly any CRDT offline / online systems out there. I guess its because the maths and concepts are hard.

The team behind autoMarge is the Cambridge team. The main guy is famous for his "Turning databases inside out" video. https://www.confluent.io/blog/turning-the-database-inside-out-with-apache-samza/ Martin Kleppman. He was the lead architect behind linked in and kind of pioneered Samza and also CQRS approaches.

[only-cliches]

So far I like Automerge the best. It seems to be designed well to handle integrating into other projects and performs conflict resolution automatically in every state that it can. The well documented feature of recording conflicts is also really nice, it'll let us create a secondary table that holds the conflicts so they can be managed manually at a later date.

[ghost]

@ClickSimply Sounds good, and i think they will support it ongoing too.

Makes sense to still choose it even though it has no Durable data storage. Easy to use your own.

[sebastianmacias]

Remote sync is the missing piece of the puzzle.

I have been looking for and evaluating multiple offline-first database alternatives that support web browsers for a while and I just found out about about Nano-SQL a few hours ago. I went over the documentation and I like Nano-SQL a lot so far.

If remote sync is implemented I think it could make Nano-SQL an excellent choice for progressive web apps (PWAs).

These are some of the alternatives I have evaluated and in my opinion their pros and cons: Blockers have been bolded

Gun.js

Pros:

• Very good performance
• First class offline support, does a great job at syncing data with remote sources
• Local and remote data can be encrypted

Cons:

• Limited query engine
• No support for relationships, join queries nor aggregate queries

Dexie.js

Pros:

• Great performance
• Flexible query engine
• Can sync with remote sources although only a remote sync/persistency adapter is available and it uses NeDB for persistency which is not actively maintained

Cons:

• Limited support for relationships and no support of join nor agregate queries

Lovefield

Pros:

• Amazing performance
• Excellent query engine, supports relationships, join and aggregate queries
• Can be used with local sources (IndexedDB) or remote (Firebase) but no other remote adapters are available

Cons:

• No remote sync, can use either a local or a remote adapter
• Even though it's used at Gmail I was recently told by its main maintainer that the project is in maintenance mode and that it is very unlikely that Google will be adding new features (maybe so it doesn't compete with Firebase?)

I haven't tested Nano-SQL yet but so far based on what I read from the docs my impressions are:

Pros:

• Can achieve good performance
• Great query engine with support for relationships, join queries and offers ways to handle aggregate queries and functions
• Allows listening to data changes events
• Multiple adapters for browser and server-side persistence

Cons:

• No remote sync but looks like there is interest and could be implemented

[ghost]

automerge seems to be the one that was discussed previously..

https://github.com/automerge/automerge

On Wed, 11 Apr 2018 at 08:27 Sebastian Macias notifications@github.com wrote:

Remote sync is the missing piece of the puzzle.

I have been looking for and evaluating multiple offline-first database alternatives that support web browsers for a while and I just found out about about Nano-SQL a few hours ago. I went over the documentation and I like Nano-SQL a lot so far.

If remote sync is implemented I think it could make Nano-SQL an excellent choice for progressive web apps (PWAs).

These are some of the alternatives I have evaluated and in my opinion their pros and cons: Blockers have been bolded

Gun.js https://github.com/amark/gun

Pros:

• Very good performance
• First class offline support, does a great job at syncing data with remote sources
• Local and remote data can be encrypted

Cons:

• Limited query engine
• No support for relationships, join queries nor aggregate queries

Dexie.js https://github.com/dfahlander/Dexie.js

Pros:

• Great performance
• Flexible query engine
• Can sync with remote sources http://dexie.org/docs/Syncable/Dexie.Syncable.js although only a remote https://github.com/nponiros/sync_server sync/persistency adapter is available and it uses NeDB https://github.com/louischatriot/nedb for persistency which is not actively maintained

Cons:

• Limited support for relationships https://github.com/ignasbernotas/dexie-relationships and no support of join nor agregate queries

Lovefield https://github.com/google/lovefield

Pros:

• Amazing performance https://google.github.io/lovefield/dashboard/src/dashboard.html
• Excellent query engine, supports relationships, join and aggregate queries
• Can be used with local sources (IndexedDB) or remote (Firebase) but no other remote adapters are available

Cons:

• No remote sync, can use either a local or a remote adapter
• Even though it's used at Gmail I was recently told by its main maintainer that the project is in maintenance mode and that it is very unlikely that Google will be adding new features (maybe so it doesn't compete with Firebase?)

I haven't tested Nano-SQL yet but so far based on what I read from the docs my impressions are:

Pros:

• Can achieve good performance
• Great query engine with support for relationships, join queries and offers ways to handle aggregate queries and functions
• Allows listening to data changes events
• Multiple adapters for browser and server-side persistence

Cons:

• No remote sync but looks like there is interest and could be implemented

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/ClickSimply/Nano-SQL/issues/18#issuecomment-380341729, or mute the thread https://github.com/notifications/unsubscribe-auth/ATuCwp2H8tu6pdSG4DA8zE0b4ics1Ljyks5tnaJZgaJpZM4RqaN- .

[nevf]

@sebastianmacias Thanks for the list and pros/cons. Re. Dexie.Syncable the Sync Server just uses Nedb as a quick way to get a sample running. You would replace that with MongoDB or whatever backend DB you wanted so you con isn't relevant here.

@ClickSimply Automerge is indeed impressive however last I looked (and asked) it only works with in-memory objects and isn't durable as @gedw99 mentioned.

Like y-js I'm also concerned about lack of garbage collection with automerge which could end up consuming a lot of memory if the objects are a reasonable size and change frequently. For example a 5KB Markdown document which was edited 20 times (in a day) would use 100KB + metadata.

[nevf]

Logux is another library I've been evaluating and not had time to mention before (been travelling) but shows promise. What I like about Logux, is it is completely independent of the front/backend database. Which Dexie.Syncable is largely.

Unfortunately development is a bit slow as Andrey has other commitments. I have reported a bug to Andrey who has been very, very helpful but can't progress further until that is addressed. Also the docs are confusing right now.

Video talk Logux Core Server Client Google Logux for more info.

[only-cliches]

@ClickSimply Automerge is indeed impressive however last I looked (and asked) it only works with in-memory objects and isn't durable as @gedw99 mentioned.

This is actually preferable to the other libraries for exactly this reason. This will make Automerge very easy to integrate into nanoSQL. Rows can be loaded into Automerge when they need to be worked on, then pulled out when they need to be saved. NanoSQL needs to have control over every row's complete lifecycle or things will get crazy real fast.

Like y-js I'm also concerned about lack of garbage collection with automerge which could end up consuming a lot of memory if the objects are a reasonable size and change frequently. For example a 5KB Markdown document which was edited 20 times (in a day) would use 100KB + metadata.

Automerge (from what the docs say) is designed specifically for merging different documents into one without destroying data, conflicts should come up infrequently and can be placed into a separate table so they can be taken care of manually. For your markdown example the 5KB document shouldn't need to store full copies for each edit, only if there is a conflict Automerge can't resolve itself.

Since the conflicts are stored in nanoSQL there will only be a memory cost if you're not using persistence into IndexedDB/WebSQL.

Again lack of garbage collection is preferable in this case if you ask me. Multiple documents are merged into single CRDTs when possible (making garbage collection moot) and the conflicts are sent to a separate callback which should be handled manually, you don't want data to be accidentally destroyed.

Just my $0.02.

[nevf]

@ClickSimply I look forward to seeing your progress with automerge.

After playing around with automerge for a bit today and reading the docs etc. it does keep a complete history of all changes, forever. Read [this related issue] (https://github.com/automerge/automerge/issues/51) . You can create a new document which excludes the history however I can't see a way of removing a document from automerge. Also as per the issue I posted @pvh makes the point that the history should never be deleted.

So unless I am missing something (which is quite possible) memory usage will grow and grow as per my earlier comment.

[only-cliches]

Hey gents, I'm getting close to implementing this as a core feature.

Looking for feedback before I put this in stone.

Here's what I'm thinking:

1. Implement a conflict resolution feature nearly identical to CouchDB that works on the client and the server. I think it would also be interesting to have a callback for conflict resolution that passes in the table, query, and conflicting rows. You can return the resolved row in the callback or false for it to fallback to versioning like CouchDB.
2. Use websockets with ajax polling fallback to allow syncing between client side databases and servers alike. This means you could not only use the offline with sync to server but you could setup multiple servers all syncing between themselves for redundancy. (I've been trying to setup a no-single-point-of-failure architecture for my projects and this is a good place to do it in my opinion)
3. Include a simple JSON Web Tokens feature in the client/server model with security baked in. I think a big thing many existing offline sync databases are missing is any kind of security/authentication. I've been using JSON web tokens in my projects with good success and it makes sense to drop them in here. Keep in mind there won't necessarily be a password/login feature here. It would just be easy for you to set properties on the JSON web tokens and check their state on the client and server to validate requests. But it would be SUPER easy to build an account system on top of this.
4. Make three way data binding super simple using the new observer feature, we could do something like this:

   nSQL().observer(() => {
   return nSQL("table").query("select").emit();
   })
   .bindToServer() // three way data binding ftw!
   .subscribe((rows) => {});

I don't think we actually include any conflict resolution code, I really like CouchDBs approach here where if a record is conflicting at all everything gets saved as revisions of that row and a winner is selected in a deterministic way. This lets the application developers handle conflict resolution in a way that suits their use case, prevents the build from bloating into hundreds of kilobytes and prevents loss of data.

[sebastianmacias]

@ClickSimply do you have a rough timeline for this? The way you are describing it is just what I have been looking for. I can help with testing if needed.

[only-cliches]

Hello Sebastian, work projects are dominating my time right now, I'll likely be able to have a beta in place around the beginning of next month.

I'll drop a comment on here when I've got something folks can start playing with. 😃

[sebastianmacias]

Excellent news, I'm particularly excited about this topic. I noticed the "Comparison With Other Projects" you added to the README. I believe nanoSQL truly has a lot of potential. I'm actually about to start using it in a project I have been working on.

Thanks and congratulations on your work so far.

[509dave16]

@ClickSimply Skimming over this issue I couldn't help but think of a way that wouldn't include conflict resolution, but could potentially solve the goal of having data synced between a client and a server as well as some authorization/authentication.

Gun.js Storage Adapter. The idea is that we would be storing data in Gun.js, which would then trigger the normal sync updates that it performs. This should also cover the case where we are offline because Gun works while offline and then attempts to sync when back online. The implementation would be fairly close to how LevelDBs adapter is done. The biggest unknown for me right now is how Gun.js would affect observable queries. And deleting data would have a little quirk: the requirement of a deleted_at field for a Model. And then all the operations of the adapter would take note of that field when performing operations. I only see as this being the case because you can never really delete nodes. Only set them to NULL or some other value that represents the record being deleted.

This is not fully thought out. But it's something that I want to explore and maybe implement if I find it feasible. Wanted to to toss this your way to get your thoughts on it.

[only-cliches]

Hey Dave, really good suggestions. I think having a Gun.js adapter would be a good solution for many uses, we might add that as an adapter along with Amazon DynamoDB in the near future.

One of the "big deal" features for me that I haven't really seen in CouchDB, Gun and many others is a flexible security model, they seem to almost exclusively be all or nothing. Let me give you a very simple example: a system with blog posts like Wordpress.

Everyone should have read capability, but only specific users should have write capability and you may even want to get more specific than that (some users can only change specific column values). So with offline syncing, any public user can grab a copy of the recent blog posts; then if they come back 6 months later with conflicts in their copy the conflicts simply get overwritten with the newest posts. But if an administrator comes back 6 months later with different blog posts those conflicts now have to be merged with the rest of the system. Or let's say you wanted some rows accessible only to specific users based on a permission level stored in the database.

These are the kinds of problems I'd like to solve with nanoSQL's offline/syncing system, and honestly where I've seen many of the existing solutions fall short.

[509dave16]

@ClickSimply Regarding Conflicts This is definitely an issue that Gun does not resolve. CouchDB does this though. Using a revision history for a document. In projects I have worked on revisions are kept for historical purposes. And could be used for manually merging or choosing revisions. So in any storage engine that Nano-SQL uses a revisions table for all changes(or one for each table's changes) could be managed to provide a history. However conflict determination would need to take place so that we can let the User know if a conflict occurred and let them take action if they are allowed to.

[sebastianmacias]

This is another isomorphic library that deals data sync, conflict resolution and offline-first features to keep in mind orbitjs.com. I just found out about it.

[nevf]

FYI Notes I've made re. Orbit.js I can't see any way to specify/use database indexes. I've written a Gitter post on this. 21 Feb 18

It looks like all data is kept in memory which can be backed up to various stores, such as IndexedDB. However I can't see that it is possible to lose the in-memory store/cache and just use IndexedDB?

I can't find any documentation on how synchronization works. Latest wins, CRDT ...?  Docs say it can be used to sync editor content?

[nevf]

@509dave16 I have real trouble thinking of GunDB as a "real" database. To me it is more of a distributed in-memory cache. It has no query language, no indexes and the entire "DB" is in memory. Further it is unreliable - see this long outstanding issue

[nevf]

@ClickSimply re. your May 26 post. Am I not mistaken that you haven't included keeping a log of all db actions in your 4 points. A log is needed when you are offline and is used to synchronize the client and server when back online.

[rafamel]

Hi, @ClickSimply, just wondering if we'll get to see this in the near future. Currently going with rxdb/coach as I can't find a good solution for offline-first client-server sync, but would be really stoked to see it happen and give it a test drive as soon as it's available!

[folkvir]

Hi! @ClickSimply, @nevf , @509dave16 , I just implemented (for a demo) an extended LevelDB adapter with a Database which is totally replicated, distributed and decentralized and offline (this is NOT GunJS) across browsers connected on a same room:

• There is absolutely no server for transferring messages, there are just P2P/WebRTC connections. (with of course a signaling server just acting for the very first connection)
• Information in the database converge to a same state using a Causal Criteria (see https://www.elsevier.com/books/distributed-systems/perrin/978-1-78548-226-7 from Matthieu Perrin). For example, GunJs is only eventually consistent.
• Inserts, Updates and deletes are hundled.
• Any modification on B1 can be retreived on B2. But using NanoSQL, B2 can't listen on modification cause you are assuming that modifications are made by your module. If your database/adapter is distributed and replicated by default you cannot listen for changes on B2 when B1 insert new data and transmit new data. For example B2 cant use this: nSQL("*").on("change", function(dbEvent) { ... }); as the adapter has no way to get this information for the moment
• SQL Updates work, but they are broken because an update operation is a set of writes (1 or more) and NanoSQL only handle single write in the db. (We need in this case a rangeWrite/Update method in the adapter)

For solving problems I think the simplest solution is to listen for changes when the database is modified. For example on LevelDB you can listen for 'pu't or 'del' operation when one of them occur. And the solution for NanoSQL is to just provide at least insert, update and delete listeners and functions with as parameters the operation which will be managed inside NanoSQL. With this we are able to transmit correctly all information using appropriate distributed algorithms (see the book) Another important thing, if you want to deal with SQL Updates, you also have to provide a rangeWrite or Update method in the adapter which have all operations to make in the database for this update, otherwise you are breaking the SQL Update... And this is the case for the moment if the database is distributed or EVEN IF YOU WANT TO SYNC DATABASES UPON NSQL...

In my opinion you have to completely separate the Data Management System (aka the storage backend, the consistency criteria and the communication module) from the application (for example with NanoSQL: the SQL wrapper and for GunJs: the graph wrapper). In this way you can change the criteria used in your system for different kind of consistency (eg: strong consistency, eventual consistency, cache consistency, update consistency, causal consistency, weak causal consistency, etc...) For example on GunJs the criteria is included directly on the system. And (if my understanding of their work is good) you cant change or choose the value inserted in the database when a concurrent operation occurs. This may cause problem when you have 2 concurrent operations on the same key with one operation more important than the other. But this is not only the problem of GunJs, if you want to track concurrent operations you have to rely on an history with operations ordered by a total order. And this has a cost in space and memory and in the number of messages sent over the network (...) and you have to choose if you want or not this history when you create your application. All of this has to be done on the Data Management System side, and in the case of NanoSQL it is the role of the storage adapter. So guys, if you want to sync your databases with other existing one, please be carefull, some problem can be hidden and can be hard to solve and especially in distributed systems; that's why there is a lot of researchers on this subject :smiley:

[nevf]

@folkvir

In my opinion you have to completely separate the Data Management System (aka the storage backend, the consistency criteria and the communication module) from the application ...

I agree 100%, then you can mix and match front-end and back-end database, data communication methods (http, websocket, webrtc) etc. arc.

Have you considered turning your work into a real project here on Github, I'm sure it would be of interest to people (like me) ;-)

Another new entrant is TurtleDB, which has very good docs and this article however as it is unusable for my specific use case. It is also an all-in-one library vs. separate independent modules as per your comment.

Sorry for the late reply, but I've been away travelling in Japan.

[only-cliches]

I'm about halfway done rewriting the core and query engine, some cool new stuff that's part of the rewrite:

1. Nested indexes and ORM relationships, you'll be able to index against something like myTable.posts.length.

2. Async/nested queries. You'll be able to do stuff like this:

   nSQL().query("select").from(() => {
   return nSQL("otherTable").query("select").where(["key", "=", "value"]).exec();
   }).where(["anotherKey", "=", "anotherValue"]).exec();

   Or something like this:

   nSQL().query("select").from(() => {
   return fetch("posts.json").then(d => d.json());
   }).where(["anotherKey", "=", "anotherValue"]).exec();

   Get as crazy as you'd like.

   nSQL().query("select").from(() => {
   return nSQL().query("select").from(() => {
        return fetch("posts.json").then(d => d.json());
   }).where(["anotherKey", "=", "anotherValue"]).exec()
   }).where(["anotherAnotherKey", "=", "anotherAnotherValue"]).exec()

3. Streaming Queries. As long as you don't use orderBy, groupBy or aggregate functions you'll be able to stream the results straight from the database, internally rows are discarded from memory as they are fed into the stream.

   nSQL("table").query("select").stream((newRow) => {
   // new row from the stream
   }, () => {
   // stream complete
   }, (err) => {
   // stream error
   })

.stream() will always work the same, but when you use orderBy, groupBy or an aggregate function the rows are buffered into memory, modified as needed, then streamed.

4. Nested data models. Instead of just putting obj[] as the type, you'll be able to declare nested objects in your data model.

   nSQL("table").model([
   {key: "id", type: "uuid", props: ["pk"],
   {key: "vacationSpots", type: "obj[]", model: [
       {key: "city", type: "string"}
       {key: "state", type: "string"}
   ]
   ])

5. Indexed OrderBy. When given a single primary key/indexed column to orderBy you'll get to skip doing the orderBy in javascript as long as the where condition is either empty or only queries against that column. Also indexed orderBy's can be streamed.

   // indexed OrderBy example
   nSQL("table").query("select").where(["id", "BETWEEN", [30, 50]]).orderBy({id: "desc"}).exec();

6. Nested Events

   
   nSQL("myTable.column.value").on("change", (ev) => {

});

7. Custom query languages.  It'll be very easy to build your own query builder ontop of nanoSQL.  The end result should end up looking something like this:

```ts
nSQL().query(SQLiteQuery(`SELECT * FROM myTable ORDER BY column;`)).exec();

or a mongoDB style syntax:

nSQL().query(mongoQuery((db) => {
    return db.myTable.find({key: "value"});
})).exec();

I have plans to support SQLite, MongoDB, GraphQL and ReQL languages. These aren't high priority though, probably won't get done until after the offline/syncing plugin is done.

The streaming API is used internally by the delete and upsert queries, meaning the max number of records you can modify with nanoSQL won't be limited by javascript memory anymore. Scaling to millions of records should be significantly more manageable in 2.0.

Looking to have a working prototype by the end of the year.

[ansarizafar]

@ClickSimply great update.

[sebastianmacias]

@ClickSimply excellent news, thanks for the update!

[only-cliches]

2.0 BETA is live on NPM: https://www.npmjs.com/package/@nano-sql/core

The query engine is done but lots of parts are still missing like ORM relationship updates and event triggers, only memory/local storage adapters are done.

But there's more than enough to do some cool stuff:

nSQL().query("select").from(() => {
    return fetch("https://jsonplaceholder.typicode.com/posts").then(d => d.json());
}, { as: "posts" }).where(["userId", "=", 3]).join([
    {
        type: "inner",
        with: {
            table: () => fetch("https://jsonplaceholder.typicode.com/comments").then(d => d.json()),
            as: "comments"
        },
        on: ["posts.id", "=", "comments.postId"]
    },
    {
        type: "inner",
        with: {
            table: () => fetch("https://jsonplaceholder.typicode.com/users").then(d => d.json()),
            as: "users"
        },
        on: ["users.id", "=", "posts.userId"]
    }
])
.exec().then((rows) => {
    console.log("POSTS", rows);
})

There are tons of breaking changes for 2.0, all packages part of the 2.0 release will be part of the @nano-sql namespace while the older packages compatible with 1.x will live where they already are. This will let folks get updates to 1.x without breaking changes, then transition to 2.0 when they're ready.

I'll update the checklist README for 2.0 as I progress. The next time I update this thread will be when 2.0 is ready to use.

[sebastianmacias]

But there's more than enough to do some cool stuff:

nSQL().query("select").from(() => {
    return fetch("https://jsonplaceholder.typicode.com/posts").then(d => d.json());
}, { as: "posts" }).where(["userId", "=", 3]).join([
    {
        type: "inner",
        with: {
            table: () => fetch("https://jsonplaceholder.typicode.com/comments").then(d => d.json()),
            as: "comments"
        },
        on: ["posts.id", "=", "comments.postId"]
    },
    {
        type: "inner",
        with: {
            table: () => fetch("https://jsonplaceholder.typicode.com/users").then(d => d.json()),
            as: "users"
        },
        on: ["users.id", "=", "posts.userId"]
    }
])
.exec().then((rows) => {
    console.log("POSTS", rows);
})

↗ that is some really cool stuff

[nevf]

@ClickSimply Scott, I've read through nano-sql/core and have to say the final V2 feature list looks really great.

Can you give us some approximate update when you hope to have an actual V2 release, and in particular with the "Net Plugin (Offline Syncing)?

CHANGELOG.md hasn't been updated in a while now, which I'm sure you are aware of.

Also are the Docs at https://docs.nanosql.io/ for V1 or V2 (I assume V1)

Thanks for all your work on Nano-SQL

[only-cliches]

Awesome, yeah I'm really excited about all the improvements and what we're gonna be able to do in the near future.

The 2.0 release is likely in the next week.

I'm thinking middle of January for the net plugin at this rate, give or take a week. The changes/api is not in stone yet, so I haven't taken the time to type out all of them as a majority of the changes have either been removed or modified since I started writing 2.0. As I'm getting ready to write the documentation the API will be locked down this week and the changelog will come with it.

https://docs.nanosql.io will continue to house documentation for 1.X releases, I'm setting up new documentation on gitbook: https://nanosql.gitbook.io/docs/, gitbook will house the 2.X documentation.

I'm going to try to make it pretty easy for people to keep using nanoSQL 1.X and migrate to 2.X when/if they want to, especially since there are so many breaking changes. So the documentation and npm packages will likely always remain separate. For example, nano-sql npm package will never see a 2.X release, those will only happen with the @nano-sql/core npm package.

[nevf]

@ClickSimply Excellent news indeed. I noticed on the nano-sql/core page you refer to noSQL whereas I think previously you were more aligned with SQL. I for one certainly welcome a shift to noSQL.

I've been hanging out for a thorough, robust in Browser DB with offline capabilities for a long time. ;-) (Besides PouchDB).

On the V1 Docs Data Models in the Data Model Interface table props says IDX = index this row, TRIE = index this row where row should be column.

[only-cliches]

v2 has been released, offline/sync support is on the way next.

[bkniffler]

Can't wait for offline/sync to happen! Investigating replicating databases, nano-sql really has some great querying. I'd love to see server to server replication, filtering/mapping data before syncing to/from client peers and syncing with multiple servers (like table-a with server-a, table-b with server-b, or even table-a with server-a and table-a with server-b); but I know this is super hard to implement.

[bkniffler]

Btw. maybe this is interesting: https://github.com/libp2p/js-libp2p

[folkvir]

Or maybe just a simple interface to implement your own gateway with your own communication technology.

[bkniffler]

That'd be even better I guess.

[nevf]

@folkvir Did you ever complete and release the lib you wrote about above (Sep 2018)

[nevf]

@bkniffler No doubt many of us are keenly awaiting offline/sync. If you haven't already done so it would be worth reading through all the posts here as there is lots of useful information. I've spent some time with Logux etc. including writing an unpublished article on it, and really like it's approach.

As for p2p I've had a bit of a play database dApps that use IPFS and found them to be so slow that they are useless.

[folkvir]

@nevf No release as it is just a demonstration case. But it is not very hard to build it from scratch. Just take a database. A communication module with broadcast(one-to-all)/unicast(one-to-one) primitives and apply a consistency criteria on the data structure you would like to use. It totally depends on your needs.

[nevf]

@folkvir It sounds like you are in a great position to help Scott with offline support in nanoSql. I have to say I've never considered offline, eventual consistency, conflict resolution etc. amongst multiple users an easy issue to handle.

[folkvir]

@ClickSimply For explaining my ideas for clients' synchronisation with an eventual consistency criteria, and conflicts resolution. Just think about inputs and outputs for each client. An example with a distributed write operation:

• nsql has to provide several things:

  • the data to write (eg: tableX.colY.rowZ; aka the old value and the new value)
  • a counter (timestamp) (tableX.colTimestamp.rowZ, in general a Lamport clock)
  • A client id
  • The message to propagate is an object with; an id, a counter, the data to write

• send this message to all clients using any techniques you want (WebSockets, IP network, WebRTC),

• upon receive, check the counter and apply a conflict resolution algorithm, typically, if you use lamport clocks check the counter, if lower, nothing to do, if equal; compare IDs and take the lowest or the highest, if counter is higher; apply the operation.

Basic example:

// get your operation, contains information for getting data in the table, 
// perhaps a serialized version of your operation with at least the data to change, the timestamp after the write and the id of the client
message = write(Table.User, Col.Name, Row.X, newValue)
// console.log(message) => { op, id, timestamp }
// send the operation to all clients interested by the operation
sendToAll(message)
// upon receive, when you receive a message from a remote client
on('receive', message => process(message))
function process(message) {
    // get the same value as the message.op value 
    // with its counter and client id
    row = this.getRow(message.op) 
    if(!row) apply(message.op) // will apply the write on the database
    if(message.timestamp >  row.timestamp) {
          apply(message.op)
    } else if (message.timestamp === row.timestamp) {
      // now apply your conflicts resolution
      // the winner can be either message.id or row.id
      // this is your choice
      if(message.id < row.id) apply(message.op)
    }
}

In this example, I assume you have another col in each table. And it corresponds to the timestamp the creation/update of the row was done.