PostgreSQL - Getting Started

This is an introduction to PostgreSQL database.

What is PostgreSQL?

PostgreSQL is a RDBMS or Relational Database Management System. It is one of the popular and most advanced open source relational database and was developed at Berkeley Computer Science Department (University of California).

Versions of PostgreSQL

PostgreSQL can be installed on Windows, Mac and Linux personal computers and can also be installed on servers like Google Cloud, AWS (Amazon Web Services) etc.

Connecting to the database using psql

After installing the PostgreSQL database on your computer you can connect to it using psql command from your terminal.

Read more...

How to install Postgres.app to use PostgreSQL database on Mac

In this tutorial we will learn to install Postgres.app to use PostgreSQL database on Mac.

What is Postgres.app?

Postgres.app is a full-featured PostgreSQL installation packaged as a standard Mac app. -- https://postgresapp.com

Download

Visit postgresapp.com and download the software on your Mac.

Run the installer and move the Postgres.app into the Applications directory.

Default settings

Postgres.app will install on your Mac with the following default settings.

Host	localhost
Port	5432
User	your system user name
Database	same as user
Password	none
Connection URL	postgresql://localhost

Read more...

How to install PostgreSQL on Mac using Homebrew

Click the link: "How to install PostgreSQL on Mac using Homebrew"

In this tutorial we will learn to install PostgreSQL database on Mac using Homebrew.

Prerequisite

It is assumed that you have Homebrew installed on your Mac.

If you don't have Homebrew installed on your Mac then open Terminal and run the following command.

$ /usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

Alright, time to install PostgreSQL on Mac.

Install PostgreSQL using Homebrew

In Terminal run the following command to install PostgreSQL on Mac using Homebrew.

$ brew install postgres

Start PostgreSQL

To start PostgreSQL run the following command in the Terminal.

$ brew services start postgres

Stop PostgreSQL

To stop PostgreSQL run the following command in the Terminal.

$ brew services stop postgres

Restart PostgreSQL

To restart PostgreSQL run the following command in the Terminal.

$ brew services restart postgres

Read more...

MongoDB - Delete Documents

Click the link: "MongoDB - Delete Documents"

In this MongoDB tutorial we will learn to delete documents.

Login to your MongoDB server and insert the following documents.

For this tutorial I will insert the documents in the subscription collection.

> db.subscription.insertMany([
  {
    "firstname": "John",
    "lastname": "Doe",
    "uid": 1,
    "accountstatus": "ACTIVE",
    "plan": "TRIAL15DAYS",
    "modifiedAt": null,
    "createdAt": new Date()
  },
  {
    "firstname": "Jane",
    "lastname": "Doe",
    "uid": 2,
    "accountstatus": "ACTIVE",
    "plan": "PLAN30DAYS",
    "modifiedAt": null,
    "createdAt": new Date()
  },
  {
    "firstname": "Jim",
    "lastname": "Doe",
    "uid": 3,
    "accountstatus": "SUSPENDED",
    "plan": "PLAN60DAYS",
    "modifiedAt": null,
    "createdAt": new Date()
  },
  {
    "firstname": "Alice",
    "lastname": "Doe",
    "uid": 4,
    "accountstatus": "ACTIVE",
    "plan": "PLAN1YEAR",
    "modifiedAt": null,
    "createdAt": new Date()
  }
])

Note! We are using new Date() to set the createdAt to current date time.

Delete one document

We use the deleteOne method to delete one document from a given collection.

In the following example we are deleting document having uid equal to 4.

> db.subscription.deleteOne({ "uid": 4 })

Read more...

MongoDB - Update Documents

Click the link: "MongoDB - Update Documents"

In this MongoDB tutorial we will learn to update documents.

Login to your MongoDB server and insert the following documents.

For this tutorial I will insert the documents in the subscription collection.

