Forem: TechPrane

PostgreSQL to MongoDB Migration Made Easy – A Step-by-Step Video Guide!

FOLASAYO SAMUEL OLAYEMI — Mon, 07 Apr 2025 11:22:29 +0000

Are you thinking of migrating from PostgreSQL to MongoDB but unsure where to begin? You're not alone—and the good news is, it just got a lot easier.

I just released a comprehensive YouTube tutorial that walks you through the entire process using the MongoDB Relational Migrator – a powerful and free tool that simplifies the migration journey from relational databases to NoSQL.

What You’ll Learn:
Smooth Data Transition: Understand the end-to-end process of moving your data from PostgreSQL to MongoDB.

Hands-on with MongoDB Relational Migrator: Learn how to perform schema analysis, data mapping, and execute a seamless migration.

Pro Tips & Best Practices: Discover how to optimize your MongoDB schema post-migration and avoid common migration pitfalls.

Whether you're a developer, DBA, or IT professional, this video is packed with valuable insights to help you modernize your application stack and leverage the flexibility of MongoDB.

Watch it now and take your database skills to the next level: PostgreSQL to MongoDB Migration Made Easy

Thanks for reading...
Happy Coding!

Secure your MongoDB Atlas Cluster with AWS VPC Peering

FOLASAYO SAMUEL OLAYEMI — Thu, 03 Apr 2025 10:27:38 +0000

Enhance your cloud database security by learning how to connect your MongoDB Atlas cluster to your AWS infrastructure using VPC Peering. This setup ensures private, encrypted traffic between your applications and Atlas, eliminating exposure to the public internet.

Key Insights:

The importance of VPC peering for cloud-native applications
Step-by-step guidance on establishing VPC peering between MongoDB Atlas and AWS
Configuration of route tables and DNS settings for secure access
Best practices for securing production-grade database traffic
Verification of connectivity using mongosh

Ideal for: DevOps engineers, backend developers, cloud architects, and teams focused on building secure, scalable infrastructure with MongoDB and AWS.

Technologies Covered:

MongoDB Atlas
AWS VPC
EC2 (for demonstration)
mongosh CLI
Route tables, CIDR blocks, and security groups

Why It Matters:

Implementing VPC Peering is foundational for zero-trust networking in the cloud. For those hosting applications on AWS and utilizing Atlas, this method offers a secure, enterprise-grade connection.

For a comprehensive walkthrough, watch the full tutorial here.

After watching, feel free to like, subscribe, and comment below with topics you'd like us to cover in future videos, such as IP whitelisting, IAM, or Terraform-based setups.

Happy Coding!

How to Use JSONB in PostgreSQL with Step-by-Step Instructions

FOLASAYO SAMUEL OLAYEMI — Wed, 16 Oct 2024 02:18:50 +0000

In this article, we'll explain how to use JSONB in PostgreSQL to store dynamic product attributes efficiently.

We'll cover why JSONB is useful, how to set up PostgreSQL, and perform full CRUD (Create, Read, Update, Delete) operations using Python and Flask. JSONB offers flexibility and performance, making it ideal for storing semi-structured data like product attributes, where schema can vary across records.

Check out this detailed example here! Perfect for hands-on learning and quick tips – don’t miss it!.

1. Setting Up the Database

To get started, we need to set up PostgreSQL and create a database that will store products with JSONB attributes.

Step 1: Install PostgreSQL

For Windows: Download and install PostgreSQL from the official website PostgreSQL Download. Follow the installation wizard to set up your database.
For Linux: Install via the terminal using:

  sudo apt-get update
  sudo apt-get install postgresql postgresql-contrib

For macOS: Read this guide for a complete steps on how to install and setup PostgreSQL

Step 2: Create a Database

Once PostgreSQL is installed, access the PostgreSQL command-line interface (psql):

psql -U postgres

Create a new database for your project:

CREATE DATABASE products_db;

Step 3: Create a Table to Store Products with JSONB Attributes

In your products_db database, create a table that stores product names and attributes as JSONB:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    attributes JSONB
);

Here, the attributes column will store product details (e.g., size, color) in JSONB format, allowing for flexible attributes per product.

Step 4: Ensure Required Permissions

Make sure the PostgreSQL user has the necessary admin permissions to create tables and perform CRUD operations. Use this command to check roles:

\du

To create a user with permissions:

CREATE USER your_username WITH PASSWORD 'your_password';
GRANT ALL PRIVILEGES ON DATABASE products_db TO your_username;

2. Full CRUD Operations Using JSONB

Once the database is ready, we can move to performing CRUD operations (Create, Read, Update, Delete) using Python and Flask. We will connect to PostgreSQL using psycopg2.

Step 1: Install Required Python Libraries

You’ll need the following libraries for database connection and web development:

pip install psycopg2 flask

Step 2: Establish a Database Connection in Python

Using the psycopg2 library, connect to your PostgreSQL database:

import psycopg2

def connect_db():
    conn = psycopg2.connect(
        host="localhost",
        database="products_db",
        user="your_username",
        password="your_password"
    )
    return conn

Step 3: Creating a Product (Insert Operation)

Define an API endpoint in Flask to allow users to create a new product with attributes:

@app.route('/product', methods=['POST'])
def create_product():
    data = request.json
    name = data['name']
    attributes = data['attributes']

    conn = connect_db()
    cur = conn.cursor()
    cur.execute(
        "INSERT INTO products (name, attributes) VALUES (%s, %s) RETURNING id",
        (name, psycopg2.extras.Json(attributes))
    )
    product_id = cur.fetchone()[0]
    conn.commit()
    cur.close()
    conn.close()

    return jsonify({"id": product_id, "message": "Product created successfully!"}), 201

