Databases - Your Excel sheet in the sky. [The Web Explained: Part 6]

Databases - Your Excel sheet in the sky. [The Web Explained: Part 6]

March 15, 2021 | 🕐10 Min Reading Time

💾 Databases

At its core, a database is just a place to persist data about a user, content for your site, or anything else you need to store for easy retrieval or manipulation. You'll also hear this referred to as a persistence layer.

We talked at length about various backend frameworks and covered the various features and strengths of each. One nearly universal feature is that they all come with ways to interact with a database in a more simplified fashion. These layers of database abstraction can lead to a huge boost in productivity and predictability when reading, writing, and manipulating data.

While it would be super-duper fun to cover the peculiarities of the superabundance of database systems that exist, we'd be here for a while, and ain't nobody got time for that. Let's talk about the basics of SQL (Structured Query Language), and how we can use it to work with the bajillions of rows of data we're storing in that gigantic Excel Spreadsheet in the sky.

👩‍👦‍👦 SQL Databases

A SQL (or relational) database is a type of software that allows for the storage and management of data using tables that are related to each other. A classic illustration would be to imagine you have a table for all of your students, another for all of the classes at the school, and a relationship between them, linking each class to a number of students and each student to a number of classes.

A Simple Relational Database Example

The data itself is stored in rows and columns, and each intersection of a row and column is called a field (like a "cell" in Excel). If you are familiar with Excel, you can really begin to visualize how the data in a database is conceptually stored. Each field also has a particular data type and size to limit both the type of data that is allowed to be stored (whole numbers, decimals, blobs of text, etc.) and the maximum size allowed for each field (limiting the first_name field to 20 characters, for example). The structure of your data and how it is organized is called the schema, and you must define it before working with the data in the table.

Example Of a Simple Database Structure

Some examples of more commonly used SQL / Relational databases are:

  • Oracle - Like having a Ferarri. Do you really need a Ferarri? Again, Ferarri's are expensive. Also, in this scenario, Ferarri has purchased almost all the other car manufacturers.
  • MySQL - It's a Trans-Am with t-tops. It's fast, it was pretty cool for a while, and to many, it's still relevant and continues to get the job done. Wait, Oracle owns them now too?!
  • Microsoft SQL Server - *It's a minivan that your HOA approved for use in your neighborhood. It's a perfectly logical choice, even if it's not the most exciting.*
  • PostgreSQL - My favorite. It's a decked-out Jeep. Capable in many situations and constantly getting add-ons and features to enable it to experience adventures in brand new terrain.

Each one has its strengths, weaknesses, and cost of operations that you must consider. But, in all likelihood, at this point, I'd just install PostgreSQL locally to tinker around with. Alternatively, if you really just wanna dip your toes in, you can look at a hosted PostgreSQL, which will free you from having to set up too much on your machine at this point.

Once you've decided on a database, it's probably a good idea to know how to get things in and out of it. In the mythical land of relational databases, you are King Arthur, and SQL will be your Excalibur...

🧲 Querying A SQL Database

I hope you're realizing by now that programming languages are actually pretty darn human-readable (well, most) once you get past all the curly brackets, strange rules, and sometimes vexatious inconsistencies. As you experience and build more and more, even new languages become minor translations from the ones you already know, and you will find yourself on the mat trying to put new, deeper challenges into a rear-naked choke.

SQL is a very specialized type of language that, due to its narrow focus, does lack some of the power and features that general-purpose programming languages like we've covered possess. SQL exists solely to define queries to read, write, update or delete data...that's it. You can't make an app with SQL; you make an app that uses SQL to get you the data you need.

The best way to take away the hocus pocus of any technology is to just expose yourself to it, so let's pull back the curtain and see the gears and levers that the Wizard has kept hidden from view...

Querying Data

# Get all the columns from the burgers table SELECT * FROM burgers; # Get just the few columns we need from the burgers table SELECT id, name, calories, patties FROM burgers;

Inserting Data

