Todolist in postgres

How to create a todo list using postgres queries

create a database with CREATE DATABASE
create a table with CREATE TABLE
insert sample todos with INSERT INTO
read todos with SELECT
read filtered todos with SELECT...WHERE
update todo by id with UPDATE...WHERE
delete todo by id with DELETE...WHERE

Bash

patricktran=# create database todo_app;
CREATE DATABASE


patricktran=# \c todo_app
You are now connected to database "todo_app" as user "patricktran".
todo_app=# CREATE TABLE todos (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  completed BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT NOW(),
  completed_at TIMESTAMP
);
CREATE TABLE


todo_app=# INSERT INTO todos (title) VALUES
  ('Buy groceries'),
  ('Walk the dog'),
  ('Write Postgres tutorial');
INSERT 0 3


todo_app=# SELECT * FROM todos;
 id |          title          | completed |         created_at         | completed_at
----+-------------------------+-----------+----------------------------+--------------
  1 | Buy groceries           | f         | 2025-06-02 00:44:43.059965 |
  2 | Walk the dog            | f         | 2025-06-02 00:44:43.059965 |
  3 | Write Postgres tutorial | f         | 2025-06-02 00:44:43.059965 |
(3 rows)


todo_app=# SELECT * FROM todos WHERE completed = FALSE;
 id |          title          | completed |         created_at         | completed_at
----+-------------------------+-----------+----------------------------+--------------
  1 | Buy groceries           | f         | 2025-06-02 00:44:43.059965 |
  2 | Walk the dog            | f         | 2025-06-02 00:44:43.059965 |
  3 | Write Postgres tutorial | f         | 2025-06-02 00:44:43.059965 |
(3 rows)


todo_app=# UPDATE todos
SET completed = TRUE, completed_at = NOW()
WHERE id = 2;
UPDATE 1


todo_app=# SELECT * FROM todos;
 id |          title          | completed |         created_at         |        completed_at
----+-------------------------+-----------+----------------------------+----------------------------
  1 | Buy groceries           | f         | 2025-06-02 00:44:43.059965 |
  3 | Write Postgres tutorial | f         | 2025-06-02 00:44:43.059965 |
  2 | Walk the dog            | t         | 2025-06-02 00:44:43.059965 | 2025-06-02 00:44:54.555347
(3 rows)


todo_app=# SELECT * FROM todos WHERE completed = FALSE;
 id |          title          | completed |         created_at         | completed_at
----+-------------------------+-----------+----------------------------+--------------
  1 | Buy groceries           | f         | 2025-06-02 00:44:43.059965 |
  3 | Write Postgres tutorial | f         | 2025-06-02 00:44:43.059965 |
(2 rows)


todo_app=# DELETE FROM todos WHERE id = 1;
DELETE 1
todo_app=# SELECT * FROM todos WHERE completed = FALSE;
 id |          title          | completed |         created_at         | completed_at
----+-------------------------+-----------+----------------------------+--------------
  3 | Write Postgres tutorial | f         | 2025-06-02 00:44:43.059965 |
(1 row)


todo_app=# SELECT * FROM todos;
 id |          title          | completed |         created_at         |        completed_at
----+-------------------------+-----------+----------------------------+----------------------------
  3 | Write Postgres tutorial | f         | 2025-06-02 00:44:43.059965 |
  2 | Walk the dog            | t         | 2025-06-02 00:44:43.059965 | 2025-06-02 00:44:54.555347

You can use psql directly or create an interactive command line tool

Bash script that runs psql commands

Save this to a file run_todo.sh
Allow the user to execute the script $ chmod +x run_todo.sh
Run the scripte $ ./run_todo.sh

Bash

#!/bin/bash

# Ensure the database exists
createdb todo_app 2>/dev/null

# Create the todos table if it doesn't exist
psql todo_app <<EOF
CREATE TABLE IF NOT EXISTS todos (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  completed BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT NOW(),
  completed_at TIMESTAMP
);
EOF

while true; do
  echo
  echo "1. View todos"
  echo "2. Add todo"
  echo "3. Toggle completed"
  echo "4. Delete todo"
  echo "5. Exit"
  read -p "Choose an option: " option

  case $option in
    1)
      psql todo_app -c "SELECT id, title, completed, created_at, completed_at FROM todos ORDER BY id;"
      ;;
    2)
      read -p "Enter title: " title
      psql todo_app -c "INSERT INTO todos (title) VALUES ('$title');"
      ;;
    3)
      read -p "Enter ID to toggle: " id
      psql todo_app <<EOF
DO \$\$
BEGIN
  IF EXISTS (SELECT 1 FROM todos WHERE id = $id AND completed = FALSE) THEN
    UPDATE todos SET completed = TRUE, completed_at = NOW() WHERE id = $id;
  ELSIF EXISTS (SELECT 1 FROM todos WHERE id = $id AND completed = TRUE) THEN
    UPDATE todos SET completed = FALSE, completed_at = NULL WHERE id = $id;
  ELSE
    RAISE NOTICE 'Todo with ID % not found.', $id;
  END IF;
END
\$\$;
EOF
      ;;
    4)
      read -p "Enter ID to delete: " id
      psql todo_app -c "DELETE FROM todos WHERE id = $id;"
      ;;
    5)
      echo "Goodbye!"
      break
      ;;
    *)
      echo "Invalid option"
      ;;
  esac
done

python script that runs psql commands

Copy this script to run_todo.py
Run with python todo_cli.py

Bash

import subprocess

def run_sql(query):
    subprocess.run(["psql", "todo_app", "-c", query])

def run_sql_block(sql_block):
    subprocess.run(["psql", "todo_app"], input=sql_block.encode())

def create_db():
    subprocess.run(["createdb", "todo_app"], stderr=subprocess.DEVNULL)

def create_table():
    run_sql("""
    CREATE TABLE IF NOT EXISTS todos (
        id SERIAL PRIMARY KEY,
        title TEXT NOT NULL,
        completed BOOLEAN DEFAULT FALSE,
        created_at TIMESTAMP DEFAULT NOW(),
        completed_at TIMESTAMP
    );
    """)

def view_todos():
    run_sql("SELECT id, title, completed, created_at, completed_at FROM todos ORDER BY id;")

def add_todo():
    title = input("Enter title: ")
    run_sql(f"INSERT INTO todos (title) VALUES ('{title}');")

def toggle_todo():
    id_ = input("Enter ID to toggle: ")
    sql_block = f"""
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM todos WHERE id = {id_} AND completed = FALSE) THEN
    UPDATE todos SET completed = TRUE, completed_at = NOW() WHERE id = {id_};
  ELSIF EXISTS (SELECT 1 FROM todos WHERE id = {id_} AND completed = TRUE) THEN
    UPDATE todos SET completed = FALSE, completed_at = NULL WHERE id = {id_};
  ELSE
    RAISE NOTICE 'Todo with ID % not found.', {id_};
  END IF;
END
$$;
"""
    run_sql_block(sql_block)

def delete_todo():
    id_ = input("Enter ID to delete: ")
    run_sql(f"DELETE FROM todos WHERE id = {id_};")

def main():
    create_db()
    create_table()

    while True:
        print("\n1. View todos\n2. Add todo\n3. Toggle completed\n4. Delete todo\n5. Exit")
        choice = input("Choose an option: ")

        if choice == "1":
            view_todos()
        elif choice == "2":
            add_todo()
        elif choice == "3":
            toggle_todo()
        elif choice == "4":
            delete_todo()
        elif choice == "5":
            print("Goodbye!")
            break
        else:
            print("Invalid option.")

if __name__ == "__main__":
    main()