Here, the attributes are stored in JSONB format, and the product is saved in the products table.

Step 4: Reading Products (Read Operation)

Create an endpoint to fetch all products along with their JSONB attributes:

@app.route('/products', methods=['GET'])
def get_products():
    conn = connect_db()
    cur = conn.cursor()
    cur.execute("SELECT id, name, attributes FROM products")
    products = cur.fetchall()
    cur.close()
    conn.close()

    return jsonify([{"id": p[0], "name": p[1], "attributes": p[2]} for p in products]), 200

This endpoint retrieves the products and parses the JSONB attributes.

Step 5: Updating Product Attributes (Update Operation)

To update a product’s attributes, use this endpoint:

@app.route('/product/<int:product_id>', methods=['PUT'])
def update_product(product_id):
    data = request.json
    attributes = data['attributes']

    conn = connect_db()
    cur = conn.cursor()
    cur.execute(
        "UPDATE products SET attributes = %s WHERE id = %s",
        (psycopg2.extras.Json(attributes), product_id)
    )
    conn.commit()
    cur.close()
    conn.close()

    return jsonify({"message": "Product updated successfully!"}), 200

This modifies the attributes stored in JSONB format.

Step 6: Deleting a Product (Delete Operation)

Finally, implement a delete operation for a product:

@app.route('/product/<int:product_id>', methods=['DELETE'])
def delete_product(product_id):
    conn = connect_db()
    cur = conn.cursor()
    cur.execute("DELETE FROM products WHERE id = %s", (product_id,))
    conn.commit()
    cur.close()
    conn.close()

    return jsonify({"message": "Product deleted successfully!"}), 200

3. Best Practices for Using JSONB in PostgreSQL

Indexing JSONB Fields

Why Index?: JSONB fields should be indexed for efficient querying, especially when dealing with large datasets.
Use GIN Index: This type of index optimizes searches inside JSONB fields.

  CREATE INDEX idx_attributes ON products USING GIN (attributes);

Optimizing JSONB Queries

Avoid Deeply Nested Data: Avoid storing deeply nested objects, which can degrade performance.
Use JSONB Operators: Take advantage of operators like @> to query specific keys efficiently.

  SELECT * FROM products WHERE attributes @> '{"color": "red"}';

JSONB Anti-Patterns

Don’t Overuse JSONB: Use JSONB only for flexible attributes. For fixed schema data, prefer traditional SQL columns.
Don’t Store Large Blobs: Storing huge chunks of data in JSONB (e.g., files, images) can slow down queries.

Conclusion

With the steps provided, you can set up PostgreSQL with JSONB and implement full CRUD operations in Python. JSONB is highly flexible for storing dynamic data, and with the right optimizations, it can provide excellent performance.

To learn more, click on the link below to read further instructions on how to set up PostgreSQL for a seamless flow:

Learn More About PostgreSQL Setup

Thanks for reading...
Happy Coding!

Setting Up PostgreSQL for macOS Users: Step-by-Step Instructions

FOLASAYO SAMUEL OLAYEMI — Wed, 16 Oct 2024 01:57:00 +0000

If you're using macOS, the steps to install PostgreSQL and set up your environment are slightly different. Here's how to do it:

1. Installing PostgreSQL on macOS

There are multiple ways to install PostgreSQL on macOS, but one of the easiest is by using Homebrew, a package manager for macOS.

Step 1: Install Homebrew (If Not Installed)

If you don't have Homebrew installed, open your Terminal and run the following command:

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

This will install Homebrew on your system.

Step 2: Install PostgreSQL via Homebrew

Once Homebrew is installed, use the following command to install PostgreSQL:

brew install postgresql

Step 3: Start the PostgreSQL Service

After the installation, start the PostgreSQL server:

brew services start postgresql

This will ensure that the PostgreSQL server starts automatically when your macOS system boots up.

Step 4: Verify Installation

Check if PostgreSQL was installed correctly by running:

psql --version

This should display the installed PostgreSQL version.

Step 5: Access PostgreSQL

You can now access PostgreSQL using:

psql postgres

2. Creating a Database for Your Application

Once PostgreSQL is installed, you need to create a database for storing products with JSONB attributes.

Step 1: Create a New Database

Inside the PostgreSQL command-line interface (psql), create a new database:

CREATE DATABASE products_db;

Step 2: Create a User with Permissions

Next, create a user with admin privileges:

CREATE USER your_username WITH PASSWORD 'your_password';
GRANT ALL PRIVILEGES ON DATABASE products_db TO your_username;

Step 3: Create a Table to Store Products

Finally, create a table that will store products and their attributes in JSONB format:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    attributes JSONB
);

This table structure allows you to store dynamic attributes for each product, taking advantage of PostgreSQL's JSONB feature for flexibility and performance.

3. Starting and Stopping PostgreSQL

You can manually start or stop PostgreSQL on macOS using the following commands:

Start PostgreSQL:

  brew services start postgresql

Stop PostgreSQL:

  brew services stop postgresql

Restart PostgreSQL:

  brew services restart postgresql

4. PostgreSQL Uninstallation (Optional)

If you ever need to uninstall PostgreSQL from macOS, you can do so using Homebrew:

brew uninstall postgresql

Conclusion

By following these steps, macOS users can easily install and configure PostgreSQL. With Homebrew, the installation process is quick and seamless.

For a smooth experience, always ensure that PostgreSQL services are running and properly configured before proceeding.

Thanks for reading...
Happy Coding!