> db.subscription.insertMany([
  {
    "firstname": "John",
    "lastname": "Doe",
    "uid": 1,
    "accountstatus": "ACTIVE",
    "plan": "TRIAL15DAYS",
    "modifiedAt": null,
    "createdAt": new Date()
  },
  {
    "firstname": "Jane",
    "lastname": "Doe",
    "uid": 2,
    "accountstatus": "ACTIVE",
    "plan": "PLAN30DAYS",
    "modifiedAt": null,
    "createdAt": new Date()
  },
  {
    "firstname": "Jim",
    "lastname": "Doe",
    "uid": 3,
    "accountstatus": "SUSPENDED",
    "plan": "PLAN60DAYS",
    "modifiedAt": null,
    "createdAt": new Date()
  },
  {
    "firstname": "Alice",
    "lastname": "Doe",
    "uid": 4,
    "accountstatus": "ACTIVE",
    "plan": "PLAN1YEAR",
    "modifiedAt": null,
    "createdAt": new Date()
  }
])

Note! We are using new Date() to set the createdAt to current date time.

Update one document

To update a single document we use updateOne method.

Syntax

db.collectionName.updateOne(filter, update)

Where, filter is the condition to get the required document and update is the part of the document we want to update.

In the following example we are updating the account status of user having uid equal to 3 from SUSPENDED to ACTIVE.

> db.subscription.updateOne(
  { "uid": 3 },
  {
    $set: { "accountstatus": "ACTIVE" },
    $currentDate: { "modifiedAt": true }
  }
)

We use $set operator to update the value of accountstatus.

We use $currentDate operator to set the modifiedAt to current date.

If we now fetch the above document we will get the following.

> db.subscription.find({ "uid": 3 }).pretty()

{
  "_id" : ObjectId("5d7715d5b385796f53d4c8ff"),
  "firstname" : "Jim",
  "lastname" : "Doe",
  "uid" : 3,
  "accountstatus" : "ACTIVE",
  "plan" : "PLAN60DAYS",
  "modifiedAt" : ISODate("2019-09-10T03:18:42.406Z"),
  "createdAt" : ISODate("2019-09-10T03:17:41.130Z")
}

Read more...

MongoDB - Query for Null and Missing fields

Click the link: "MongoDB - Query for Null and Missing fields"

In this MongoDB tutorial we will learn to query for Null and missing fields.

Login to your MongoDB server and insert the following documents.

For this tutorial I will insert the documents in the deliveryAddress collection.

> db.deliveryAddress.insertMany([
  {
    "addressLine1": "House #1, 2nd Street, 1st Main Road",
    "addressLine2": "Opposite Bakery",
    "city": "Bangalore",
    "state": "KA",
    "country": "India",
    "pincode": "560001",
    "contactphone": "919800000000",
    "contactperson": "John Doe"
  },
  {
    "addressLine1": "House #20, 3rd Street, 5th Main Road",
    "addressLine2": null,
    "city": "Bangalore",
    "state": "KA",
    "country": "India",
    "pincode": "560001",
    "contactperson": "Jane Doe"
  },
  {
    "addressLine1": "House #20, 5th Street, 10th Main Road",
    "addressLine2": null,
    "city": "Bangalore",
    "state": "KA",
    "country": "India",
    "pincode": "560001",
    "contactphone": null,
    "contactperson": "Jim Doe"
  }
])

Select all documents having null field

In the following example we are fetching all the documents that have addressLine2 field set to null value.

> db.deliveryAddress.find({ "addressLine2": null }).pretty()


{
  "_id" : ObjectId("5d76170f89ccf6fae0c7974b"),
  "addressLine1" : "House #20, 3rd Street, 5th Main Road",
  "addressLine2" : null,
  "city" : "Bangalore",
  "state" : "KA",
  "country" : "India",
  "pincode" : "560001",
  "contactperson" : "Jane Doe"
}
{
  "_id" : ObjectId("5d76170f89ccf6fae0c7974c"),
  "addressLine1" : "House #20, 5th Street, 10th Main Road",
  "addressLine2" : null,
  "city" : "Bangalore",
  "state" : "KA",
  "country" : "India",
  "pincode" : "560001",
  "contactphone" : null,
  "contactperson" : "Jim Doe"
}

The $exists operator

To check if a document does or does not contains a given field we use the $exists operator.

Syntax

We use the following syntax to fetch all the documents of a given collection that does contains a given field.

db.collectionName.find({ field: { $exists: true } })

