Dive into the World of PostgreSQL: Beginners Guide with Docker

PostgreSQL is an open-source relational database management system (RDBMS) known for its robustness, scalability, and extensibility. It is favored by developers and enterprises for building scalable and high-performance applications.

There are many ways to create and manage a PostgreSQL database such as

• Installing PostgreSQL directly on your operating system and using tools like psql (the PostgreSQL command-line interface) or graphical user interfaces (GUIs) like pgAdmin to interact with and manage your PostgreSQL database.
• Using Package Managers like apt (Ubuntu or Debian - based systems), Homebrew (Mac), etc.
• Using Cloud-based Services like Amazon RDS for PostgreSQL, Google Cloud SQL for PostgreSQL, and Azure Database for PostgreSQL.
• Using Docker.

This article focuses on creating and managing PostgreSQL databases using Docker.

Docker is a containerization platform that allows you to package apps along with their dependencies in lightweight containers. Using PostgreSQL with Docker provides several benefits, such as easy setup, portability, and isolation.

AGENDA:

1. Create a Docker Container with a PostgreSQL image.
2. Connect to the PostgreSQL server and create a DB.
3. A simple TODO app with Flask to demonstrate the working.

Create a Docker Container with PostgreSQL image:

Step 1: Install Docker.

For Ubuntu users, use the following commands to install docker.

sudo apt install docker.io

Type the following to verify the installation

sudo docker

For Windows Users, visit the docker’s official documentation and follow the steps to install docker in your system.

Install Docker Desktop on Windows

Step 2: Pull the PostgreSQL image

Once Docker is installed, the next step is to pull the PostgreSQL image from Docker Hub. You can do this by running the following command in the terminal:

sudo docker pull postgres

sudo docker images

Step 3: Create a docker container.

sudo docker run --name <container-name> -e POSTGRES_PASSWORD=<password> -d -p <port>:<port> postgres

container-name: any name for your Postgres container.

password: strong password for the PostgreSQL superuser.

For example,

sudo docker run --name pg11 -e POSTGRES_PASSWORD=test123 -d -p 5432:5432 postgres

• sudo: This command is used to run the subsequent command with administrative privileges.
• docker run: This command starts a new Docker container.
• --name pg11: It assigns the name "pg11" to the container.
• -e POSTGRES_PASSWORD=test123: This sets the environment variable POSTGRES_PASSWORD to "test", specifying the password for the PostgreSQL superuser.
• -d: It runs the container in detached mode, which means it runs in the background.
• -p 5432:5432: This maps the container's port 5432 (default PostgreSQL port) to the host's port 5432, allowing connections from the host machine to the PostgreSQL server running inside the container.
• postgres: This specifies the Docker image that we have pulled.

This command will create a new Docker container with the PostgreSQL image and start the PostgreSQL server inside it.

Verify if the container is created using the command,

docker ps

Connect to the PostgreSQL Server:

Start the docker container.

sudo docker start <container-name>
sudo docker exec -it <container-name> bash
psql -h <host-ip> -p <port-no> -U <user-name>

Replace <container-name> with the name you specified in the above step.

Example:

sudo docker start pg11
sudo docker exec -it pg11 bash
psql -h 0.0.0.0 -p 5432 -U postgres

• docker start: This command starts a stopped container or a newly created one.
• docker exec: This command runs a command within a running container.
• -it: It attaches an interactive terminal to the container.
• pg11: It specifies the container to execute the command in.
• bash: This is the command to be executed inside the container, which opens a Bash shell.
• psql: This command starts the PostgreSQL command-line interface.
• -h 0.0.0.0: It specifies the host IP address to connect to. Here, it is set to 0.0.0.0, which means all available network interfaces.
• -p 5432: This specifies the port number (5432) to connect to.
• -U postgres: It specifies the username (postgres) to connect with.

You should now be connected to the PostgreSQL server through the PostgreSQL command-line interface.

Once connected to the PostgreSQL server, you can execute SQL commands to create databases, and tables, and perform other operations as needed.
For example,

create database saru;

This command creates a database with the name “saru”

\c saru;

This connects to the database “Saru”

create table Todos(id int primary key , title varchar(234));

The above SQL command creates a table Todo with fields id and title.

Use \d to list all the tables in the database “saru”.

Use a select statement to query all the rows in the table

select * from <table-name>

A simple TODO app with Flask:

We are now going to make a to-do app that makes use of the Postgres DB that we have created.

Make sure you run your Postgres container in a separate terminal.

Let's create a simple Flask web interface where users can submit a to-do item through a form. The submitted items are stored in a PostgreSQL database using SQLAlchemy. Flask-SQLAlchemy is a Flask extension that provides integration between Flask and SQLAlchemy, a powerful and popular Object-Relational Mapping (ORM) library for Python.

