Databases are programs built to hold data in a structured, logical, well ordered and efficient way. There are many kinds of databases out there but the two most known and used are SQL and noSQL databases.

SQL or relational databases

SQL stands for Structured query language. As the name implies, its a language used for retrieving information from the database itself. It's not a programming language, but a "dialect" we use to talk with the database and get the information we want from it. This same language is used to query any SQL database.

SQL databases resemble good ol' excel tables in the following way:

• Each database is conformed by a variety of tables.
• Each table is conformed of columns (which dictate the type of data you'll store in each one of them) and rows (that represent the actual information you're storing).

This is how a typical SQL table might look like:

In this case we have 6 columns representing different data fields (id, name, email, password, about and profilePicture) and 2 rows representing the different records we have stored so far.

SQL databases have been around since the 70's and are yet the most popular and used kind of databases. This model was first conceived by IBM computer scientist Ted Codd, who first published the idea on a paper called "A relational model of data for large shared data banks".

The of idea relational comes from the way in which we can relate different tables together. Say we have two different tables in the database we're using, 'users' and 'cocktails'.

The users table:

The cocktails table:

See that in the users table we're referencing each user by a unique id. This is called primary key and it's used to reference each unique record inside a table.

Now see the cocktails table and notice we have a primary key too, but we're also using a unique id in the column publisherId. This is used to reference the user who published the cocktail recipe. This type of ids are known as "foreign keys" as they are used to reference individual records outside of the actual table.

Since we have primary keys to identify individual records on each table and foreign keys to identify individual records outside of a table, It's easy for us to query information for various tables on a single call. This type of query is called "join" and is one of the main differences between SQL and noSQL DBs (in noSQL we can't make joins).

In good part thanks to this fact, in SQL DBs the user is encouraged to store data in it's smallest possible form. Following the example, we may have a table for users, a table for cocktails, and a table for recipe ingredients; instead of having one unique table with columns for user data, cocktail data and ingredients data all together.

Another important fact about SQL DBs is that they have schemas. A schema is like a mold you define when you create a table. If when you create a table you define the columns ID, name and age, those are the only fields you'll be able to store in each record. If you later on decide you need information about gender and favorite food, you can't easily add the new columns into the table. There are ways around this, but SQL discourages this to assure data consistency (meaning all records will have the same type of data).

Examples of SQL databases include mySQL, postgreSQL, SQLserver and MariaDb.

noSQL or non relational databases

noSQL databases started getting popular in the 2010's and were born as a flexible alternative to SQL DBs.

In noSQL DBs, each database is conformed by collections, and each collection is conformed by documents. At the same time, each document is conformed by key-value pairs. Its structure resembles a lot a javascript object.

Let's see graphical example on firestore (a cloud stored noSQL db offered by Google).

In this example, I have a collection called "tasks" with several documents within it. And each document has the fields "creationDate, dueDate, taskDescription, taskPriority, taskState, taskTitle, taskType and userId"

The equivalent structured in a JS object my look like this:

const myNoSQLdb = {
    tasks: {
        BymXwZFkSBW3QsYQKX7S: {
            creationDate: '1 de julio de 2021, 20:26:23 UTC-3',
            dueDate: '26 de julio de 2021, 20:24:18 UTC-3',
            taskDescription: "This is test task number 2",
            taskPriority: 1,
            taskState: "completed",
            taskTittle: "Test task number 2",
            taskType: "studies",
            userId: "KyET1JwmoCPbHEfvkIBlcjlX6Rz2"
        }
        otherDocument: {
            ...
        },
        andOtherDocument: {
            ...
        }
    }
}

An important fact about noSQL DBs is that each field can hold any data type a JS object can store. This includes strings, numbers, arrays, dates and another objects.

Another important fact is that they are schemaless, meaning each document doesn't have to necessarily have the same information.

noSQL DBs don't support joins so instead of normalizing data into small parts, the user is encouraged to embed diverse data into single documents. This makes noSQL DBs very efficient to read (query) from the front end, but sllower to write to (store new data).

Examples of noSQL databases include mongoDb, dynamoDb, firestore and reddis.

The SQL language

We mentioned the SQL language is used to query all SQL DBs (only minor changes might be present). So let's go through some of the most common commands to get an idea of how SQL looks.

Creating a database:

CREATE DATABASE mixr;

Creating a table:

CREATE TABLE users(
    id SERIAL PRIMARY KEY,
    name VARCHAR (100),
    email TEXT,
    password TEXT,
    about TEXT,
    profilePicture TEXT
);

Inserting records into a table:

INSERT INTO users(name, password, email) VALUES
    ('joe', 'joeFakePassword1!', 'joeEmail@gmailcom'),
    ('frank', 'frankFakePassword1!', 'frankEmail@gmailcom');

Get all records from a table:

SELECT * FROM users;

When selecting fields, we can use many conditionals or modifiers such as: WHERE, GROUP BY, HAVING, ORDER BY, LIMIT and DISTINCT

