Metadata-Version: 2.4
Name: beaver-box
Version: 1.2.2
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" alt="PyPI Version"></a>
    <a href="https://pypistats.com/packages/beaver-box"><img src="https://pypistats.com/api/badges/beaver-box?period=total" alt="PyPI Stats"></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)
    - [Pipe into a Database](#pipe-into-a-database)
- [Python Usage](#python-usage)
    - [Error Handling](#error-handling)
- [Behavior Reference](#behavior-reference)
    - [Type Inference](#type-inference)
    - [Schema Inference and Sampling](#schema-inference-and-sampling)
    - [SQL Type Mapping](#sql-type-mapping)
    - [Value Escaping](#value-escaping)
    - [Primary Keys](#primary-keys)
    - [Streaming and Memory](#streaming-and-memory)
- [Changelog](#changelog)
- [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 from a sample of rows (`--sample`), so one empty cell no longer tunrs a number column into text.
- **Safe Values:** Rows with leading zeros stay text, `nan` and `inf` are quoted as text and MySQL backslashes are escaped.
- **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.

**Scan More Rows to Detect Column Types**

With `--create-table`, Beaver reads the first 100 rows to decide each column's type. If a column only shows its real type further down the file, raise the number with `-s` / `--sample`:

```bash
beaver -i data.csv -t users --create-table --sample 1000
```

If values after the sampled rows do not fit the inferred types, Beaver still writes the SQL and prints a warning to standard error:

```text
beaver: warning: 2 value(s) did not match the column types inferred from the first 100 row(s); use a larger --sample so the schema covers them
```

### 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` |
| `--sample` | `-s` | Number of rows scanned to infer column types (minimum `1`). Only used with `--create-table` | `100` |
| `--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.
>
> Columns keep the order of the keys in the JSON file.
 

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

**With values that only look like numbers**

Given `places.csv`, where the first row has an empty `score`:

```csv
zip,score,note
01234,,nan
10001,4.5,ok
```

Running `beaver -i places.csv -t places -d postgres --create-table` keeps the zero in `zip` and still detects `score` as a number, because Beaver looks at more than the first row:
 
```sql
CREATE TABLE IF NOT EXISTS "places" (
  "zip" TEXT,
  "score" DOUBLE PRECISION,
  "note" TEXT
);
 
INSERT INTO "places" ("zip", "score", "note") VALUES ('01234', NULL, 'nan');
INSERT INTO "places" ("zip", "score", "note") VALUES ('10001', 4.5, 'ok');
```


### Pipe into a Database

Without `-o`, Beaver writes SQL to standard output, so you can pipe it straight into the command-line client of your database. Set `-d` to match the target database.


**SQLite** *(creates the `.db` file if it does not exist)*

```bash
beaver -i data.csv -t users -d sqlite --create-table | sqlite3 user_data.db
```

**PostgreSQL**

```bash
beaver -i data.csv -t users -d postgres --create-table \
    | psql "postgresql://user:pass@localhost:5432/mydb" -v ON_ERROR_STOP=1 --single-transaction -f -
```

> `ON_ERROR_STOP` stops at the first error, and `--single-transaction` rolls everything back if a statement fails.

**MySQL**

```bash
beaver -i data.csv -t users -d mysql --create-table | MYSQL_PWD=secret mysql -u root mydb
```

**Databases running in Docker**
 
```bash
# PostgreSQL
beaver -i data.csv -t users -d postgres --create-table | docker exec -i my-postgres psql -U postgres -d mydb
 
# MySQL
beaver -i data.csv -t users -d mysql --create-table | docker exec -i my-mysql mysql -u root -psecret mydb
```

> - Without `--create-table`, the target table must already exist.
> - Beaver uses `CREATE TABLE IF NOT EXISTS`, so running the same command twice inserts the rows twice. With `-k`, the second run fails on duplicate keys instead.


## 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` |
| `sample` | Number of rows scanned to infer column types (minimum `1`). Only used with `create_table=True` | `100` |


**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)
```

**Scan More Rows to Detect Column Types**


Raise `sample` when a column only shows its real type further down the file:

```python
import beaver
 
sql = beaver.convert_csv_file_to_sql(
    "data/data.csv",
    table_name="users",
    create_table=True,
    sample=1000,
)
 
print(sql)
```

> The Python functions do not print the type-mismatch warning that the CLI shows. If your data is irregular, use a larger `sample`.


### 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` |
| `ValueError` | `sample` is less than `1` |
| `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 | A whole number that fits in 64 bits and prints back as the exact same text |
| Float | A plain decimal or scientific number such as `3.14`, `-0.5` or `1e10` |
| UUID | 36 characters in `8-4-4-4-12` hexadecimal form |
| Date | `YYYY-MM-DD` with a month from `01` to `12` and a day from `01` to `31` |
| Timestamp | `YYYY-MM-DD`, then `T` or a space, then `HH:MM:SS` (anything after that is allowed) |
| Text | Everything else |

Numbers have to survive a round trip, so values that only look numeric stay text and are quoted:

| Value | Detected as | Why |
| :--- | :--- | :--- |
| `01234`, `007` | Text | A leading zero would be lost |
| `+639171234567`, `+5` | Text | The `+` would be lost |
| `123456789012345678901234` | Text | Too large for a 64-bit integer |
| `nan`, `inf`, `infinity` | Text | Not valid SQL number literals |
| `.5`, `5.` | Text | Not written as a plain decimal |
| `2024-13-45` | Text | Not a real date |
| `0`, `-15`, `0.5`, `1e10` | Integer / Float | Exact round trip |


### Schema Inference and Sampling
 
When `--create-table` is on, Beaver reads the first `--sample` rows *(default `100`)*, merges the type of every value seen in each column and writes the `CREATE TABLE` statement before the first `INSERT`. Columns are listed in the order they first appear and a column that only shows up in later sampled rows is still included.

| Values seen in a column | Column type |
| :--- | :--- |
| All the same type | Type |
| Nulls and one other type | Type |
| `Integer` and `Float` | `Float` |
| `Date` and `Timestamp` | `Timestamp` |
| Any other mix, or only nulls | `Text` |

> Values are then written to match the column. A number in a `TEXT` column is quoted (`'5'`), and values in numeric or date columns are written unquoted or quoted as usual.

- Input shorter than `--sample` rows is read completely before anything is written.
- A value after the sampled rows that does not fit its column (for example `abc` in an integer column) is still written. The CLI counts these values and prints one warning at the end. Use a larger `--sample` to cover them.
- Without `--create-table` there is no schema, and each value is written from its own inferred type.

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


### Value Escaping

Text values are wrapped in single quotes, and leading and trailing whitespace is trimmed.

| Dialect | Escaping inside text values |
| :--- | :--- |
| PostgreSQL | `'` becomes `''` |
| SQLite | `'` becomes `''` |
| MySQL | `\` becomes `\\` and `'` becomes `''` |


> *MySQL* treats a backslash as an escape character by default. If your server runs with the `NO_BACKSLASH_ESCAPES` SQL mode, backslashes in values will be stored doubled.


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


### Streaming and Memory

- CSV is read in chunks, and one row at a time is converted and written.
- A top-level JSON array is read one object at a time, and NDJSON one line at a time. Nothing is loaded as a whole.
- Only the sampled rows are kept in memory, and only when `--create-table` is used.
- Unbalanced quotes in a CSV are detected when the end of the input is reached. Rows converted before that point have already been written, so check the exit code (`1` on error) before using the output.


## Changelog
 
See [CHANGELOG.md](./CHANGELOG.md) for the list of changes in each release.


## LICENSE

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

## Author

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