Database Guide: Architecture and Data Management

The database is a critical component of the Multi-Language Compiler, serving as the persistent storage layer for all code executions. Its primary role is to create a historical record of every code snippet that is processed by the backend. This guide details the technology, schema, and interaction patterns used to manage and store data effectively.

2. Technology Stack

The data tier is built on a modern and robust technology stack:

• Database System: PostgreSQL, a powerful, open-source object-relational database system known for its reliability, feature robustness, and performance.

• ORM (Object-Relational Mapper): Prisma, a next-generation ORM for Node.js and TypeScript (used here for its powerful schema management and migration tools) that integrates seamlessly with the Rust backend through the prisma-client-rust crate.

3. Database Schema Definition

The entire database schema is declaratively defined in a single file, which serves as the single source of truth for the data models.

• Location: db/prisma/schema.prisma

The schema defines one primary model: CodeSnippet. This model represents a single, complete record of a code execution event.

generator client {
  provider = "cargo prisma" // Generates the Rust client
  output   = "../src/prisma.rs"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model CodeSnippet {
  id            Int      @id @default(autoincrement())
  code          String
  language      String
  output        String
  executionTime Int
  createdAt     DateTime @default(now())
}

Fields Explained:

Field	Type	Description
id	Int	A unique, auto-incrementing integer that serves as the Primary Key.
code	String	Stores the exact source code string that was submitted by the user.
language	String	Stores the language of the submitted code (e.g., "rust", "python").
output	String	Stores the complete captured output (stdout or stderr) from the execution.
executionTime	Int	Records the execution time of the code in milliseconds.
createdAt	DateTime	A timestamp automatically set to the moment the record is created.

4. Database Interaction in Code

The Rust backend is responsible for all communication with the database. It uses the prisma-client-rust crate, which provides a type-safe and idiomatic way to perform database queries.

4.1. Prisma Client Initialization

Before any queries can be made, the Prisma client is initialized within the Rust application. This is typically handled in main.rs.

// In src/main.rs
let db = PrismaClient::_builder().build().await?;

4.2. Storing Data: The create Operation

After every code execution, a new record is created in the CodeSnippet table. The following function from main.rs demonstrates exactly how this is done.

• Location: src/main.rs (within the compile_code handler)

// A snippet from the compile_code function after execution
let (output, execution_time) = match language.as_str() {
    // ... execution logic ...
};

// Destructure the output to get result and error strings
let (result, error) = if output.status.success() {
    (String::from_utf8_lossy(&output.stdout).to_string(), "".to_string())
} else {
    ("".to_string(), String::from_utf8_lossy(&output.stderr).to_string())
};

// Combine them for the 'output' column
let db_output = if !result.is_empty() { result.clone() } else { error.clone() };

// Create the database record using the Prisma client
db.code_snippet()
    .create(
        code.clone(),
        language.clone(),
        db_output,
        execution_time as i32,
        vec![],
    )
    .exec()
    .await?;

// ... return response to user ...dt

Code Explanation:

1. Execute Code: The compile_code function first executes the user's code and captures the output and execution_time.

2. Process Output: It checks if the execution was successful. It populates a result string with stdout on success, or an error string with stderr on failure.

3. Prepare for Storage: A db_output variable is created to hold whichever string (result or error) is non-empty. This is what will be stored in the database's output column.

4. Create Record: The db.code_snippet().create(...) function is called.

   • This is a type-safe function generated by Prisma based on your schema.

   • It takes all the required fields (code, language, output, executionTime) as arguments.

   • The vec![] is for any optional fields that are not being set.

5. Execute Query: The .exec().await? part sends the INSERT query to the PostgreSQL database and awaits its completion.

5. Database Migrations

Prisma provides a powerful and straightforward system for managing changes to the database schema over time.

• Location of Migrations: db/prisma/migrations/

This directory contains subdirectories for each migration, each with a migration.sql file that holds the raw SQL commands needed to update the database schema.

How to Manage Migrations

All migration commands should be run from the root of the repository.

• Creating a New Migration: Whenever you make a change to the schema.prisma file, you can create a new migration by running:

npx prisma migrate dev --name your_migration_name

• This command will:

  1. Compare the current schema to the database state.

  2. Generate a new SQL migration file.

  3. Apply the new migration to your development database.

• Applying Migrations to Other Environments (e.g., Production): To apply all pending migrations to a database, you can run:

npx prisma migrate deploy

6. Setup and Connection

The connection between the application and the database is configured using an environment variable.

• Configuration File: .env (at the root of the project)

You must have a .env file containing the connection string for your PostgreSQL database.