# Insert our new burger into the database so the # world may know what it means to truly live. INSERT INTO burgers (name, calories, patties, bacon_strips) VALUES ("THE MCBACON DELUXE", 4001, 6, 22);

Notice that we didn't add an id when we inserted this new burger. This is because pretty much every database system has the ability to auto-generate and increment a unique id field automatically when you insert a new record. This is called the primary key.

Deleting Data

# As a part of our new "caveman healthy" initiative, we're going to # remove burgers from our menu that don't meet our strict guidelines DELETE FROM burgers WHERE bacon_strips = 0;

Updating Data

# Someone told us we should rename a burger, and who are we to disagree # with the customers...our marketing department would have never thought # of this masterpiece, but some guy on Reddit totally saved the day. UPDATE burgers SET name = "Burger McBurgerFace", patties = 12 WHERE id='85df9ffe-f1d7-4ebd-a678-2cfdec7c167b';

Joining Tables

# We have a table for our burger orders and a table for all the loyal # customers that keep ordering our borderline irresponsible culinary # delights. Let's get a list of orders and rather than seeing a boring ol' # `customerId`, let's augment the results with their actual name SELECT orders.id, orders.orderDate, customers.name FROM orders INNER JOIN customers ON orders.customerId = customers.id;

There are a few types of joins, but for the sake of this learning, you should know that you can use joins to take the data from several tables and return the data from them in a combined format. This is where the relational in relational database really starts to earn its paycheck.

The process of deciding how to structure your data is an important one, as making a poor short-sided decision now can cause real headaches and frustration down the road. Properly normalizing your database will help ensure you generally have a good time interacting with your persistence layer and will give you flexibility for the future.

Now, what if I told you that you could get incredible performance and flexibility by not normalizing your data. In fact, what if I said that you could store and access ludicrous amounts of data, but you actually can't normalize it. Holy crap, right? Welcome to NoSQL...keep your helmet on cause' it's about to get real.

Hot Rod. Truly a gem of a movie.

🔖 NoSQL Databases

There are significant differences between NoSQL databases and SQL databases. They're both siblings born into the world of data. It's like SQL is the older brother with a solid track record, and NoSQL is the rebel...hell-bent on doing all the stuff his older brother said would never work:

  • With traditional relational databases (Oracle, MySQL, PostgreSQL, etc.), you must define a strict schema, whereas NoSQL databases (MongoDB, Cassandra, CouchDB, Redis, etc.) allow you to work more freely with unstructured data.

  • In document-based NoSQL databases such as MongoDB, you don't store your data in rows and tables but rather as documents. Each document can contain as many keys and values to represent your data, and most NoSQL databases store this in JSON format, so if you remember that from before, you're golden!

  • SQL databases love to boast about the atomic nature of their data availability and the strict consistency of their data. NoSQL databases, ever so rebellious, smoke cigarettes and love to deal in eventual consistency:

    1. I check my weather app and learn that it's going to rain tomorrow.
    2. I tell you that it's going to rain tomorrow.
    3. Your co-worker tells his wife that it's going to be sunny tomorrow.
    4. You tell your co-worker that it is going to rain tomorrow.
    5. Eventually, you all will agree that it's going to rain.

So, what has made NoSQL the choice of so many? Well, the explosion of NoSQL databases has come about in part because of the immense amount of data that we suddenly find ourselves generating, storing, and integrating with. Think about it; we're walking data generators.

In many cases, with traditional relational databases, the only way to scale is vertically, which means you just need to keep buying a bigger and faster machine to host the server. With NoSQL databases, you can easily scale horizontally, which is basically buying more machines. There are tradeoffs to both horizontal and vertical scaling that you must weigh with their benefits, but horizontal scaling is great for growing with a large number of users who are generating more and more data exponentially.

Scaling

As mentioned before, there are numerous types of databases considered to be NoSQL, and each brings to bear its own unique flavor that will require going beyond this article to appreciate truly. In summary, there are 4 distinct types:

