Databases

Overview

Databases are essential tools for organizing, storing, and managing data in a structured way. There are various types of databases, each with its own unique features and use cases. Here's an overview of some common types of databases:

Different types of Databases

1. Relational Databases (RDBMS): Relational databases use tables to store data and are based on the relational model. They use Structured Query Language (SQL) for data manipulation and retrieval. Examples include MySQL, PostgreSQL, Microsoft SQL Server, and Oracle Database.

2. NoSQL Databases: NoSQL databases are non-relational and designed to handle unstructured data, high volumes of data, and distributed data storage. They use various data models like document, key-value, column-family, and graph. Examples include MongoDB (document), Redis (key-value), Cassandra (column-family), and Neo4j (graph).

3. NewSQL Databases: NewSQL databases are designed to provide the benefits of both relational and NoSQL databases. They offer the ACID (Atomicity, Consistency, Isolation, Durability) properties of relational databases while also providing horizontal scalability like NoSQL databases. Examples include Google Spanner, CockroachDB, and VoltDB.

4. Time-series Databases (TSDB): Time-series databases are specifically designed for handling time-series data, which are data points indexed in time order. They are commonly used in IoT, finance, and monitoring applications. Examples include InfluxDB, OpenTSDB, and TimescaleDB.

5. Graph Databases: Graph databases use graph structures to represent and store data, with nodes representing entities and edges representing relationships between entities. They are optimized for querying complex relationships and are often used in social networks, recommendation engines, and fraud detection. Examples include Neo4j, OrientDB, and ArangoDB.

6. Columnar Databases: Columnar databases store data in columns rather than rows, which allows for more efficient storage and retrieval of data. They are particularly useful for analytics and big data applications. Examples include Apache Cassandra, Google Bigtable, and Apache HBase.

7. Object-oriented Databases (OODBMS): Object-oriented databases store data in the form of objects, as used in object-oriented programming languages. They offer better performance and ease of use when working with complex data structures. Examples include ObjectDB, Versant Object Database, and GemStone/S.

8. In-memory Databases (IMDB): In-memory databases store data in the system's main memory instead of on disk, which provides faster access and processing times. They are often used for real-time analytics, caching, and high-performance applications. Examples include Redis, MemSQL, and SAP HANA.

9. XML Databases: XML databases are designed to store and query data in XML format. They are used in applications that require complex data structures and hierarchical data storage. Examples include eXist-db, BaseX, and MarkLogic.

10. Spatial Databases: Spatial databases are designed to store and manage geographic and spatial data like points, lines, and polygons. They are commonly used in GIS (Geographic Information Systems), location-based services, and geospatial analytics. Examples include PostGIS (an extension for PostgreSQL), Oracle Spatial, and MySQL Spatial.

Each type of database has its strengths and weaknesses, and the choice of the appropriate database type depends on the specific requirements and use cases of the application.

ORMs

Object-Relational Mapping (ORM) is a programming technique that helps developers interact with databases using object-oriented programming (OOP) concepts. ORMs allow developers to work with databases by abstracting the underlying SQL queries and representing database tables as classes and rows as objects in the code. This abstraction simplifies the development process and improves code maintainability.

There are several popular JavaScript ORMs available, each supporting different databases. In this explanation, we will discuss three widely-used ORMs: Sequelize, Mongoose, and TypeORM.

Sequelize (Relational Databases):

Sequelize is an ORM for Node.js that supports various relational databases like PostgreSQL, MySQL, SQLite, and Microsoft SQL Server. It provides a simple API to interact with databases using JavaScript objects and functions.

Example: Sequelize with PostgreSQL

Installation:

npm install sequelize pg pg-hstore

Usage:

const { Sequelize, DataTypes } = require('sequelize');

const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: 'postgres',
});

// Define a 'User' model
const User = sequelize.define('User', {
  firstName: {
    type: DataTypes.STRING,
  },
  lastName: {
    type: DataTypes.STRING,
  },
});

(async () => {
  await sequelize.sync({ force: true });

  // Create a new user
  const newUser = await User.create({
    firstName: 'John',
    lastName: 'Doe',
  });

  console.log(`Created user: ${newUser.firstName} ${newUser.lastName}`);
})();

Mongoose (MongoDB):

Mongoose is an ORM for MongoDB in Node.js, providing a simple and schema-based solution to model application data.

Example: Mongoose with MongoDB

Installation:

npm install mongoose

Usage:

const mongoose = require('mongoose');

const { Schema } = mongoose;

mongoose.connect('mongodb://localhost/test', { useNewUrlParser: true, useUnifiedTopology: true });

// Define a 'User' schema and model
const userSchema = new Schema({
  firstName: String,
  lastName: String,
});

const User = mongoose.model('User', userSchema);

(async () => {
  // Create a new user
  const newUser = await User.create({
    firstName: 'John',
    lastName: 'Doe',
  });

  console.log(`Created user: ${newUser.firstName} ${newUser.lastName}`);
})();

