The Road from SQL to NoSQL

My Road from Oracle, SQL Server to Google’s Cloud Firebase part 1

A year ago I made the leap into Flutter/Dart, and that included the jump to Google’s Cloud Firebase’s NoSQL database. And that’s where my story begins.

I’m getting ahead of myself, a bit about me first. I started development in 1987 with a fresh off the shelf copy of Nantucket’s Clipper, Summer of 87. Clipper was a compiler for dBase. How novel, right? A small single user file based “database” system. A program on one floppy and the OS on the other. Swap in your database floppy, and you’re on a roll.

Eventually I moved from DOS to Windows in the early 90s, from Clipper to Delphi, dBase files to Oracle and SQL Server.

Then 33 years later, I dipped my toe into the Flutter/Dart waters. I decided I needed a change, a big change. I always wanted to develop for phones, and tablets. This was Google’s own language and development environment. And frankly, after looking at thousands to upgrade my Delphi environment and zero to get into Flutter/Dart, it was a no brainer.

I had never taken online courses, but I had seen the ads for Udemy often, and the $11.99 Flutter boot camp looked too good to pass up. Indeed it was very good, a perfect jump start.

Back to the subject, SQL to NoSQL. I never considered myself to be a SQL expert. I had been writing SQL so long that it never really seemed special to me. I thought in tables, and rows. Rows from one Table that related to Rows from another are Joined in your Queries.

Rows, Tables, Join and Query are terms you use more often than you’d like to think. Rows and rows of data, in any number of tables become one set of returned data just the way you need it with a simple query, thank you very much.

The first thing I did after boot camp for Flutter was start to look for a SQL Database I could use. Write queries, get my data my way, thank you very much. I was used to using direct drivers, or third party drivers to do all my SQL work and get back data into objects you could create, read, update or delete (CRUD). I thought that would be a similar thing in Flutter. It’s not.

I know, I know, there are packages to do a lot of cool things, and yes there are a few local SQL engines you can use, but my point of moving to Flutter was to get reach, and reach meant cloud hosting, and cloud hosted data today means Google’s Firebase, Cloud Firestore, Storage and Authentication.

And that meant NoSQL!

I was just learning a new language, and now I saw learning NoSQL as daunting. Had I made a big mistake? Should I have just learned a language that had support for SQL databases? As I thought about this, I was also seeing the reach of Google’s platform. To be able to work in a database in real time on opposite sides of the country no less opposite sides of the world is a game changer in the field I am serving.

While 99% of our customers’ work will likely be local, 100% of pandemic consulting will be remote. I’ll write more about the app in another post, but basically our consulting work pre pandemic would entail sitting in the user’s office and going over their processes for a few weeks, then we would go back and make up a bunch of reports. Now we would be able to do all of it in real time with zero travel leaving the system behind with the customer. Game changer!

Rethinking for NoSQL

NoSQL, often called a document database, can take on several different forms, but in general, and with Firestore specific, this is a database that follows the following pattern:

Collection -> Documents -> Collection ->Documents…

Every Document is in a collection, and Documents have fields, and can have Collections, which have Documents… Sounds very Russian Doll like, or as one would say in SQL jargon, a cluster F*&K!

Put your data where you will need it, no matter how many times you have to put it.

Duplication is assumed. Rethinking your data is obviously where you need to start when you make the transition to NoSQL. For my application, it at first seemed like I was really decentralizing everything, and everything was going every where, what did I get myself into???

What would have been something close to a dozen tables in SQL wound up being this:

Orpheus Database Design

Consider each box to be an address, like a post office box. Each address can have many letters (Documents), and on and on.

After creating these “like branches”, “groups of related data”, you wind up with end point addresses /company/${company}/repository/${id}/businesses/’ … , and relative addresses, an address relative to it’s owner.

A main branch of our database replicated in many places

This segment of our database gets repeated. It represents a process in a process tree. Each process can have as many Documents in any of the 14 categories beneath them. Each individual one is represented as a master record in another section of the database, and replicated as needed.

I found that I needed another pattern inside the documents to make this happen. So, the following is true of all the Documents:

  1. Each Document has a unique ID.
  2. Knows the path to their Master Record
  3. Knows the version of it’s Master Record
  4. Knows where all the versions of itself can be found.

With this design, we are keeping our collections shallow and wide. Easy to retrieve and track.

Create collectors in your Documents

This next one was really sufficing the data duplication smartly. If a Document has a Collection with Documents in it, find aspects of the data that you would normally query for and put them in the Document.

Let’s say you have a Document with a Collection of Documents that represent related parts to the Document. In the top Document you can store:

  1. The count, how many of them are there.
  2. The max, or min of one of the fields
  3. totals, subtotals…

As you read the Collection, you can check the totals against the top level Document and update it as need be. For example, if you have a hundred Documents with a quantity represented in each Document, just make a filed in the top level Document called total, and update it as changes occur. Even just a count of how many can save you a read of all Documents in a Collection.

This prepositioning of information is how NoSQL works. Hosted databases are not free, though I have never had to pay while we have been testing. One caveat we had an errant function that was doing thousands of reads every time you opened the app. After a day of testing we had run out of our 50k reads for the day.

Each Document can store up to 1 meg of data. That is a ton of data!!! But, do not be lulled to put too much into you individual Documents. Every time they are read, the whole Document has to travel back and forth (if you save). You can also store arrays of data, but likewise, remember, if you are creating a network app, you have to keep your data fairly atomic or figure out a way to do good record locking. Though you may have one task working on one piece of the Document, you can have another working on another piece of it. So, don’t over load your Documents either.

Btw, the images were made in our process design tool, Orpheus. Our database is tracked, and changes versioned in our own tool.

Thank you for reading,

Jeff

--

--

--

34 years in Software, and I'm just getting started

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

How to Create a Kubernetes Custom Controller using client-go

Building Solid Demos . . . and what I learnt from it — Dataflow

Software is Still Eating Everything

What is latency? Let’s deep dive & understand possible ways to optimise it.

MYPUNKS: Deisgn your OWN punks!

Cloudlab Disk Image Tutorial

Creating macros and functions for Fibonacci numbers in Julia

Why you should already be with Crio!

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Jeff Heisler

Jeff Heisler

34 years in Software, and I'm just getting started

More from Medium

BLoC pattern: Learn in painless way

Setup Flutter — Basic System Configuration.

Polymorphism | OOP Concepts 1/4.

The Right Path To Being A Senior Flutter Developer