SELECT * FROM "users" WHERE name='joe';

SELECT * FROM "users" LIMIT 1;

SELECT * FROM users ORDER BY id DESC;

Deleting records from a table:

DELETE FROM users WHERE name = 'frank';

Join:

SELECT * FROM users INNER JOIN cocktails ON users.id = cocktails.publisherId;

Querying noSQL dbs

noSQL DBs don't have a common query language, but most of them are fairly similar. Let's look at some examples of Mongo DB.

Show Databases:

show dbs
db // prints the current database

Switch Database:

use <database_name>

Show Collections

show collections

Create:

db.coll.insertOne({name: "Max"})
db.coll.insert([{name: "Max"}, {name:"Alex"}]) // ordered bulk insert
db.coll.insert([{name: "Max"}, {name:"Alex"}], {ordered: false}) // unordered bulk insert
db.coll.insert({date: ISODate()})
db.coll.insert({name: "Max"}, {"writeConcern": {"w": "majority", "wtimeout": 5000}})

Read:

db.coll.findOne() // returns a single document
db.coll.find()    // returns a cursor - show 20 results - "it" to display more
db.coll.find().pretty()
db.coll.find({name: "Max", age: 32}) // implicit logical "AND".
db.coll.find({date: ISODate("2020-09-25T13:57:17.180Z")})
db.coll.find({name: "Max", age: 32}).explain("executionStats") // or "queryPlanner" or "allPlansExecution"
db.coll.distinct("name")
// Count
db.coll.count({age: 32})          // estimation based on collection metadata
db.coll.estimatedDocumentCount()  // estimation based on collection metadata
db.coll.countDocuments({age: 32}) // alias for an aggregation pipeline - accurate count
// Comparison
db.coll.find({"year": {$gt: 1970}})
db.coll.find({"year": {$gte: 1970}})
db.coll.find({"year": {$lt: 1970}})
db.coll.find({"year": {$lte: 1970}})
db.coll.find({"year": {$ne: 1970}})
db.coll.find({"year": {$in: [1958, 1959]}})
db.coll.find({"year": {$nin: [1958, 1959]}})
// Logical
db.coll.find({name:{$not: {$eq: "Max"}}})
db.coll.find({$or: [{"year" : 1958}, {"year" : 1959}]})
db.coll.find({$nor: [{price: 1.99}, {sale: true}]})
db.coll.find({
  $and: [
    {$or: [{qty: {$lt :10}}, {qty :{$gt: 50}}]},
    {$or: [{sale: true}, {price: {$lt: 5 }}]}
  ]
})
// Element
db.coll.find({name: {$exists: true}})
db.coll.find({"zipCode": {$type: 2 }})
db.coll.find({"zipCode": {$type: "string"}})
// Aggregation Pipeline
db.coll.aggregate([
  {$match: {status: "A"}},
  {$group: {_id: "$cust_id", total: {$sum: "$amount"}}},
  {$sort: {total: -1}}
])
// Text search with a "text" index
db.coll.find({$text: {$search: "cake"}}, {score: {$meta: "textScore"}}).sort({score: {$meta: "textScore"}})
// Regex
db.coll.find({name: /^Max/})   // regex: starts by letter "M"
db.coll.find({name: /^Max$/i}) // regex case insensitive
// Array
db.coll.find({tags: {$all: ["Realm", "Charts"]}})
db.coll.find({field: {$size: 2}}) // impossible to index - prefer storing the size of the array & update it
db.coll.find({results: {$elemMatch: {product: "xyz", score: {$gte: 8}}}})
// Projections
db.coll.find({"x": 1}, {"actors": 1})               // actors + _id
db.coll.find({"x": 1}, {"actors": 1, "_id": 0})     // actors
db.coll.find({"x": 1}, {"actors": 0, "summary": 0}) // all but "actors" and "summary"
// Sort, skip, limit
db.coll.find({}).sort({"year": 1, "rating": -1}).skip(10).limit(3)
// Read Concern
db.coll.find().readConcern("majority")

Update:

