Metadata-Version: 2.4
Name: beaver-box
Version: 1.1.0
Classifier: Programming Language :: Rust
Classifier: Programming Language :: Python :: Implementation :: CPython
Classifier: Topic :: Database
License-File: LICENSE
Summary: Turn raw data into ready-to-run SQL.
Keywords: sql,csv,json,database,cli,etl
Home-Page: https://gitlab.com/cjggarcia.dev/beaver
Author-email: "Christian Joyce G. Garcia" <cjggarcia.dev@gmail.com>
License-Expression: Apache-2.0
Requires-Python: >=3.8
Description-Content-Type: text/markdown; charset=UTF-8; variant=GFM
Project-URL: Issues, https://gitlab.com/cjggarcia.dev/beaver/-/issues
Project-URL: Repository, https://gitlab.com/cjggarcia.dev/beaver

<p align="center">
    <img src="https://gitlab.com/cjggarcia.dev/beaver/-/raw/main/assets/banner.png?ref_type=heads" alt="beaver banner">
</p>

<p align="center">
    <a href="https://pypi.org/project/beaver-box/"><img src="https://img.shields.io/pypi/v/beaver-box?style=flat-square&color=blue&v=1.1.0" alt="PyPI Version"></a>
    <a href="https://pypi.org/project/beaver-box/"><img src="https://img.shields.io/badge/python-%3E%3D3.8-blue?style=flat-square&logo=python&logoColor=white" alt="Python Versions"></a>
    <a href="https://www.rust-lang.org/"><img src="https://img.shields.io/badge/rust-1.70%2B-orange?style=flat-square&logo=rust" alt="Rust Version"></a>
    <a href="https://gitlab.com/cjggarcia.dev/beaver"><img src="https://img.shields.io/badge/GitLab-repo-fc6d26?style=flat-square&logo=gitlab&logoColor=white" alt="GitLab"></a>
    <a href="https://pypi.org/project/beaver-box/"><img src="https://img.shields.io/pypi/implementation/beaver-box?style=flat-square" alt="Python Implementation"></a>
    <a href="https://gitlab.com/cjggarcia.dev/beaver/-/blob/main/LICENSE"><img src="https://img.shields.io/badge/license-Apache--2.0-green?style=flat-square" alt="License"></a>
</p>

# Beaver 

**Turn raw data into ready-to-run SQL in one command.**

