Database / SQL

Supabase Database

This documentation details the architecture and operation of the data persistence engine built to interact with Supabase, using a hybrid approach between the REST API (via the official client) and direct PostgreSQL access (via psycopg2).

On this page

    #Overview

    The code implements a database abstraction layer split into two main responsibilities: Connection Management and the Query Engine.

    Unlike approaches that rely only on high-level libraries, this project focuses on performance and robust transactional control, allowing pure SQL executions, JSONB handling and bulk operations to be performed safely.

    #Execution Flow

    The flow of a common operation follows these steps:

    1. Initialization: SupabaseConnection loads the environment variables and validates the credentials.
    2. Lazy Loading: The connection to the PostgreSQL database is only opened at the moment the first query is requested.
    3. Dependency Injection: SupabaseQueryEngineer receives the connection method.
    4. Execution & Context: The query engine opens a cursor, runs the SQL and, on success, performs the commit.
    5. Error Handling: If something fails, an automatic rollback is triggered to preserve data integrity.

    #Methods Table

    ClassMethodBrief Description
    SupabaseConnectionget_connection()Returns an active connection, reconnecting if necessary.
    SupabaseConnectionclose()Closes the physical connection to the database.
    SupabaseQueryEngineerselect()Runs read queries and returns a list of dictionaries.
    SupabaseQueryEngineerexecute()Runs write commands (Insert, Update, Delete).
    SupabaseQueryEngineerexecute_many()Runs the same SQL command for a list of multiple parameters.

    #Architecture and Insights

    • Resilience (Auto-reconnect): The get_connection method sends a "ping" (SELECT 1) to the database. This avoids common "broken pipe" errors in serverless environments or when the connection stays idle for too long.
    • Typed Results: The use of RealDictCursor turns database rows into native Python dictionaries, making them easier to use in APIs and data handling.
    • Security (SQL Injection): The system uses parameterization (%s), delegating data sanitization to the driver, which prevents SQL injection attacks.

    #Detailed Class Documentation

    #SupabaseConnection Class

    Description

    Manages the lifecycle of the connection to the Supabase ecosystem. It validates that the environment is configured correctly and provides an interface to obtain stable PostgreSQL connections over the TCP protocol.

    Arguments

    • Has no initialization arguments (reads directly from .env).

    #Methods

    1. get_connection

    • Description: Provides a psycopg2 connection instance. Implements the Lazy Initialization pattern.
    • Arguments: None.
    • Returns: psycopg2.extensions.connection
    • Raises: Exception if the credentials are incorrect or the database is unreachable.
    • Examples: conn = connection.get_connection()

    #SupabaseQueryEngineer Class

    Description

    The "brain" of the database operations. This class isolates the complexity of SQL, managing cursors and transactions (Commit/Rollback) transparently for the developer.

    Arguments

    • get_connection (Callable): A function or method that returns an active connection.

    #Methods

    1. select

    • Description: Runs data search queries.
    • Arguments:
      • query (str): SQL SELECT command.
      • params (tuple, optional): Values to replace the %s placeholders.
    • Returns: List[Dict[str, Any]] - A list where each item is a table row.
    • Raises: Exception in case of an SQL syntax error or connection failure.
    • Examples: engine.select("SELECT * FROM users WHERE name = %s", ("Enzo",))

    2. execute

    • Description: Runs commands that change the state of the database (DDL or DML).
    • Arguments:
      • query (str): SQL command (INSERT, UPDATE, DELETE, CREATE, etc).
      • params (tuple, optional): Query parameters.
    • Returns: None
    • Raises: Exception with automatic rollback in case of failure.
    • Examples: engine.execute("UPDATE users SET name = %s", ("Novo Nome",))

    3. execute_many

    • Description: Optimizes the insertion or update of large volumes of data.
    • Arguments:
      • query (str): Parameterized SQL command.
      • params_list (List[tuple]): List containing the parameter tuples for each repetition.
    • Returns: None
    • Raises: Exception if any of the operations fails (cancels the entire batch).
    • Examples: engine.execute_many("INSERT INTO logs (msg) VALUES (%s)", [("Erro 1",), ("Erro 2",)])

    Source: src/database/relational_db/supabase_database.py

    Esc
    ↑↓navigate Enteropen