We use the following syntax to fetch all the documents of a given collection that does not contains a given field.

db.collectionName.find({ field: { $exists: false } })

Read more...

MongoDB - Query an Array of Embedded Documents

Click the link: "MongoDB - Query an Array of Embedded Documents"

In this MongoDB tutorial we will learn to query an array of embedded documents.

Login to your MongoDB server and insert the following documents.

For this tutorial I will insert the documents in the books collection.

> db.books.insertMany([
  {
    "title": "The C Programming Language",
    "author": ["Brian Kernighan", "Dennis Ritchie"],
    "inventory": [
      { "warehouse": "W1", "qty": 3000 },
      { "warehouse": "W2", "qty": 750 }
    ]
  },
  {
    "title": "MongoDB in Action",
    "author": ["Kyle Banker"],
    "inventory": [
      { "warehouse": "W2", "qty": 100 },
      { "warehouse": "W3", "qty": 1500 }
    ]
  },
  {
    "title": "Node.js in Action",
    "author": ["Marc Harter", "Nathan Rajlich", "T. J. Holowaychuk", "Mike Cantelon"],
    "inventory": [
      { "warehouse": "W1", "qty": 100 },
      { "warehouse": "W3", "qty": 250 }
    ]
  }

]);

Select based on exact match of embedded document in an array

In the following example we are fetching all the documents having the exact embedded document { "warehouse": "W1", "qty": 100 } in the inventory array.

Note! The field order is important i.e. the first field of the embedded document must be "warehouse" and must have value "W1" and the second field of the embedded document must be "qty" and must have value 100.

> db.books.find({ "inventory": { "warehouse": "W1", "qty": 100 } }).pretty();



{
  "_id" : ObjectId("5d2d9afd81a3cd0a9239c121"),
  "title" : "Node.js in Action",
  "author" : [
    "Marc Harter",
    "Nathan Rajlich",
    "T. J. Holowaychuk",
    "Mike Cantelon"
  ],
  "inventory" : [
    {
      "warehouse" : "W1",
      "qty" : 100
    },
    {
      "warehouse" : "W3",
      "qty" : 250
    }
  ]
}

If we change the field order of the embedded document in the query then we will not get the result.

In the following query we are changing the field order of the embedded document to { "qty": 100, "warehouse": "W1" } and it gives us no result.

> db.books.find({ "inventory": { "qty": 100, "warehouse": "W1" } }).pretty();

Read more...

MongoDB - Query an Array

Click the link: "MongoDB - Query an Array"

In this MongoDB tutorial we will learn to query arrays.

Login to your MongoDB server and insert the following documents.

For this tutorial I will insert the following documents in the posts collection.

> db.posts.insertMany([
  {
    "title": "This is my first post!",
    "tags": ["awesome", "post", "blog"]
  },
  {
    "title": "Dinner time :)",
    "tags": ["food", "dinner", "fun"]
  },
  {
    "title": "Birthday Treat",
    "tags": ["fun", "food", "birthday", "treat", "party"]
  }
]);

Match exact array

In the following example we are going to fetch documents having exactly 3 elements in the array and values are awesome, post and blog in that order.

> db.posts.find({
  "tags": ["awesome", "post", "blog"]
}).pretty();


{
  "_id" : ObjectId("5d1b43d9085e37060f4c36b5"),
  "title" : "This is my first post!",
  "tags" : [
    "awesome",
    "post",
    "blog"
  ]
}

Note! If we change the positions of the array elements in the above query then we will not get the same result.

Query an array for an element

In the following example we are going to fetch all the documents having an array field tags that contains food as one of the element in the array.

> db.posts.find({ "tags": "food" }).pretty();


{
  "_id" : ObjectId("5d1b43d9085e37060f4c36b6"),
  "title" : "Dinner time :)",
  "tags" : [
    "food",
    "dinner",
    "fun"
  ]
}
{
  "_id" : ObjectId("5d1b43d9085e37060f4c36b7"),
  "title" : "Birthday Treat",
  "tags" : [
    "fun",
    "food",
    "birthday",
    "treat",
    "party"
  ]
}

Read more...

MongoDB - Query Embedded Document