To connect a Flask app with DB,

the SQLALCHEMY_DATABASE_URI configuration variable is set to specify the connection details for a PostgreSQL database or any other.

app.config ['SQLALCHEMY_DATABASE_URI'] = 'postgresql://<username>:<password>@<host>:5432/<db-name>'

• <username>: Replace this with the username for your PostgreSQL database.
• <password>: Replace this with the password for the specified username.
• <host>: Replace this with the hostname or IP address where your PostgreSQL server is running.
• <db-name>: Replace this with the name of the PostgreSQL database you want to connect to.

Steps:

1. Import necessary modules.
2. Instantiate Flask class and set SQLALCHEMY_DATABASE_URI to configure the PostgreSQL database connection.
3. Create a class representing the database schema for the todo table.
4. Set up a route for rendering the HTML template and processing POST requests
5. Define a route to display and process the post request.
6. Don't forget to create a html file to display and submit the form.

from flask import Flask, render_template,  request
from flask_sqlalchemy import SQLAlchemy


app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://postgres:test123@0.0.0.0:5432/saru'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)


class Todo(db.Model):
    __tablename__ = 'todos'
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(80), nullable=False)

    def __repr__(self):
        return f'<Todo {self.id}>'
    def __init__(self, id, title):
        self.id = id
        self.title = title
    

@app.route('/',methods = ['GET','POST'])
def index():
   
    objs = db.session.query(Todo).all() 

    if request.method == "POST":
        now = request.form.get("todo")

        now_id = db.session.query(Todo.id).all()
        todo_obj =  Todo(len(now_id)+1,now)
        
        db.session.add(todo_obj)
        db.session.commit()
        
        objs = db.session.query(Todo).all() 
        
 
    return render_template('index.html', l = objs)

if __name__ == "__main__":
    
    app.run(debug = True, port = 5003)

Methods and concepts related to the SQLAlchemy db.session object:

1. db.session.add(<obj>): This method is used to add an object to the session. It stages the object to be inserted into the database during the next commit operation.
2. db.session.commit(): This method commits all pending changes in the session to the database. It performs inserts, updates, and deletes based on the staged objects in the session.
3. db.session.delete(<obj>): This method marks an object for deletion in the session. The object will be deleted from the database during the next commit operation.
4. db.session.query(<Class>): This method is used to construct a new query object for a given class. It allows you to perform database queries and retrieve objects of that class.
5. db.session.query(<Class>).filter(condition): This method is used to apply a filter condition to a query. It narrows down the result set by specifying criteria that the objects must meet. The condition parameter is an expression that defines the filter condition.
6. db.session.query(<Class>).all(): This method executes the query and retrieves all objects of the specified class from the database as a list. It returns all rows from the table associated with the class.

db.session the object provides a way to interact with the database within an SQLAlchemy session. You can add objects to the session using add(), commit changes with commit(), delete objects withdelete(), construct queries using query(), apply filters with filter(), and retrieve query results using methods like all(). These methods form the core functionality for interacting with the database using SQLAlchemy in a Flask application.

index.html file:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>MY TODOS</title>
</head>
<body>
    <form action = "/", method = "post">
        Your todo:
        <input type="text" name="todo" id = "todo">
        <button type = "submit">SUBMIT</button>
    </form>
    
    <ul>
        {% for i in l %}
        <li> {{i.title}}</li>
        {% endfor %}
    </ul>
    
</body>
</html>

1. Inside the <body> section, there is an <form> element that allows users to submit a to-do. It has an action attribute set to /, which indicates that the form will be submitted to the root URL of the application. The method attribute is set to postindicate that the form will use the POST HTTP method to send data.
2. Inside the form, there is a text input field named “todo” with an associated label “Your todo:”. Users can enter the to-do text in this field.
3. There is a submit button labeled “SUBMIT” that users can click to submit the form
4. {% for i in l %} : This is a template expression in Jinja2 (a template engine used by Flask) that iterates over a list of todos (l in the Flask view function) and assigns each todo object to the variable i.
5. Within the loop, each todo’s title is displayed as a list item using {{ i.title }}, where i represents the current to-do object.

Note:

1. To exit from psql , Type \q or exit . You need to type exit two times to exit from psql and Bash.

2. One can also use psycopg2 and sqlalchemy to connect with Postgres db using Python.

3. Make sure you commit everything after making changes for them to reflect in your database db.session.commit().

GitHub repository for this:

https://github.com/SarumathyPrabakaran/todo-app

Hope it helps.