Database Integration

Integrating databases with JavaScript applications is essential for storing and managing data. This guide will cover various aspects of database integration, including types of databases, popular database management systems (DBMS), how to connect to a database, and performing CRUD (Create, Read, Update, Delete) operations.

Database Integration

Integrating databases with JavaScript applications is essential for storing and managing data. This guide will cover various aspects of database integration, including types of databases, popular database management systems (DBMS), how to connect to a database, and performing CRUD (Create, Read, Update, Delete) operations.

Types of Databases

  1. Relational Databases (SQL): Structured databases that use tables to store data. Examples include MySQL, PostgreSQL, and SQLite.
  2. NoSQL Databases: Flexible databases that store data in various formats like documents, key-value pairs, graphs, or wide-column stores. Examples include MongoDB, Cassandra, and Redis.

Popular Database Management Systems

Relational Databases (SQL)

  • MySQL: An open-source relational database management system.
  • PostgreSQL: An open-source, object-relational database system with a focus on extensibility and standards compliance.
  • SQLite: A C-language library that implements a small, fast, self-contained, high-reliability, full-featured SQL database engine.

NoSQL Databases

  • MongoDB: A document-based NoSQL database known for its flexibility and scalability.
  • Cassandra: A distributed NoSQL database designed to handle large amounts of data across many commodity servers.
  • Redis: An in-memory key-value store known for its speed and versatility.

Connecting to a Database

Using MySQL with Node.js

  1. Install MySQL Module
   npm install mysql
  1. Connect to MySQL Database
db.js 
const mysql = require('mysql');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'database_name'
});

connection.connect(err => {
  if (err) {
    console.error('Error connecting: ' + err.stack);
    return;
  }
  console.log('Connected as id ' + connection.threadId);
});

// Don't forget to close the connection
connection.end();

Using MongoDB with Node.js

  1. Install MongoDB Module
    npm install mongodb
  1. Connect to MongoDB Database
db.js 
const { MongoClient } = require('mongodb');

const url = 'mongodb://localhost:27017';
const dbName = 'database_name';

MongoClient.connect(url, { useUnifiedTopology: true }, (err, client) => {
  if (err) {
    console.error('Error connecting: ' + err);
    return;
  }
  console.log('Connected successfully to server');
  const db = client.db(dbName);

  // Perform operations

  client.close();
});

Performing CRUD Operations

CRUD Operations in MySQL

Let's start by describing what CRUD means and how we use it when integrating databases

  • C Create
  • R Read
  • U Update
  • D Delete
  1. Create
utils/create.js 
const mysql = require('mysql');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'database_name'
});

connection.connect();

const sql = 'INSERT INTO users (name, age) VALUES (?, ?)';
const values = ['John Doe', 30];

connection.query(sql, values, (err, result) => {
  if (err) throw err;
  console.log('Record inserted, ID: ' + result.insertId);
});

connection.end();
  1. Read
utils/read.js 
const mysql = require('mysql');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'database_name'
});

connection.connect();

const sql = 'SELECT * FROM users';

connection.query(sql, (err, results) => {
  if (err) throw err;
  console.log(results);
});

connection.end();
  1. Update
utils/update.js 
const mysql = require('mysql');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'database_name'
});

connection.connect();

const sql = 'UPDATE users SET age = ? WHERE name = ?';
const values = [35, 'John Doe'];

connection.query(sql, values, (err, result) => {
  if (err) throw err;
  console.log('Record updated, Rows affected: ' + result.affectedRows);
});

connection.end();
  1. Delete
utils/delete.js 
const mysql = require('mysql');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'database_name'
});

connection.connect();

const sql = 'DELETE FROM users WHERE name = ?';
const values = ['John Doe'];

connection.query(sql, values, (err, result) => {
  if (err) throw err;
  console.log('Record deleted, Rows affected: ' + result.affectedRows);
});

connection.end();

CRUD Operations in MongoDB

  1. Create
utils/create.js 
const { MongoClient } = require('mongodb');

const url = 'mongodb://localhost:27017';  // Connection URL
const dbName = 'database_name';  // Database Name

MongoClient.connect(url, { useUnifiedTopology: true }, (err, client) => {
  if (err) throw err;
  console.log('Connected successfully to server');
  const db = client.db(dbName);  // Database instance

  const collection = db.collection('users');  // Collection instance
  collection.insertOne({ name: 'John Doe', age: 30 }, (err, result) => {
    if (err) throw err;
    console.log('Record inserted, ID: ' + result.insertedId);  // Log the inserted ID
    client.close();  // Close the connection
  });
});
  1. Read
utils/read.js 
const { MongoClient } = require('mongodb');

const url = 'mongodb://localhost:27017';  // Connection URL
const dbName = 'database_name';  // Database Name