TypeORM (Various Databases):

TypeORM is a versatile ORM for Node.js that supports multiple databases, including MySQL, PostgreSQL, SQLite, Microsoft SQL Server, and MongoDB. It is particularly useful for TypeScript projects.

Example: TypeORM with MySQL

Installation:

npm install typeorm mysql

Usage:

const { DataSource, EntitySchema } = require("typeorm");

class User {
  constructor(id, firstName, lastName) {
    this.id = id;
    this.firstName = firstName;
    this.lastName = lastName;
  }
}

(async () => {
  const datasource = new DataSource({
    type: "mysql",
    host: "localhost",
    username: "user",
    password: "password",
    database: "mydb",
    logging: true,
    entities: [
      new EntitySchema({
        target: User,
        name: "User",
        columns: {
          id: {
            primary: true,
            generated: true,
          },
          firstName: {
            type: "varchar",
          },
          lastName: {
            type: "varchar",
          },
        },
      }),
    ],
    synchronize: true,
  });
  await datasource.initialize();

  const userRepository = datasource.getRepository("User");

  // Create a new user
  const newUser = new User();
  newUser.firstName = "John";
  newUser.lastName = "Doe";

  await userRepository.save(newUser);

  console.log(`Created user: ${newUser.firstName} ${newUser.lastName}`);
})();

Objection.js (Relational Databases):

Objection.js is an ORM for Node.js that supports various relational databases like PostgreSQL, MySQL, SQLite, and Microsoft SQL Server. It is built on top of Knex.js and provides a simple API for interacting with databases using JavaScript objects and functions.

Example: Objection.js with SQLite

Installation:

npm install objection knex sqlite3

Usage:

const { Model, knex } = require('objection');
const Knex = require('knex');

const knexConfig = {
  client: 'sqlite3',
  connection: {
    filename: './database.sqlite3',
  },
  useNullAsDefault: true,
};

const knexInstance = Knex(knexConfig);
Model.knex(knexInstance);

class User extends Model {
  static get tableName() {
    return 'users';
  }
}

(async () => {
  await knexInstance.schema.createTable('users', (table) => {
    table.increments('id').primary();
    table.string('firstName');
    table.string('lastName');
  });

  // Create a new user
  const newUser = await User.query().insert({
    firstName: 'John',
    lastName: 'Doe',
  });

  console.log(`Created user: ${newUser.firstName} ${newUser.lastName}`);
})();

Waterline (Various Databases):

Waterline is a versatile ORM for Node.js that supports multiple databases, including PostgreSQL, MySQL, SQLite, Microsoft SQL Server, and MongoDB. It is part of the Sails.js web application framework but can also be used independently.

Example: Waterline with MongoDB

Installation:

npm install waterline waterline-mongo

Usage:

const Waterline = require('waterline');
const WaterlineMongo = require('waterline-mongo');

const waterline = new Waterline();

const userCollection = Waterline.Collection.extend({
  identity: 'user',
  datastore: 'default',
  primaryKey: 'id',
  attributes: {
    firstName: 'string',
    lastName: 'string',
  },
});

waterline.registerModel(userCollection);

const config = {
  datastores: {
    default: {
      adapter: 'mongo',
      url: 'mongodb://localhost:27017/test',
    },
  },
  adapters: {
    mongo: WaterlineMongo,
  },
};

(async () => {
  const orm = await waterline.initialize(config);

  // Create a new user
  const newUser = await orm.models.user.create({
    firstName: 'John',
    lastName: 'Doe',
  }).fetch();

  console.log(`Created user: ${newUser.firstName} ${newUser.lastName}`);
})();

In each example, we defined a User model/schema/entity and then created a new user with the same first and last name. Despite using different ORMs and databases, the examples all followed a similar pattern to demonstrate how the respective ORM could be used for the same purpose.

Next Steps

Docker Fundementals

• Networking
• Volumes

Running Containerised Databases with Docker Compose

• Containersing Databases

NoSQL Databases

• MongoDB
• Neo4j

Relational Databases

• Normal Forms
• ACID
• Joins
• Postgres

Homework

Task 1

Write a pair of node applications that:

• Initialize a database (init-ORM_NAME-DB_NAME.js)
• Perform CRUD operations against one or more entities in the database (app-ORM_NAME_DB_NAME.js)

For each database, use the specified ORM/Driver:

| Database | ORM/Driver      |
|----------|-----------------|
| MySql    | TypeORM         |
| MongoDB  | Mongoose        |
| Postgres | Sequelize       |
| Neo4j    | Neo4j JS Driver |

Task 2

Write a docker-compose.yaml file to allow a user to run your Bit-By-Bit application. This includes:

1. Front-end application
2. HTTP API
3. Database (both in-memory and MongoDB)

You should provide a means for users to start you application with both an in-memory and a MongoDB database.

Task 3

Complete the Building a Basic Relational Database Engine with Test-Driven Development worksheet.