Difference in CRUD operations in SQL and NoSQL Databases

CRUD (Create, Read, Update, Delete) operations are fundamental actions performed on data in databases. The differences in how these operations are handled between SQL (relational databases) and NoSQL (non-relational databases) databases are rooted in the underlying data models and structures.

SQL Databases

Data Model:
SQL databases use a structured, tabular data model.
Data is organized into tables with predefined schemas.
Tables have rows and columns, and relationships between tables are established using foreign keys.

Create (Insert): Data is inserted into specific tables, adhering to the table’s predefined structure.

sql INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
Read (Select): Data is queried using SQL SELECT statements.
sql SELECT column1, column2, ... FROM table_name WHERE condition;
Update (Update): Data is modified in existing rows.
sql UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
Delete (Delete): Rows are deleted from a table based on specified conditions.
sql DELETE FROM table_name WHERE condition;

NoSQL Databases

Data Model:

• NoSQL databases employ various data models, including document-oriented, key-value, wide-column store, and graph databases.
  The structure is more flexible, and each document or item can have different fields.
• CRUD Operations:
  Create (Insert): Data is typically inserted as documents, items, or key-value pairs without a predefined schema.

javascript db.collection_name.insert({ field1: value1, field2: value2, ... });

• Read (Find/Get): Data is retrieved based on queries, often using a flexible JSON-like syntax.Example in MongoDB:

javascript db.collection_name.find({ field: value });

• Update (Update/Modify): Existing documents or items are updated.Example in MongoDB:

javascript db.collection_name.update({ field: value }, { $set: { new_field: new_value } });

• Delete (Remove/Delete): Documents or items are removed based on specified conditions.

javascript db.collection_name.remove({ field: value });

Key Differences

• Schema:
  SQL databases have a rigid, predefined schema
  NoSQL databases are schema-less or have a dynamic schema.
• Flexibility:
  SQL databases offer less flexibility in terms of changing the schema.
  NoSQL databases provide more flexibility as the data model can evolve over time.
• Scaling:
  SQL databases typically scale vertically (adding more resources to a single server).
  NoSQL databases are often designed to scale horizontally (adding more servers to distribute the load).

CRUD Operations in MongoDB

MongoDB is a NoSQL database that stores data in a flexible, JSON-like format called BSON. Here’s a brief explanation and examples of how to perform CRUD operations in MongoDB using its official MongoDB Node.js driver.

1.Create (Insert)
To insert data into MongoDB, you can use the insertOne or insertMany method. Here’s an example using insertOne:

const MongoClient = require('mongodb').MongoClient;
const url = 'mongodb://localhost:27017';
const dbName = 'mydatabase';
MongoClient.connect(url, { useNewUrlParser: true, useUnifiedTopology: true }, (err, client) => {
  if (err) throw err;
  const db = client.db(dbName);
  const collection = db.collection('mycollection');

  // Insert one document
  collection.insertOne({
    name: 'John Doe',
    age: 30,
    city: 'New York'
  }, (err, result) => {
    if (err) throw err;

    console.log('Document inserted');
    client.close();
  });
});

2.Read (Query)
To query data from MongoDB, you can use the find method. Here’s an example:

const MongoClient = require('mongodb').MongoClient;

const url = 'mongodb://localhost:27017';
const dbName = 'mydatabase';
MongoClient.connect(url, { useNewUrlParser: true, useUnifiedTopology: true }, (err, client) => {

  if (err) throw err;
  const db = client.db(dbName);
  const collection = db.collection('mycollection');

  // Find documents
  collection.find({ city: 'New York' }).toArray((err, documents) => {

    if (err) throw err;
    console.log('Documents found:', documents);
    client.close();
  });
});

3.Update
To update data in MongoDB, you can use the updateOne or updateMany method. Here’s an example using updateOne:

const MongoClient = require('mongodb').MongoClient;
const url = 'mongodb://localhost:27017';
const dbName = 'mydatabase';
MongoClient.connect(url, { useNewUrlParser: true, useUnifiedTopology: true }, (err, client) => {

  if (err) throw err;
  const db = client.db(dbName);
  const collection = db.collection('mycollection');

  // Update one document
  collection.updateOne(
    { name: 'John Doe' },
    { $set: { age: 31 } },
    (err, result) => {
      if (err) throw err;

      console.log('Document updated');
      client.close();
    }
  );
});

4.Delete
To delete data in MongoDB, you can use the deleteOne or deleteMany method. Here’s an example using deleteOne:

const MongoClient = require('mongodb').MongoClient;
const url = 'mongodb://localhost:27017';
const dbName = 'mydatabase';
MongoClient.connect(url, { useNewUrlParser: true, useUnifiedTopology: true }, (err, client) => {

  if (err) throw err;
  const db = client.db(dbName);
  const collection = db.collection('mycollection');

  // Delete one document
  collection.deleteOne({ name: 'John Doe' }, (err, result) => {

    if (err) throw err;
    console.log('Document deleted');
    client.close();
  });
});</code.

Make sure to replace the connection URL, database name and collection name with your specific values. Additionally, handle errors appropriately in a production environment.

The post How to perform Create, Read, Update, and Delete operations using MongoDB appeared first on Exatosoftware.