MongoClient.connect(url, { useUnifiedTopology: true }, (err, client) => {
  if (err) throw err;
  console.log('Connected successfully to server');
  const db = client.db(dbName);  // Database instance

  const collection = db.collection('users');  // Collection instance
  collection.find({}).toArray((err, docs) => {
    if (err) throw err;
    console.log(docs);  // Log the retrieved documents
    client.close();  // Close the connection
  });
});
  1. Update
utils/update.js 
const { MongoClient } = require('mongodb');

const url = 'mongodb://localhost:27017';  // Connection URL
const dbName = 'database_name';  // Database Name

MongoClient.connect(url, { useUnifiedTopology: true }, (err, client) => {
  if (err) throw err;
  console.log('Connected successfully to server');
  const db = client.db(dbName);  // Database instance

  const collection = db.collection('users');  // Collection instance
  collection.updateOne({ name: 'John Doe' }, { $set: { age: 35 } }, (err, result) => {
    if (err) throw err;
    console.log('Record updated, Matched count: ' + result.matchedCount);  // Log the matched count
    client.close();  // Close the connection
  });
});
  1. Delete
utils/delete.js 
const { MongoClient } = require('mongodb');

const url = 'mongodb://localhost:27017';  // Connection URL
const dbName = 'database_name';  // Database Name

MongoClient.connect(url, { useUnifiedTopology: true }, (err, client) => {
  if (err) throw err;
  console.log('Connected successfully to server');
  const db = client.db(dbName);  // Database instance

  const collection = db.collection('users');  // Collection instance
  collection.deleteOne({ name: 'John Doe' }, (err, result) => {
    if (err) throw err;
    console.log('Record deleted, Deleted count: ' + result.deletedCount);  // Log the deleted count
    client.close();  // Close the connection
  });
});

All MongoDB CRUD Operations in a Single File

Here's how you can combine all CRUD operations into a single file for better management and execution.

project/src/db/crud-operations.js 
const { MongoClient } = require('mongodb');

const url = 'mongodb://localhost:27017';  // Connection URL
const dbName = 'database_name';  // Database Name

// Function to create a document
const createDocument = (db, callback) => {
  const collection = db.collection('users');
  collection.insertOne({ name: 'John Doe', age: 30 }, (err, result) => {
    if (err) throw err;
    console.log('Record inserted, ID: ' + result.insertedId);
    callback(result);
  });
};

// Function to read documents
const readDocuments = (db, callback) => {
  const collection = db.collection('users');
  collection.find({}).toArray((err, docs) => {
    if (err) throw err;
    console.log(docs);
    callback(docs);
  });
};

// Function to update a document
const updateDocument = (db, callback) => {
  const collection = db.collection('users');
  collection.updateOne({ name: 'John Doe' }, { $set: { age: 35 } }, (err, result) => {
    if (err) throw err;
    console.log('Record updated, Matched count: ' + result.matchedCount);
    callback(result);
  });
};

// Function to delete a document
const deleteDocument = (db, callback) => {
  const collection = db.collection('users');
  collection.deleteOne({ name: 'John Doe' }, (err, result) => {
    if (err) throw err;
    console.log('Record deleted, Deleted count: ' + result.deletedCount);
    callback(result);
  });
};

// Connect to the MongoDB server and perform CRUD operations
MongoClient.connect(url, { useUnifiedTopology: true }, (err, client) => {
  if (err) throw err;
  console.log('Connected successfully to server');
  const db = client.db(dbName);

  // Create a document
  createDocument(db, () => {
    // Read documents
    readDocuments(db, () => {
      // Update a document
      updateDocument(db, () => {
        // Delete a document
        deleteDocument(db, () => {
          client.close();  // Close the connection after all operations
        });
      });
    });
  });
});

Here is an example of how your project structure might look

project/
src/
db/
crud-operations.js
...
...
...

Explanation

  • Create DocumentThis function inserts a new document into the users collection.
  • Read DocumentsThis function retrieves all documents from the users collection.
  • Update DocumentThis function updates the document with the name "John Doe" to have an age of 35.
  • Delete DocumentThis function deletes the document with the name "John Doe".

By combining all CRUD operations into a single file, you can manage your database interactions more efficiently and ensure all operations are executed in sequence.

Best Practices

  • Use Environment VariablesStore sensitive information like database credentials in environment variables instead of hardcoding them.
  • Use Connection PoolingManage database connections efficiently to handle multiple concurrent requests.
  • Handle Errors GracefullyImplement proper error handling to manage database connection errors and query failures.
  • Use ORM/ODMUse Object-Relational Mapping (ORM) or Object Data Modeling (ODM) libraries like Sequelize for SQL databases or Mongoose for MongoDB to simplify database interactions.
Tooling & Workflow: How to use Git and GitHub
This guide is designed to help you become a master at using ...
Using Express
Express is a popular Node.js framework that simplifies the d...