Giving Memory to the Engine

DAO
CRUD
SQL
Data Mapping
Mocking
Lab 3 - Data Access Object
Author

Ludovic Deneuville

Before you start

This tutorial combines explanations and code phases. The explanations should not take precedence over those of your teacher.

NoteConcepts covered
  • SQL reminder
  • The DAO Pattern
  • Transfer data between the app and the database
  • Test DAO methods using data from another schema

Work on a branch

🚧

Each member of the team works on its own branch:

    • or stick with the one from TP2 if you’ve finished everything

1 The Mission

In the previous lab, our gaming engine became modular and powerful. We can play different games (Coin Flip, Dice) and calculate scores using different strategies.

But there is a huge problem: As soon as the application stops, everything is forgotten. Every victory, every loss, and every Elo change disappears into the void.

Our mission for this TP is to give our engine a long-term memory by implementing a persistence layer using a Relational Database (PostgreSQL).

2 Warmup: SQL Queries

Before we dive into Python, we need to prepare our database. We will add a game table to store the history of every match played.

TipTips and reminder
  • Tables are in the project schema
  • Write Beautiful SQL:
SELECT p.*
  FROM project.player p
 WHERE 1=1
   AND 2=2;

2.1 Create and pop table

We want to save the games, so let’s start by creating a table.

