# 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.

```prisma
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`.

```rust
// 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)

```rust
// 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:

```bash
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:

```bash
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.