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...

ES6 - Template Literals (Template strings)

Watch the video tutorial: ES6 Template Literals (Template strings) #02

In this ES6 JavaScript tutorial we will learn about Template Literals (Template strings).

What are Template Literals?

Template literals are string literals that allows us to embed expressions, multi-line strings and string interpolation.

Template literals are enclosed using backtick ` ` instead of single quotes and double quotes.

Let's print the string Hello World using single and double quotes.

console.log('Hello World');
console.log("Hello World");

Now, let's print the same string using template literal.

console.log(`Hello World`);

Read more...

ES6 - var let const

Check out the tutorial video: ES6 var let const #01

In this ES6 JavaScript tutorial we will learn to create variables using var, let and const.

The var keyword

Alright, let's begin by creating a variable using the var keyword.

Let's say we want to create a variable x and assign it a value 10. So, we will write the following line.

var x = 10;

Now, if we want to print the value of x we can console log it by writing the following line.

var x = 10;
console.log(x);

And we get the output 10.

Few important points we must note while using the var keyword to declare variables.

We can redefine the same variable x again and we will not get any error.

So, we can write something like the following.

var x = 10;
console.log(x); // 10
var x = 20;
console.log(x); // 20

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 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...