Click the link: "MongoDB - Query Embedded Document"

In this MongoDB tutorial we will learn to query embedded documents.

In the previous tutorial we learned about how to query documents and about logical operators and how to select specific fields. Feel free to check that out.

Embedded or Nested documents

For this tutorial lets go ahead and create some embedded documents. So, connect to your MongoDB server and run the following command to insert some documents in the shapes collection.

> db.shapes.insertMany([
  {
    "shape": "rectangle",
    "item": "Rect 1",
    "dim": {
      "length": 10,
      "breadth": 20,
      "uom": "cm"
    }
  },
  {
    "shape": "rectangle",
    "item": "Rect 2",
    "dim": {
      "length": 20,
      "breadth": 30,
      "uom": "cm"
    }
  },
  {
    "shape": "rectangle",
    "item": "Rect 3",
    "dim": {
      "length": 5,
      "breadth": 10,
      "uom": "cm"
    }
  },
  {
    "shape": "square",
    "item": "Sq 1",
    "dim": {
      "side": 10.5,
      "uom": "cm"
    }
  },
  {
    "shape": "square",
    "item": "Sq 2",
    "dim": {
      "side": 20,
      "uom": "cm"
    }
  },
  {
    "shape": "square",
    "item": "Sq 3",
    "dim": {
      "side": 15,
      "uom": "inch"
    }
  },
  {
    "shape": "square",
    "item": "Sq 4",
    "dim": {
      "side": 25.5,
      "uom": "inch"
    }
  }
]);

We learned how to create embedded documents in the Insert Document tutorial. Feel free to check that out.

Alright, let's start simple then progress to advance problems.

Find all rectangles

To find all the documents in the shapes collection that represents a rectangle we have to run the following command.

> db.shapes.find({ "shape": "rectangle" }).pretty();


{
  "_id" : ObjectId("5d175dffba3250e57f98faca"),
  "shape" : "rectangle",
  "item" : "Rect 1",
  "dim" : {
    "length" : 10,
    "breadth" : 20,
    "uom" : "cm"
  }
}
{
  "_id" : ObjectId("5d175dffba3250e57f98facb"),
  "shape" : "rectangle",
  "item" : "Rect 2",
  "dim" : {
    "length" : 20,
    "breadth" : 30,
    "uom" : "cm"
  }
}
{
  "_id" : ObjectId("5d175dffba3250e57f98facc"),
  "shape" : "rectangle",
  "item" : "Rect 3",
  "dim" : {
    "length" : 5,
    "breadth" : 10,
    "uom" : "cm"
  }
}

Read more...

MongoDB - Query Document

Click the link: "MongoDB - Query Document"

In this MongoDB tutorial we will learn to query documents.

Select all the documents

To select all the documents in a collection we use the find() method.

In the following example we are listing all the documents in the students collection.

> db.students.find();

{ "_id" : ObjectId("5d16c9e9e8cb73839ac9f2f1"), "firstname" : "Yusuf", "lastname" : "Shakeel", "studentid" : "s01" }
{ "_id" : ObjectId("5d16c9efe8cb73839ac9f2f2"), "firstname" : "Jane", "lastname" : "Doe", "studentid" : "s02" }
{ "_id" : ObjectId("5d16c9efe8cb73839ac9f2f3"), "firstname" : "John", "lastname" : "Doe", "studentid" : "s03" }
{ "_id" : ObjectId("5d16ca23e8cb73839ac9f2f4"), "firstname" : "Alice", "lastname" : "Doe", "studentid" : "s04", "score" : 10.5 }
{ "_id" : ObjectId("5d16ca23e8cb73839ac9f2f5"), "firstname" : "Bob", "lastname" : "Doe", "studentid" : "s05", "date_of_birth" : ISODate("2000-01-01T00:00:00Z") }
{ "_id" : ObjectId("5d16cfa4e8cb73839ac9f2f6"), "firstname" : "Eve", "lastname" : "Doe", "studentid" : "s06", "contact_phone" : { "primary" : { "number" : "+919800000000", "name" : "Bill Doe", "relation" : "Father" }, "secondary" : [ { "number" : "+919800000001", "name" : "Mac Doe", "relation" : "Brother" } ] } }

