Strategies for persisting complex data in a database


#1

I’m looking for the best way to store complex data such as objects in my NS Angular application so that it persist after the app is closed. I’m trying to store a dynamic array of objects, these objects contain number, string, and Date data types. I’ve considered using SQLite but that would require me to loop through each object and each object’s data. CouchbaseLite seems to have a similar problem in that as far as I can tell you can’t store complex data in a file such as an array, and I’ve had a lot of problems in the past getting Firebase to work with the Angular version of my application. I need the database to be in sync with the application so I’m looking for the solution with the highest algorithmic efficiency.

Any strategies for accomplishing this?

Thanks!!


#2

Well, in one of my apps I do use SQLite as the app has a lot of relational data where I pull data from multiple tables in a single join. In this app SQLite worked great.

In another application I just store my JSON object to a json files and re-read those files back in on startup. This app originally started as a SQLite app; and worked pretty well – but since almost every record was being updated/replaced (via some api calls to return new data) and all needed to be in memory 99% of the time, I decided to go older school and just store the JSON as is and load it in at startup. This did require me to implement my own sort, search and internal indexing system, but the app is a lot faster now and doesn’t really have a larger memory foot print.

Really the solution is what makes sense for your application. SQLite will bring in easy to use joining and filtering. Internal JSON storage will just be a poor mans NoSQL, without any frills and features, but if you don’t need any of that extra overhead it works great. :wink:

Nathanael A.


#3

It sounds like serializing everything as a JSON object and storing it on the filesystem like @NathanaelA mentioned would work the best in your case.


#4

Awesome thanks for the detailed responses as always @NathanaelA!

One follow-up question however.

You mentioned that loading the JSON object on startup was faster in your app vs jointing SQL tables. In my case, the data will be constantly changing throughout the lifecycle of the application, and I would like to build the app in such a way that would allow me to add the functionality of having data being synced between multiple users in near real time. In my limited experience dealing with JSON objects it would seem to me that SQL joins and sets would be faster since only some of the data will be updated during the lifecycle by the user, but again my experience with JSON is limited.

Would you say my expectation of speed and efficiency accurate in this case?

Thanks a lot!

-Quinn


#5

Really depends on the data; the biggest thing SQLite has going for it is that you can query separate tables and join the result sets in queries. In a couple of apps this is critical behavior. However, sometimes that doesn’t matter another app I wrote has about 15 different filters on the primary data; and I do this all with JSON tables as it is fairly quick to scan through all the records in memory and create a sub-set of valid records to show. However my data in this app is already pretty flat, each of the sub-tables are just used for lookup lists not for really joining any data. So I just have to use all the sub-tables to populate the different filterable lists, not for any other display purposes.

You can use either data type for the app; it really depends on what makes sense to you for usage. On the one app I wrote a data abstraction layer; and so I was able to fairly easily switch from SQLite to JSON without impacting the rest of the system, and it sped up my start time considerably.

Sqlite is always going to be slower than a pure memory database, however you have to do more work in a memory database (JSON). So you have to look at the pro’s and con’s. SQLite the you can do transactions and roll back things if part of an update fails. Your own Memory table, not so much , you have to manage everything.

Basically look at the pros:

  1. JSON easy to update, easy to store, quick and dirty, very very fast.
  2. SQLite powerful searching, easy searching, CRUD, Transactions, data safety, very easy joins between tables.

and Cons:

  1. JSON, a update fails; you might screw up your entire table, the larger your dataset the longer it takes to save it.
  2. SQLite, slower than pure memory

#6

Just to chime in here. You CAN store complex data in Couchbase Lite. That is the whole point of NoSQL and honestly it makes a whole lot more sense to do this rather than serializing it and storing it in SQLite.

Say you have the following JavaScript object:

var myObj = {
    "firstname": "Nic",
    "lastname": "Raboy",
    "addresses": [
        {
            "city": "San Francisco",
            "state": "California"
        },
        {
            "city": "Incline Village",
            "state": "Nevada"
        }
    ],
    "social": {
        "twitter": "https://www.twitter.com/nraboy",
        "github": "https://www.github.com/nraboy"
    }
}

In the above example we have nested objects, nested arrays, and objects inside of arrays. Not the most complex thing you can have, but it is certainly more complex than a flat set of SQLite columns.

Then assuming you’ve got Couchbase setup in your NativeScript project, you could insert this data like the following:

var database = new Couchbase("test-database");
var documentId = database.createDocument(myObj);

Now because this is NoSQL and you’ve not serialized the complex object into an string, you can actually query the data based on its properties. When you serialize a JSON object, you’ve essentially got a key-value store where you cannot query on the value.

Here is the README to the NativeScript Couchbase plugin:

I would definitely recommend against using SQLite for your needs, likewise storing this data as key-value.

Best,


#7

@nraboy,

I have to disagree with you Nic; there are places to use SQLite, and there are places to use a NoSQL solution (like CouchBase, Realm, FireBase) and even some places to use something as simple as my NativeScript-LocalStorage plugin or your own JSON data store.

There are plenty of places I would NEVER consider a NoSQL solution but a full SQL solution, and vise-versa. :wink:

What you decide to use really depends on what you are doing and your data requirements. Never try to make a tool “couchbase” (in your case) fit everywhere. We have different tools, because the different needs well help you choose the tool you should use in each specific case. :wink:


#8

Of course that is absolutely correct @NathanaelA! However, @Quinn wanted to store complex data, not necessarily relational data. It is silly to serialize complex data to store it in SQLite. You can do it and it will work, but there are far better solutions out there designed to store complex data.

There may be specifics to @Quinn’s use-case that might include the need for database constraints that you’d find in SQLite, but if not, I don’t think he should use it for complex data.

I threw Couchbase out there because it is what I know in terms of NoSQL. Likewise, any NoSQL solution would be good for this job.

Best,


#9

Nic, yep, I understand what you are saying. However, even if the data is complex doesn’t mean NoSQL is a better solution. It really depends on how the data will be used is how I determine which engine I use. There is a lot of complex data that I would put into SQL before a NoSQL. Example when I have a very formal data spec and/or a lots of joins or certain data CRUD requirements between data pieces; SQL is way easier (imho) to use for this type of complex data vs a NoSQL dataset. However, on the flip side there is a lot of data that SQLite is not the right solution for, also the server updating that CouchBase can do, is a big pro for some work loads; pick the right tool for your specific data requirements. :wink:


#10

@NathanaelA I see what you’re saying that for certain situations, SQL can provide a better solution than NoSQL. I created an indexing system for my Couchbase dataset yesterday which I wouldn’t have had to do had I gone with SQL’s built-in indexing which would have been ideal. However, like @nraboy was saying, my dataset has many nested objects and scalability is a concern for my project. I was planning on using SQLite for its indexing system, but as the dataset becomes larger and more complex, the overhead of performing multiple joins at scale could possibly cause performance issues considering that I’m pulling data regularly throughout the app’s lifecycle, not just at startup. I went with Couchbase for this project primarily for efficiency at scale and for the ability to expand the database by adding boxes in parallel versus having to increase the size of the current box using a SQL database.

Thanks a lot, @NathanaelA, and @nraboy for the feedback!
It helped me choose a solution for my case!


#11

Glad we could help :slight_smile:


#12

@NathanaelA Hi, mind if i ask, in my case i’m using Redux through @ngrx/store, which one would you choose?, please note i want a solution that i can integrate in both NativeScript and Web versions, do you have any recommendation? so far i can only think about LocalStorage and it’s counterpart NativeScript plugin.


#13

Well, it really depends on the data and type of data. Smaller amounts of data; LocalStorage is great. If you have large data requirements; Firebase, CounchBase are pretty good solutions that can be shared on the web…

In some cases I might even pick another no-sql provider that supports both Web and NativeScript (there are a couple of them if you check my plugin site (See my tagline) in the processing category). If the data is highly structured, I might use my SQLite plugin on NativeScript and a different solution for the web.

Unfortunately this is can be a very subjective area; and for me it really depends on the data, and what you are doing with the data…

Nathanael A.


#14

My data is very structured, i want to transition from an Ionic 2 codebase that used SQLite in mobile, but WebSql in Web (yes, i know it’s deprecated).

The main purpose is offline access, being able to save the data, keep it there and use it on demand even offline.


#15

Well the nativescript-sqlite plugin is SQLite. So that seems like what you are used to using :wink: However, again if it is only a smaller amount of data LocalStorage is a better solution as it is way faster for small data sets than SQLite.

Nathanael A.


#16

This is great topic and discussion. For me my data is not complex (mainly customers info, and items), but the data could get huge (e.g. 20k of items). I will be doing lots of quires on items (by category, name, barcode, qty …etc) and updates, so which solution will have better performance in my case.
I’m comfortable with SQL however, working with MapReduce View I still didn’t completely get my head around it.


#17

@NathanaelA @nraboy am using Sqlite in my App, when a user signs Up all fields but the profile picture get inserted into the Db, but if i try to insert multiple inserts no errors are thrown the insert id is returned as a successful insert for each insert ! if i retrieve that data, i only get id’s and blank comma separated rows ,looks like sqlite cant handle a storm of INSERT ! now i don’t know what to use in my app for chat database and e-commerce
here is my SELECT code

 new sqlite("Db.db", function(err, db) {
            db.all("SELECT * FROM chat  ", [], function(err, row) {  
                if(err){
                    console.log('error querying Db for  msgs');
                    return;
                } else{
                    row.forEach(function(res){
                        if (v.firstname==res.chatUser) {
                            var name=res.chatTo ;
                        } else {
                            name=res.chatUser ;
                        }
                      var  ary={"msg":res.msg,"name":name,"time":res.post_time};
                        console.log("DB Results =>",JSON.stringify(ary));
                       model.Chat.push(ary);
                    })
                }
            });
        });

and my INSERT code

 new sqlite("Db.db", function(err, db) {
            db.execSQL("INSERT INTO chat (chatUser,chatTo,msg,post_time) VALUES (?,?,?,?)", [values.chatUser,values.chatTo,values.msg, values.post_time], function(err, id) {
                console.log("The new Chat id is: " + id);
                console.log("ERROR IS :",err);//just to see if i get an Error
                });
             });

           ;