Learn Procedural SQL by Building a Chess Game in PostgreSQL

At this video we install Visual Studio Code and DBeaver in order to implement our Chess Project

At this video we make a brief introduction at PostgreSql (You can skip this video if you are familiar with Postgres)

At this video we make a brief introduction at Python (You can skip this video if you are familiar with Python)

In this lecture, we build the foundation of our chess game by creating the initial database structure in PostgreSQL.
You'll learn how to:

• Create and populate the pawns and board_table tables.

• Work with multidimensional arrays (text[9][9]) to simulate a chessboard.

• Implement utility functions like array_reverse().

• Use control structures in append_white_pawns() to dynamically generate white pieces.

• Populate the board with coordinates, black and white pieces using PL/pgSQL loops and updates.

By the end of this lecture, your PostgreSQL database will contain a fully initialized chessboard, ready for gameplay logic.

In this lecture, we write the first part of our Python code to connect to the PostgreSQL database and display the chess board.

You will learn how to:

• Set up a PostgreSQL connection using psycopg2.

• Create a Python class to manage the game.

• Fetch and display the board using a well-formatted console output.

• Use object-oriented programming (OOP) to organize the game logic.

• Reset and repopulate the chess board from SQL using Python.

This is the first building block in connecting Python and PostgreSQL for interactive gameplay.

In this lecture, we begin building the logic for how each chess piece moves, starting with the pawn.

You will learn how to:

• Create the pawn() function to define legal movement for white and black pawns.

• Handle different move types: forward moves, first double-step moves, and diagonal captures.

• Access and evaluate the board state using SQL array accessors.

• Create the check_if_valid_move() wrapper function to route moves based on piece type.

These functions form the core rules engine of our chess game, and we’ll expand them in later lectures for the rest of the pieces.

In this lecture, we implement the core logic that powers gameplay from the Python side.

You will learn how to:

• Create the move_piece() method to communicate with PostgreSQL and execute piece movement.

• Manage player turns based on the returned result from the SQL backend.

• Handle invalid moves and ensure turn rollback for incorrect actions.

• Build the start_game() method to begin a console-based interactive game loop using player input.

• Use Python to fetch, display, and update board state dynamically.

In this lecture, we implement the move_piece() function in PL/pgSQL — the core SQL logic responsible for executing a single chess move.

You will learn how to:

• Use input coordinates and player turn to validate a move.

• Retrieve and evaluate piece type and color from the board array.

• Check if a move is valid using the check_if_valid_move() function.

• Update the board state and handle piece captures.

• Return human-readable status messages to the Python side.

In this lecture, we implement the movement logic for the rook in PL/pgSQL.

You will learn how to:

• Write the rook() function to validate legal rook movements.

• Handle both vertical and horizontal paths on a 2D board array.

• Check for obstacles along the rook’s path using FOR loops.

• Ensure that the destination square is either empty or occupied by an enemy piece.

• Update the check_if_valid_move() function to support the rook logic.

This function forms part of the core move validation engine that we will build out for all chess pieces.

In this lecture, we implement the bishop's movement logic in PL/pgSQL by creating the bishop() function.

You will learn how to:

• Detect and validate legal diagonal movement using absolute differences.

• Use WHILE loops to check for obstacles along each diagonal path.

• Ensure the destination square is empty or occupied by an opponent.

• Integrate the bishop() function into the existing check_if_valid_move() dispatcher.

This lecture builds your understanding of pathfinding and prepares you for implementing more complex pieces like the queen.

In this lecture, we implement the movement logic for the knight piece in PL/pgSQL.

You will learn how to:

• Define the knight’s unique L-shaped movement using absolute differences in coordinates.

• Implement the knight() function to validate legal knight moves.

• Ensure the destination square is either empty or occupied by an enemy piece.

• Extend the central dispatcher function check_if_valid_move() to support knight logic.

In this lecture, we implement the queen’s movement logic by combining the logic of the rook and bishop.

You will learn how to:

• Create the queen() function by reusing existing logic from the rook() and bishop() functions.

• Understand how code reuse can simplify chess piece behavior implementation.

• Integrate the queen logic into the central check_if_valid_move() function.

• Enable diagonal and straight-line movement checks for the queen piece.

In this lecture, we implement the king() function in PL/pgSQL to define how the king piece moves on the board.

You will learn how to:

• Handle legal king movements in all directions (diagonal, horizontal, vertical) using coordinate differences.

• Write conditionals that prevent illegal jumps and validate correct capture logic.

• Ensure the king can only move one square in any direction.

• Update the central check_if_valid_move() function to support the king’s movement rules.

This function completes the movement logic for all basic chess pieces and forms the foundation for check detection and king safety logic in later lectures.

In this lecture, we implement the find_enemy_king() function to locate the position of the enemy king on the board.

You will learn how to:

• Use nested loops to iterate through a 2D array representing the chessboard.

• Extract and compare piece type and color using split_part() logic.

• Dynamically return the enemy king's coordinates as an integer array.

• Prepare the foundation for later features such as check detection and game state evaluation.

This utility function is critical for future logic that involves checking whether the opponent’s king is under threat.

In this lecture, we implement the check_if_rua() function, which determines if the current move places the opponent’s king under threat.

You will learn how to:

• Use the find_enemy_king() function to retrieve the enemy king's coordinates.

• Call check_if_valid_move() to check if the piece can legally reach the king's position.

• Apply conditional logic based on the active player’s color.

• Return a special value (e.g., 2) if the enemy king is under threat.

This function introduces a key part of chess gameplay — detecting a RUA (check) — and is crucial for building logic around game state awareness.

In this lecture, we implement the check_if_rua_ally() function to ensure that a player’s move does not leave their own king vulnerable to RUA (check).

You will learn how to:

• Loop through all enemy pieces and simulate potential RUA threats.

• Use the check_if_rua() function to determine if the move would expose your king.

• Implement move validation that considers both offensive and defensive consequences.

• Return a special value (-3) when a move would compromise your own king’s safety.

This function enforces the rule that players cannot make a move that places or leaves their own king in check — a critical part of realistic chess logic.

In this lecture, we introduce two major enhancements to our PostgreSQL chess engine:

1. The resurrect Procedure
   This procedure allows us to bring a previously captured piece back onto the board. It:

   • Retrieves the earliest instance of a specified captured piece.

   • Places it on a specified coordinate.

   • Updates the board_table accordingly and removes the resurrected piece from the dead_pieces table.

2. Enhanced move_piece Function
   The updated move_piece() function now:

   • Captures and stores any opponent piece it lands on.

   • Validates movement legality and board boundaries.

   • Prevents moves that would result in self-RUA (leaving your king in check).

   • Triggers RUA warnings after a move.

   • Checks for edge-promotion scenarios.

By the end of this lecture, students will be able to implement move validation logic with full support for piece capture, undo logic, self-check detection, and even resurrection for advanced chess mechanics like testing or custom game rules.

In this final lecture, we complete the Python-side game logic by enhancing the move_piece() and start_game() methods for full gameplay support.

You will learn how to:

• Handle resurrection prompts when a piece reaches the promotion edge.

• Retrieve and display available captured pieces from the dead_pieces table.

• Validate resurrection inputs and restore pieces using the resurrect procedure.

• Improve feedback for RUA (check), invalid moves, and user interactions.

• Finalize the turn-based game loop with clean and user-friendly controls.

By the end of this lecture, you will have a fully playable command-line chess game powered by PostgreSQL and Python — including support for capturing, resurrecting, and RUA detection.