🗝 Key-value Stores

A key-value store is like a relational database with only two columns: the key or attribute name (such as state) and the value (such as Alaska). Mega fast and great for storing small data like user preferences, profiles, and shopping cart data.

Examples:

  • Redis
  • Ignite

🏛 Column-Oriented Databases

Great for data analytics. Instead of storing data in rows, it stores it in columns which has advantages when it comes to things like quickly aggregating the value of a given column (adding up the total sales for the year, for example).

Examples:

  • Cassandra
  • HBase
  • Redshift

🦠 Graph Databases

Imagine a social network where we're all connected to each other, the same people, different people. That bi-directional relationship is called a graph, and graph databases store each element as a node. The connections between elements are called links, relationships, or edges. In a graph database, these connections are first-class elements of the database, giving you great power to explore the connections between elements that would otherwise require an absurd amount of data joining. These are generally pretty nerdy and never standalone for a business, often working in tandem with more traditional databases.

Examples:

  • Neo4j
  • AllegroGraph

📄 Document Databases

A document database generally stores data as JSON, making it a breeze to work with when creating applications. Developers love the flexibility, lack of translation, and ability to shape data to fit the application instead bending the application to the data. Examples:

  • MongoDB
  • CouchDB
  • Firebase Cloud Firestore

To simplify things a bit for you, when most people think NoSQL, they are usually thinking of document databases, and for that reason, we're going to take a small peek at what that looks like in a tangible form.

📄 A sample NoSql Document

As you can see, a document is just a JSON object...pretty darn simple, huh? And just like any other JSON object, it can contain a variety of data types, including more objects (see "marketing_fluff")!

