Build a Rock, Paper, Scissors Game on PostgreSQL With Database Programming

Modern databases, such as PostgreSQL, have become robust environments for application development, yet many of their advanced features—like functions, triggers, and materialized views—are often underutilized. This article explores the revival of database programming through a practical example: creating a Rock, Paper, Scissors game that operates directly within a PostgreSQL instance.

Why Are Functions, Triggers, and Views Hardly Used?

Despite their potential, many software engineering teams shy away from utilizing the full range of database capabilities. The prevailing belief is that business logic should reside in the application layer, relegating databases to mere data storage. This perspective has emerged from a desire to maintain a clear separation of concerns, where the application handles business logic and the database merely serves queries.

However, this approach can lead to inefficiencies, such as increased latency due to multiple network calls and the complexity of ensuring data consistency across distributed systems. By leveraging database features like triggers and functions, developers can reduce round trips to the database, thereby enhancing performance and simplifying codebases.

The Hidden Cost of Pushing Everything Up to the Application

While treating databases solely as repositories has its advantages, it also incurs hidden costs:

• Latency and Resource Utilization: Each request from the application to the database adds overhead, including serialization, network calls, and data parsing. By pushing logic into the database, these costs can be minimized.
• Loss of Atomicity and Consistency: Modern databases excel at maintaining data integrity through ACID properties. When logic is split between the application and the database, ensuring consistency becomes more complex.
• Security: Implementing access control in the application layer can introduce vulnerabilities. Database-level security features, such as Row-Level Security in PostgreSQL, can streamline this process and enhance security.

Database Programming Is Making a Comeback

Recently, there has been a resurgence of interest in database programming, driven by the recognition of its benefits. Tools like Hasura and Supabase exemplify this trend, providing developers with powerful frameworks that integrate advanced database capabilities into applications without extensive boilerplate code.

Hasura, for instance, offers a real-time GraphQL API on top of PostgreSQL, while Supabase provides a Firebase-like experience using PostgreSQL’s features. Both tools demonstrate the efficiency of embracing database programming, allowing developers to focus on building applications without sacrificing performance or security.

Database Schema as Code: Database Code Needs Proper Tooling

The shift towards database programming has been facilitated by the emergence of tools that allow developers to manage database schemas as code. This approach enables versioning, testing, and sharing of database resources, akin to traditional software development practices.

One such tool is Atlas, which provides a declarative API for managing database resources and a testing framework for validating database logic. With Atlas, developers can apply modern software engineering principles to database design, bridging the gap between application logic and database capabilities.

Playing Rock, Paper, Scissors on Your PostgreSQL

To illustrate the application of these concepts, we will create a Rock, Paper, Scissors game directly within PostgreSQL. The first step involves setting up a local PostgreSQL instance using Docker:

docker run --rm -e POSTGRES_PASSWORD=pass --name rps -p 5432:5432 -d postgres:16

Next, we will define the game logic in a schema file, starting with the necessary types and functions:

-- Create enum type "move"
CREATE TYPE "move" AS ENUM ('rock', 'paper', 'scissors');

-- Create enum type "result"
CREATE TYPE "result" AS ENUM ('win', 'lose', 'draw');

After defining the core components, we can apply the schema to our local database using Atlas.

Our Business Logic

We will encapsulate the game’s logic in a function that determines the outcome of each turn:

-- Create "turn_result" function
CREATE FUNCTION "turn_result" ("player" "move", "opponent" "move") RETURNS "result" LANGUAGE plpgsql AS $$
BEGIN
  RETURN
    CASE
      WHEN player = 'rock' AND opponent = 'scissors' THEN 'win'
      WHEN player = 'rock' AND opponent = 'paper' THEN 'lose'
      WHEN player = 'paper' AND opponent = 'rock' THEN 'win'
      WHEN player = 'paper' AND opponent = 'scissors' THEN 'lose'
      WHEN player = 'scissors' AND opponent = 'paper' THEN 'win'
      WHEN player = 'scissors' AND opponent = 'rock' THEN 'lose'
      ELSE 'draw'
    END;
END;
$$;

This function will be tested to ensure it behaves as expected before being applied to the database.

Testing Our Code

Atlas provides a built-in testing framework that allows us to write unit tests for our database functions. We can create a test case for the turn_result function to verify its correctness:

test "schema" "turn_result" {
  parallel = true
  for_each = [
    {player: "rock", opponent: "rock", expected: "draw"},
    {player: "rock", opponent: "paper", expected: "lose"},
    {player: "rock", opponent: "scissors", expected: "win"},
    {player: "paper", opponent: "rock", expected: "win"},
    {player: "paper", opponent: "paper", expected: "draw"},
    {player: "paper", opponent: "scissors", expected: "lose"},
    {player: "scissors", opponent: "rock", expected: "lose"},
    {player: "scissors", opponent: "paper", expected: "win"},
    {player: "scissors", opponent: "scissors", expected: "draw"},
  ]
  log {
    message = "Testing ${each.value.player}, ${each.value.opponent} -> ${each.value.expected}"
  }
  exec {
    sql    = "SELECT turn_result('${each.value.player}', '${each.value.opponent}')"
    output = each.value.expected
  }
}

Running this test will confirm the functionality of our game logic.

Storing Results in a Table

To keep track of game history, we will create a table to store the results:

-- Create "games" table
CREATE TABLE "games" (
  "id" integer NOT NULL GENERATED ALWAYS AS IDENTITY,
  "player" "move" NOT NULL,
  "opponent" "move" NOT NULL,
  "result" "result" NOT NULL,
  PRIMARY KEY ("id")
);

Once the table is created, we can implement additional functions to handle game interactions and render results for players.

Trying out the Game

After implementing the game logic and functions, we can interact with our PostgreSQL database to play the game:

select play('rock');

The output will indicate the opponent’s move and the result of the game, allowing players to engage with the application directly.

In conclusion, this example illustrates how leveraging database programming can enhance application performance and security while simplifying code management. By embracing modern tools and methodologies, developers can revitalize the art of database programming and unlock its full potential.