db.coll.update({"_id": 1}, {"year": 2016}) // WARNING! Replaces the entire document
db.coll.update({"_id": 1}, {$set: {"year": 2016, name: "Max"}})
db.coll.update({"_id": 1}, {$unset: {"year": 1}})
db.coll.update({"_id": 1}, {$rename: {"year": "date"} })
db.coll.update({"_id": 1}, {$inc: {"year": 5}})
db.coll.update({"_id": 1}, {$mul: {price: NumberDecimal("1.25"), qty: 2}})
db.coll.update({"_id": 1}, {$min: {"imdb": 5}})
db.coll.update({"_id": 1}, {$max: {"imdb": 8}})
db.coll.update({"_id": 1}, {$currentDate: {"lastModified": true}})
db.coll.update({"_id": 1}, {$currentDate: {"lastModified": {$type: "timestamp"}}})
// Array
db.coll.update({"_id": 1}, {$push :{"array": 1}})
db.coll.update({"_id": 1}, {$pull :{"array": 1}})
db.coll.update({"_id": 1}, {$addToSet :{"array": 2}})
db.coll.update({"_id": 1}, {$pop: {"array": 1}})  // last element
db.coll.update({"_id": 1}, {$pop: {"array": -1}}) // first element
db.coll.update({"_id": 1}, {$pullAll: {"array" :[3, 4, 5]}})
db.coll.update({"_id": 1}, {$push: {scores: {$each: [90, 92, 85]}}})
db.coll.updateOne({"_id": 1, "grades": 80}, {$set: {"grades.$": 82}})
db.coll.updateMany({}, {$inc: {"grades.$[]": 10}})
db.coll.update({}, {$set: {"grades.$[element]": 100}}, {multi: true, arrayFilters: [{"element": {$gte: 100}}]})
// Update many
db.coll.update({"year": 1999}, {$set: {"decade": "90's"}}, {"multi":true})
db.coll.updateMany({"year": 1999}, {$set: {"decade": "90's"}})
// FindOneAndUpdate
db.coll.findOneAndUpdate({"name": "Max"}, {$inc: {"points": 5}}, {returnNewDocument: true})
// Upsert
db.coll.update({"_id": 1}, {$set: {item: "apple"}, $setOnInsert: {defaultQty: 100}}, {upsert: true})
// Replace
db.coll.replaceOne({"name": "Max"}, {"firstname": "Maxime", "surname": "Beugnet"})
// Save
db.coll.save({"item": "book", "qty": 40})
// Write concern
db.coll.update({}, {$set: {"x": 1}}, {"writeConcern": {"w": "majority", "wtimeout": 5000}})

Delete:

db.coll.remove({name: "Max"})
db.coll.remove({name: "Max"}, {justOne: true})
db.coll.remove({}) // WARNING! Deletes all the docs but not the collection itself and its index definitions
db.coll.remove({name: "Max"}, {"writeConcern": {"w": "majority", "wtimeout": 5000}})
db.coll.findOneAndDelete({"name": "Max"})

Query builders

While DBs have their own query language, it's fairly common to use libraries that simplify the building of queries and add other functionalities that simplify the development process. Query builders are libraries that add a level of abstraction to the relationship with the Db.

Two popular examples are Knex (for SQL DBs) and Mongoose for (mongoDb).

Knex

Knexjs. is a SQL query builder for PostgreSQL, CockroachDB, MSSQL, MySQL, MariaDB, SQLite3, Better-SQLite3, Oracle, and Amazon Redshift. It features both traditional node style callbacks as well as a promise interface for cleaner async flow control, a stream interface, full-featured query and schema builders, transaction support (with savepoints), connection pooling and standardized responses between different query clients and dialects.

This is how common SQL queries look on knex: Create table

knex.schema.createTable('user', (table) => {
  table.increments('id')
  table.string('name')
  table.integer('age')
})
.then(() => ···)

Insert

knex('users')
  .insert({ email: 'hi@example.com' })

Select

knex('users')
  .where({ email: 'hi@example.com' })
  .then(rows => ···)

Update

knex('users')
  .where({ id: 135 })
  .update({ email: 'hi@example.com' })

Mongoose

Mongoose provides a straight-forward, schema-based solution to model your application data. It includes built-in type casting, validation, query building, business logic hooks and more, out of the box. Libraries like mongoose offer a way to insert schemas into noSQL DBs, which cover one of it biggest "weaknesses".

Creating a schema:

var mongoose = require("mongoose");
var Schema = mongoose.Schema;

var ProductSchema = new Schema({

  name: {
    type: String,
    required: true
  },
  brand: {
    type: String,
    required: false
  },
  order: {
    type: Schema.Types.ObjectId,
    ref: "Order"
  }
});

// This creates our model from the above schema, using mongoose's model method
var Product = mongoose.model("Product", ProductSchema);

// Export the Article model
module.exports = Product;

Creating documents based on the model:

// To use the model we need to import mongoose and the model. We have our model in a models folder so our code to do so looks like this

var mongoose = require("mongoose");
var db = require("./models");
mongoose.connect("mongodb://localhost:27017/grocerydb", { useNewUrlParser: true });
// Then we create a document like so

var product = { name: "Soda", brand: "demoBrand" };
db.Product.create(product)
.then(function(dbProduct) {
    console.log(dbProduct);
})
.catch(function(err) {
    console.log(err);
});

Select documents:

db.Product.findOne({ _id: <id> })

Update documents:

db.Product.updateOne({ name: 'Soda' }, { brand: 'newBrand' });
db.Product.updateMany({ brand: 'demoBrand' }, { quantity: 500 });

Delete documents:

db.Product.deleteOne({ name: 'Soda' });
db.Product.deleteMany({ quantity: { $gte: 100 } });

If my article was helpful, consider inviting me a coffee = )

Buy me a coffee

You can also follow me on Twitter and Linkedin