{ "_id": ObjectId("59bd4cc7668dcce02aaa6fef"), // uniquely genererated value "name": "Bacon McBaconface, Jr.", "bacon_strips": 24, "patties": 4, "calories": 3500, "rating": 10, "optional_add_ons": ["pickles", "onions", "bacon", "peanut butter"], "featured": true, "marketing_fluff": { "headline": "THEY SAID IT COULDN'T BE DONE! TWENTY FOUR STRIPS OF BACON!", "subheadline": "It's February, we know your resolutions are long dead anyway.", } }

🙋‍♂️ Sample NoSQL Queries

Imagine with all our might that we have a MongoDB database filled with documents about our various burger offerings. How would we go about fetching one or many of our delectable patty parties?

// assume that we've imported the MongoDB client, connected, and given the // connection the variable name `db` // get all the burgers in the database db.burgers.find(); // get one specific burger in the database db.burgers.find({"name": "Bob The Burger"}); // get all burgers that are less than 4000 calories db.burgers.find({"calories": {$lt:4000}}); // delete that one burger that got us sued. db.burger.remove({"calories": 10550}); // create a new burger in response to the lawsuit. db.burgers.insert( { "name": "Lawyer McLawyerFace", "bacon_strips": 1, "patties": 1, "calories": 1000, "optional_add_ons": ["kale", "arugula", "lemongrass"], "featured": true, "marketing_fluff": { "headline": "Back, by popular court order!!", "subheadline": "We apologize for our flippant disregard for public health.", } } );

Hopefully you can see how easy it is to work with data in your applications when you're backed by a document store like MongoDB. It feels almost too easy to manipulate and query data, especially if you are working with a native Javascript implementation like NodeJS. A pairing like this hasn't hit the scene since McDonald's decided to use pancakes as buns with the McGriddle.

For real, though, NoSQL databases are a versatile and fascinating tool to have in your toolbox and can really make your life much easier in a number of scenarios. But, just as if you go around the house trying to hang pictures using a sledgehammer, you need to make sure you're choosing the right tool for the job, or your life will actually get much harder.

One thing's for sure, a sledgehammer can drive a nail into the wall, but the extra work patching up drywall (and your marriage) will quickly outweigh the overwhelming and decisive force that a 20-pound sledgehammer affords you when pitted against a tack nail. So if you think you want to go NoSQL - learn about all the tradeoffs and choose your tool wisely!

🧠 Learn more about NoSQL Databases:

> https://www.guru99.com/nosql-tutorial.html
> https://www.improgrammer.net/most-popular-nosql-database/
> https://www.mongodb.com/nosql-explained/nosql-vs-sql

🚰 ORM!

Writing extensive vendor-specific SQL queries can come between you and a fun night of laser-tag with the girls, so how can we help you reclaim some of that time and pew-pew some lasers into Darlene's face? With an ORM (object-relational mapper) library, that's how!

An ORM is a library that you can utilize to interact with our database using your language of choice instead of SQL.

With an ORM, you take something like this:

SELECT * FROM burgers WHERE bacon_strips = 12;

...and transform it into something like this:

const burgers = orm('burgers').where({ bacon_strips: 12 });

I bet that last bit there reminds you of how we interact with a document store like MongoDB, doesn't it? With an ORM, you get some of the developer niceties of something like MongoDB with a traditional relational database. There are a few other reasons you might want to use an ORM when creating your application:

  1. You get to write in your favorite language (ORMs are available for pretty much any language).
  2. An abstraction of the underlying database system...making it somewhat easier to change from MySQL to PostgreSQL, for example.
  3. Advanced features for pretty much free.
  4. If you're not a SQL guru, some of the queries you'd write with an ORM will be more performant than if you had hand-written the SQL.

As I said, there are ORM's for pretty much any language you can imagine and by-and-large they all function roughly the same. Here are a few to dig into to get you started!

🧠 Learn more about ORM's:

> https://blog.bitsrc.io/what-is-an-orm-and-why-you-should-use-it-b2b6f75f5e2a
> https://youtu.be/dUyjlHm1r5A

🦓 Migrations

migration

Another important concept in your journey to next-level awesomeness with databases is migrations. Just like you might use something like Git to manage the changes to the code that you and your team are writing, you can use migrations to keep record of all changes made to the structure (schema) of the database.

The big idea with migrations is that you never directly or manually modify your database schema. Instead, what you do is to write a small piece of code that defines an up and a down state.

The up method is where you would, for example, make a change to add a new column to your "burgers" table in your database. The down method is basically a giant undo button for whatever action you took in the up method.

As you run migrations, a special table in your database will be updated with the last-run migration on that database. Over time, as your database needs to change, you will continue to create new migration files. Running these migrations will update the "high water mark" in the database to ensure that the tables, columns, and other database structures are as you'd expect.

Here's a sample migration that creates a table for customers:

// 134217728-create-customers.js module.exports = { // create the 'customers' table and give it a few fields up: (queryInterface, Sequelize) => { return queryInterface.createTable('customers', { name: Sequelize.STRING, likesBacon: { type: Sequelize.BOOLEAN, defaultValue: false, allowNull: false, }, }); }, // remove the `customers` table down: (queryInterface, Sequelize) => { return queryInterface.dropTable('customers'); }, };

When you run a command to apply the migration, the up method will create a new table, ready to accept customers. But thanks to the down method, if you realize you've made a grave mistake, you can always undo the action by reverting.

Another great reason to embrace database migrations is to imagine a scenario where you need to spin up a copy of your database, either as a replica or on your local machine for development. Imagine needing to create possibly hundreds of tables, columns, and indexes by hand...it would take forever. With a migration library, you can simply run a single command and magically have your database match the latest committed schema. How awesome is that!?

Migrations make it easy to safely and predictably make database changes that can be versioned and tracked. If you find yourself making a database without utilizing migrations, especially in a production environment, you should have a good long hard in the mirror and think about where your life is headed and what your grandkids would think if they ever found out.

Many ORMs like Sequelize come with migration utilities built-in and ready to use.

🧠 Learn more about Database Migrations:

> https://sequelize.org/master/manual/migrations.html
> https://laravel.com/docs/8.x/migrations

More in this series

Binge this stuff like Tiger King!