Select all documents and render in easy-to-read format

To render the result in easy to read format we use the pretty() method.

In the following example we are selecting all the documents in the students collection and listing them in an easy-to-read format.

> db.students.find().pretty();

{
  "_id" : ObjectId("5d16c9e9e8cb73839ac9f2f1"),
  "firstname" : "Yusuf",
  "lastname" : "Shakeel",
  "studentid" : "s01"
}
{
  "_id" : ObjectId("5d16c9efe8cb73839ac9f2f2"),
  "firstname" : "Jane",
  "lastname" : "Doe",
  "studentid" : "s02"
}
{
  "_id" : ObjectId("5d16c9efe8cb73839ac9f2f3"),
  "firstname" : "John",
  "lastname" : "Doe",
  "studentid" : "s03"
}
{
  "_id" : ObjectId("5d16ca23e8cb73839ac9f2f4"),
  "firstname" : "Alice",
  "lastname" : "Doe",
  "studentid" : "s04",
  "score" : 10.5
}
{
  "_id" : ObjectId("5d16ca23e8cb73839ac9f2f5"),
  "firstname" : "Bob",
  "lastname" : "Doe",
  "studentid" : "s05",
  "date_of_birth" : ISODate("2000-01-01T00:00:00Z")
}
{
  "_id" : ObjectId("5d16cfa4e8cb73839ac9f2f6"),
  "firstname" : "Eve",
  "lastname" : "Doe",
  "studentid" : "s06",
  "contact_phone" : {
    "primary" : {
      "number" : "+919800000000",
      "name" : "Bill Doe",
      "relation" : "Father"
    },
    "secondary" : [
      {
        "number" : "+919800000001",
        "name" : "Mac Doe",
        "relation" : "Brother"
      }
    ]
  }
}

The find() method is equivalent to the following SQL statement.

SELECT * FROM students;

Read more...

MongoDB - Insert Document

Click the link: "MongoDB - Insert Document"

In this MongoDB tutorial we will learn to insert documents.

Insert one document

To insert one document into a collection we use the db.collection.insertOne() method.

Note! collection is the name of the collection in which you want to insert the document.

In the following example we are inserting one document into students collection.

> db.students.insertOne({
  "firstname": "Yusuf",
  "lastname": "Shakeel",
  "studentid": "s01"
});

On success we will get a similar response in the terminal.

{
  "acknowledged" : true,
  "insertedId" : ObjectId("5d16c693e8cb73839ac9f2ec")
}

Note! insertedId is a unique id (primary key) given to each document in a collection by MongoDB.

To fetch the inserted documents in a collection we use the find() method.

> db.students.find()
{ "_id" : ObjectId("5d16c693e8cb73839ac9f2ec"), "firstname" : "Yusuf", "lastname" : "Shakeel", "studentid" : "s01" }

We will learn more about find() in the Query Document tutorial.

Read more...

MongoDB - Database and Collection

Click the link: "MongoDB - Database and Collection"

In this MongoDB tutorial we will learn about databases, collections and documents.

What is a document?

A document is a key value pair. It is a JSON (JavaScript Object Notation).

Following example represents a student document.

{
  "student_id": "s0001",
  "first_name": "Yusuf",
  "last_name": "Shakeel"
}

student_id, first_name and last_name are referred as the fields or keys of the document and s0001, Yusuf and Shakeel are the respective values of the given fields.

If you are familiar with RDBMS (eg. MySQL) then a MongoDB document is like a row in a RDBMS table.

What is a collection?

A collection is a set of documents.

A single collection can have zero or more number of documents.

Think of MongoDB collections as tables in RDBMS.

Read more...

MongoDB - Getting Started

Click the link: "MongoDB - Getting Started"

This is the MongoDB getting started tutorial.

What is MongoDB?

MongoDB is a general purpose, document-based, distributed database built for modern application developers and for the cloud era. No database is more productive to use.
- mongodb.com

Prerequisite

If you have some prior knowledge of relational database (RDBMS like MySQL) then that is an advantage for you. Though MongoDB is not a RDBMS but having some database knowledge will help you understand MongoDB quickly.

Read more...