Beaver is a high-performance data conversion tool written in [Rust](https://rust-lang.org/). It streams structured CSV, JSON and NDJSON data from files, standard input or pipeline streams and outputs validated, dialect-accurate SQL statements.

Designed for low overhead and speed, Beaver operates as a zero-dependency CLI binary or as a native [Python](https://www.python.org/) extension compiled via [PyO3](https://pyo3.rs/v0.29.3/).


## Table of Contents

- [Features](#features)
- [Installation](#installation)
- [Command-Line Usage](#command-line-usage)
    - [CLI Flags Reference](#cli-flags-reference)
    - [Example Output](#example-output)
- [Python Usage](#python-usage)
    - [Error Handling](#error-handling)
- [Behavior Reference](#behavior-reference)
    - [Type Inference](#type-inference)
    - [SQL Type Mapping](#sql-type-mapping)
    - [Primary Keys](#primary-keys)
- [License](#license)
- [Author](#author)

## Features

- **Streaming Processing:** Streams continuous inputs (CSV, JSON arrays, NDJSON) with minimal footprint.
- **Multi-Dialect Support:** Supports *PostgreSQL*, *MySQL* and *SQLite* identifier quoting and value escaping rules.
- **Automatic Type Inference:** Infers column types on the fly.
- **DDL & DML Generation:** Generates schema definitions alongside value-escaped statements.
- **Primary Keys:** Mark existing columns (including composite keys) as the primary key, or add an auto-increment `id` column.
- **Dual Runtime Interface:** Available as a standalone CLI tool and a C-extension Python Library [beaver-box](https://pypi.org/project/beaver-box/).


## Installation

```bash
pip install beaver-box
```

Verify installation:

```bash
beaver --version
```

## Command-Line Usage

```bash
beaver [OPTIONS]
```

Basic Conversion

```bash
# Convert CSV to SQL
beaver -i data.csv -o data.sql -t users --create-table

# Convert JSON to SQL
beaver -i data.json -f json -o data.sql -t users --create-table
```

**Stream from Standard Input (Pipelines)**

Pipe JSON or CSV data directly into Beaver:

```bash
cat data.ndjson | beaver -f json -t users -d postgres
```

**Generate Table Schema (DDL) and Inserts**
Include the `--create-table` flag to prepend a `CREATE TABLE IF NOT EXISTS` definition:

```bash 
cat data.csv | beaver -f csv -t users -d mysql --create-table
```

**Convert File and Write Output Directly to File**

```bash 
beaver -i data.csv -o user_data.sql -t users -d sqlite --create-table
```

**Create a Primary Key**

Use `-k` / `--primary-key` to make one or more existing columns the primary key. Separate column names with commas for a composite key:

```bash
# Single column
beaver -i data.csv -t users --create-table -k id

# Composite key
beaver -i book.csv -t orders --create-table -k user_id,book_id
```

If your data has no suitable column, use `--auto-id` to add a new auto-increment `id` column that the database fills in:

```bash
beaver -i data.csv -t people --create-table --auto-id
```

> `--primary-key` and `--auto-id` only affect the generated `CREATE TABLE` statement, so they require `--create-table`. They cannot be used together.

### CLI Flags Reference

| Option | Short | Description | Default |
| :--- | :--- | :--- | :--- |
| `--input` | `-i` | Input file path (reads from stdin if omitted) | `stdin` |
| `--output` | `-o` | Output file path (writes to stdout if omitted) | `stdout` |
| `--table` | `-t` | Target database table name | `beaver_table` |
| `--format` | `-f` | Input format (`csv`, `json`) | `csv` |
| `--dialect` | `-d` | SQL dialect (`postgres`, `sqlite`, `mysql`) | `postgres` |
| `--create-table` | | Generate `CREATE TABLE IF NOT EXISTS` DDL | `false` |
| `--primary-key` | `-k` | Existing column(s) to use as primary key, comma-separated. Requires `--create-table` | none |
| `--auto-id` | | Add an auto-increment `id` primary key column. Requires `--create-table` | `false` |
| `--help` | `-h` | Print help information | |
| `--version` | `-V` | Print version information | |


> The `json` format accepts both a JSON array of objects and newline-delimited JSON (NDJSON). Beaver detects which one it is from the first character of the input.
 
### Example Output

Given `data.csv`:

```csv
id,name,active,joined
1,Karen,true,2024-03-01
2,G'Christian,false,2024-04-12
```

Running `beaver -i data.csv -t users -d postgres --create-table` produces:

```sql
CREATE TABLE IF NOT EXISTS "users" (
  "id" BIGINT,
  "name" TEXT,
  "active" BOOLEAN,
  "joined" DATE
);
 
INSERT INTO "users" ("id", "name", "active", "joined") VALUES (1, 'Karen', TRUE, '2024-03-01');
INSERT INTO "users" ("id", "name", "active", "joined") VALUES (2, 'G''Christian', FALSE, '2024-04-12');
```

**With a primary key**

Running `beaver -i data.csv -t users -d postgres --create-table -k id` marks the `id` column as the primary key. Key columns become `NOT NULL`, and a `PRIMARY KEY` clause is added:

```sql
CREATE TABLE IF NOT EXISTS "users" (
  "id" BIGINT NOT NULL,
  "name" TEXT,
  "active" BOOLEAN,
  "joined" DATE,
  PRIMARY KEY ("id")
);
```

**With an auto-increment id**

Given `data.csv`:

```csv
name,email
Karen,karen@example.com
Christian,christian@example.com
```

Running `beaver -i data.csv -t users -d postgres --create-table --auto-id` produces:

```sql
CREATE TABLE IF NOT EXISTS "users" (
  "id" BIGSERIAL PRIMARY KEY,
  "name" TEXT,
  "email" TEXT
);

INSERT INTO "users" ("name", "email") VALUES ('Karen', 'karen@example.com');
INSERT INTO "users" ("name", "email") VALUES ('Christian', 'christian@example.com');
```

> The `INSERT` statements leave out `id`, so the database generates the values.


## Python Usage

Beaver exposes C-extension functions for high-speed string and file conversion inside Python workflows. Each function returns the generated SQL as `str`.

| Function | Input |
| :--- | :--- |
| `convert_csv_to_sql(csv_data, ...)` | CSV **contents** as a string |
| `convert_json_to_sql(json_data, ...)` | JSON / NDJSON **contents** as a string |
| `convert_csv_file_to_sql(path, ...)` | Path to a CSV **file** |
| `convert_json_file_to_sql(path, ...)` | Path to a JSON / NDJSON **file** |

All four accept the same keyword arguments:

| Argument | Description | Default |
| :--- | :--- | :--- |
| `table_name` | Target database table name | `"beaver_table"` |
| `create_table` | Prepend `CREATE TABLE IF NOT EXISTS` DDL | `False` |
| `dialect` | `"postgres"`, `"mysql"` or `"sqlite"` | `"postgres"` |
| `primary_key` | Existing column(s) to use as primary key, comma-separated (for example `"id"` or `"user_id,book_id"`). Requires `create_table=True` | `None` |
| `auto_id` | Add an auto-increment `id` primary key column. Requires `create_table=True` | `False` |


**Convert a File to SQL**
 
1. CSV

```python
import beaver
 
sql = beaver.convert_csv_file_to_sql(
    "data/data.csv",
    table_name="users",
    create_table=True,
    dialect="postgres",
)
 
print(sql)
```
 
2. JSON/NDJSON

```python
import beaver
 
sql = beaver.convert_json_file_to_sql(
    "data/data.json",
    table_name="users",
    create_table=True,
    dialect="postgres",
)
 
print(sql)
```

3. Write Output Directly to a `.sql` File

> Beaver returns the generated SQL as a standard Python string, you can easily save it to a file using Python's built-in file handling:

```python
import beaver

sql = beaver.convert_csv_file_to_sql(
    "data/data.csv",
    table_name="users",
    create_table=True,
    dialect="postgres",
)


with open("user_db.sql", "w") as f:
    f.write(sql)

print("Successfully generated user_db.sql!")
```

**Convert a String to SQL**
 
The `*_to_sql` functions take the data itself, not a file path:
 
```python
import beaver
 
csv_data = "id,name\n1,Karen\n2,Christian\n"
 
sql = beaver.convert_csv_to_sql(
    csv_data, 
    table_name="users", 
    create_table=True
)

print(sql)
```

**Convert a pandas DataFrame to SQL**
 
Beaver works with plain strings, so a DataFrame goes in as CSV text. Use `df.to_csv(index=False)` and pass the result to `convert_csv_to_sql`:

```python
import pandas as pd
import beaver
 
df = pd.DataFrame({
    "id": [1, 2],
    "name": ["Karen", "Christian"],
})
 
sql = beaver.convert_csv_to_sql(
    df.convert_dtypes().to_csv(index=False),
    table_name="users",
    create_table=True,
    primary_key="id",
)
 
print(sql)
```

> pandas is not a dependency of Beaver. Install it separately with `pip install pandas`.



**Create a Primary Key**

Use an existing column *(or several, comma-separated, for a composite key)*:

```python
import beaver

sql = beaver.convert_csv_file_to_sql(
    "data/data.csv",
    table_name="users",
    create_table=True,
    primary_key="id",
)

print(sql)
```

Or let the database generate the key with an auto-increment `id` column:

```python
import beaver

sql = beaver.convert_csv_file_to_sql(
    "data/data.csv",
    table_name="users",
    create_table=True,
    auto_id=True,
)

print(sql)
```


### Error Handling
 
| Exception | Raised when |
| :--- | :--- |
| `ValueError` | The input is malformed (for example, unbalanced quotes in CSV or invalid JSON) |
| `ValueError` | A primary key option is invalid: the column is not in the data, `primary_key` and `auto_id` are used together, `auto_id` is used when the data already has an `id` column, or either option is used without `create_table=True` |
| `OSError` | A file passed to a `*_file_to_sql` function cannot be opened |
 

> An unrecognized `dialect` string in Python falls back to `"postgres"`. The CLI rejects unknown dialects instead.

## Behavior Reference
 
### Type Inference
 
Each value is classified in this order. The first match wins.
 
| Detected as | Rule |
| :--- | :--- |
| Null | Empty value or the text `null` (case-insensitive) |
| Boolean | `true` or `false` (case-insensitive) |
| Integer | Parses as a 64-bit integer |
| Float | Parses as a 64-bit float |
| UUID | 36 characters in `8-4-4-4-12` hexadecimal form |
| Date | `YYYY-MM-DD` |
| Timestamp | `YYYY-MM-DD` followed by `T` or a space and a time (at least 19 characters) |
| Text | Everything else |
 
### SQL Type Mapping
 
| Detected as | PostgreSQL | MySQL | SQLite |
| :--- | :--- | :--- | :--- |
| Integer | `BIGINT` | `BIGINT` | `INTEGER` |
| Float | `DOUBLE PRECISION` | `DOUBLE` | `REAL` |
| Boolean | `BOOLEAN` | `BOOLEAN` | `INTEGER` |
| UUID | `UUID` | `VARCHAR(36)` | `TEXT` |
| Date | `DATE` | `DATE` | `TEXT` |
| Timestamp | `TIMESTAMPTZ` | `DATETIME` | `TEXT` |
| Text / Null | `TEXT` | `TEXT` | `TEXT` |

### Primary Keys

**Existing columns** (`--primary-key` / `primary_key`) are marked `NOT NULL` and listed in a `PRIMARY KEY (...)` clause at the end of the table definition. Composite keys keep the column order you give.

**Auto-increment id** (`--auto-id` / `auto_id`) adds an `id` column as the first column of the table. Its definition depends on the dialect:

| Dialect | Generated `id` column |
| :--- | :--- |
| PostgreSQL | `"id" BIGSERIAL PRIMARY KEY` |
| MySQL | `` `id` BIGINT AUTO_INCREMENT PRIMARY KEY `` |
| SQLite | `"id" INTEGER PRIMARY KEY AUTOINCREMENT` |


## LICENSE

This project is licensed under the [Apache License 2.0](./LICENSE)

## Author

Created and maintained by [cjggarcia.dev](https://gitlab.com/cjggarcia.dev)