CREATE TABLE project.game (
    id_game      SERIAL PRIMARY KEY,
    id_player1   INTEGER REFERENCES player(id_player),
    id_player2   INTEGER REFERENCES player(id_player),
    game_mode    VARCHAR(20),
    id_winner    INTEGER REFERENCES player(id_player),
    detail       VARCHAR(100),
    timestamp    TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

INSERT INTO project.game(id_player1, id_player2, game_mode, id_winner, detail) VALUES
(1, 2, 'coinflip', 1, 'Gilbert chose heads, result was heads'),
(3, 4, 'dice',     3, 'Maurice rolled 5, Batricia rolled 2'),
(3, 4, 'dice',     null, 'Maurice rolled 4, Batricia rolled 4'),
(4, 3, 'dice',     4, 'Batricia rolled 2, Maurice rolled 1'),
(3, 4, 'coinflip', 4, 'Maurice chose heads, result was tails');

Great, now you have a table where you can store your games.

TipThe right way
  • Always keep a record of your database changes
  • Save them in SQL files (data folder)
  • These files will be versioned by Git, so any loss can be recovered
  • You can automate this by using, for example, the reset_database.py script or by creating a button in your UI
    • you can ask the AI agent to analyze and explain it
Note

In this script, the schema is managed via an environment variable, so there’s no need to include it in the SQL scripts.

CREATE TABLE project.game ➡️ CREATE TABLE game

2.2 Database questions

Since all of you have reviewed your relational database course this summer 📖

    • Any idea if you want to do it anyway?
    • don’t fix it

2.3 Some queries

To finish these reviews, enter the following queries:

    • including a boolean if she win

3 Architecture: The DAO Pattern

Now we’re going to map the business objects to the database tables.

In Lab2, we created Business Objects (Player, Game). These objects represent the logic of our game.

Should we add a method game.save_to_db() inside the Game class?

The answer is a loud NO! ❌

If we do that, our Game class will suddenly need to know about database connections, SQL queries, and connection strings. This violates the principle of High Cohesion: a class should do one thing. A Game should represent a match, not manage a database connection.

Instead, we use the DAO (Data Access Object) Pattern.

3.1 The Separation of Concerns

We separate the What (Business Logic) from the How (Persistence).

sequenceDiagram
    participant GS as GameService
    participant GD as GameDao
    participant DB as DBConnection
    participant SQL as PostgreSQL

    GS->>GD: create(game_object)
    GD->>DB: get_connection()
    DB-->>GD: connection
    GD->>SQL: INSERT INTO game (...)
    SQL-->>GD: success (id)
    GD-->>GS: True

3.2 Connection management and Singleton pattern

To connect to the database, we’ll use the Python library psycopg2. This library will establish the connection with the database, send our queries, and return the results.

However, we need to be careful with connection management, as we could quickly open hundreds of connections and degrade our application’s performance.

This is the job of the DBConnection class. Since it’s a singleton, there will be only one instance of this class in our entire application, and since it’s the one that connects to the database, we ensure the connection’s uniqueness.

Tip

This class is a purely technical solution, so feel free to reuse it for your project. It introduces an advanced concept in OOP: metaclasses.

A metaclass allows you to modify a class’s behavior at a deep level (for example, altering how objects are constructed by Python). Unless you have a specific passion for computer science, don’t spend time on this topic.

3.3 DAO and CRUD

If you look closely, our DAO’s methods resemble those of CRUD. This is normal, as these methods will store the SQL code.

The basic methods are generally:

  • find_all(): which will return the entire table
  • find_by_id(): which returns a record based on its id
  • create(): which creates a new record
  • delete(): which deletes a record
  • update(): which updates a record

These 5 methods are enough to communicate with your database. You can perform the rest of the processing in your Service classes (recommended). However, nothing prevents you from creating more complex methods (e.g., find_by_type_and_level_order_by_name_desc()).

Here is the general operation of one of the DAO methods:

a_dao_class.py
from dao.db_connection import DBConnection

class ADaoClass(metaclass=Singleton):

    def a_dao_method(self, optional_param):

        # Step 1: We get the connection using the DBConnection class.
        with DBConnection().connection as connection:
        
            # Step 2: From the connection, we create a cursor for the query.
            with connection.cursor() as cursor: 
            
                # Step 3: We execute our SQL query.
                cursor.execute("SQL query using an optional parameter")

                # Step 4: We store the query result.
                res = cursor.fetchall()

        if res:
            # Step 5: We format the results into the desired shape (object, list...).

        return something

The cursor object holds a pointer to the results of your query. This result isn’t yet on your machine; it’s still stored by the database. You have three methods to retrieve the results:

  • cursor.fetchone(): Returns a single record as a dictionary.
    • If you call fetchone() again on the same cursor, you’ll get the next row.
  • cursor.fetchall(): Returns all results as a list of dictionaries.
    • The dictionaries are the rows from the retrieved table.
    • The dictionary keys are the retrieved columns.
    • This method works well when you want all results at once and there are only a few of them. When you have millions of records, this will cause problems because:
      • The data transfer over the internet will take time and block your application.
      • Your application may be unable to handle such a large amount of data.
  • cursor.fetchmany(size): Returns the requested number of records as a list of dictionaries. This lets you control the volume of data you’re processing. If you call fetchmany(size) again on your cursor, you’ll retrieve the next set of rows (a pagination system).

Examples of returned data (cursor query: SELECT * FROM tp.attack;):

A single dictionary

{"id_attack": 2, "id_attack_type": 3, "power": 20, "accuracy": 100, "element": "Grass", "attack_name": "Absorb"}

A list of dictionaries

[
    {"id_attack": 2, "id_attack_type": 3, "power": 20, "accuracy": 100, "element": "Grass", "attack_name": "Absorb"},
    {"id_attack": 3, "id_attack_type": 2, "power": 40, "accuracy": 100, "element": "Rock", "attack_name": "Accelerock"},
    {"id_attack": 4, "id_attack_type": 3, "power": 40, "accuracy": 100, "element": "Poison", "attack_name": "Acid"},
    ...
]

4 The GameDao

Tip

Feel free to use the PlayerDao class as a reference when writing the GameDao methods.

The advantage of DAO classes is that once you understand the concept, the process is always the same.

Your main task is to create the GameDao class. This class will be the only one allowed to talk to the game table.

    • Inserts a new game record
    • After the insertion, update the game.id_game with the ID returned by the database
    • Retrieves a specific game and converts the database row into a Game object
ImportantThe Conversion Step

When you fetch data using cursor.fetchone(), you get a dictionary.

You must not return this dictionary as it is!

You have to use this dictionary to instantiate a Game object.

Below an example of how to convert a row to an object:

row = cursor.fetchone()
if row:
    # Step 1: Convert IDs into real Player objects using PlayerDao
    p1 = PlayerDao().find_by_id(row["id_player1"])
    p2 = PlayerDao().find_by_id(row["id_player2"])
    winner = PlayerDao().find_by_id(row["id_winner"])

    # Step 2: Build and return the Game object
    return Game(
        id_game=row["id_game"],
        game_mode=row["game_mode"],
        player1=p1,
        player2=p2,
        winner=winner,
        detail=row["detail"]
    )
  • Raw data
    • row match with an horizontal line from that table game
    • row["id_game"]: a cell in the table
  • Object
    • Game(id_game= ..., game_mode = ...): call the constructor to create one
Caution

Using PlayerDao().find_by_id() inside a loop to fetch players for every game creates many small database calls. This is slow!

In a real production app, you would solve this using a SQL JOIN to fetch the game and its players in one single query.

For the next method, you won’t be getting just one recording, but several:

    • Returns all games involving a specific player

5 Use it in the service

Now that we have a way to save games, let’s use it!

The service creates a Game object and calls the appropriate method to persist the result.

6 Test a DAO method

Bonus

Testing a DAO is different because you are touching a real database. This creates two major problems.

6.1 The Naive Approach

“I want to test the delete() method. I’ll create a game, then delete it. Easy!”

Imagine you run your test:

  • First run: You create a game (ID: 5), you delete it. Success! ✅
  • Second run: You run the exact same test. But wait… the test fails! ❌

Why? Because in your first run, the game was deleted, but your database is still “dirty”. If your test doesn’t start from a perfectly clean state, your results will depend on what happened in the past. This is a nightmare to debug.

TipIsolation & Reset

Never use your real database for tests. Use a separate schema (a “sandbox”) and always reset it before every test.

This way, every test starts with a completely empty database, and you are sure that a failure is actually a bug in your code, not a leftover from a previous test.

6.2 Test using the real Data

“I’ll just run my tests directly on my local database where I have all my useful data.”

Your tests are designed to create, modify, and delete data. If you run them on your real database, you will eventually delete something important. You are literally destroying your own workspace just to check if your code works.

NoteThe Sandbox

By using a dedicated test schema, you can delete everything, crash everything, and mess everything up without any risk. If you break the test database, it doesn’t matter—you just reset it.

6.3 Hands on

7 Conclusion

Congratulations! You have successfully transitioned the engine from a “volatile” state to a “persistent” state.

By implementing the DAO pattern, you have ensured that the business logic remains pure and decoupled from the technical details of storage. Even if we switched from PostgreSQL to MongoDB tomorrow, our Game and GameService classes wouldn’t need a single line of code changed!

End of the Lab

Important

When you have finished coding, don’t forget to:

    • If your service is terminated, all unpushed code is lost…
    • to free up reserved resources