Metadata-Version: 2.4
Name: webduck
Version: 1.0.1
Summary: A DuckDB server with REST API and Web UI
Author-email: autumo GmbH <info@autumo.ch>
License-Expression: MIT
License-File: LICENSE
Keywords: api,database,duckdb,server
Classifier: Development Status :: 3 - Alpha
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: MIT License
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Libraries
Requires-Python: >=3.11
Requires-Dist: babel>=2.14.0
Requires-Dist: bcrypt>=4.1.0
Requires-Dist: click>=8.0
Requires-Dist: duckdb>=1.0.0
Requires-Dist: fastapi>=0.115.0
Requires-Dist: nicegui>=2.0.0
Requires-Dist: pyjwt>=2.8.0
Requires-Dist: python-multipart>=0.0.7
Requires-Dist: pyyaml>=6.0
Requires-Dist: uvicorn[standard]>=0.30.0
Provides-Extra: dev
Requires-Dist: pytest-asyncio>=0.23; extra == 'dev'
Requires-Dist: pytest>=8.0; extra == 'dev'
Requires-Dist: ruff>=0.5.0; extra == 'dev'
Description-Content-Type: text/markdown

<div align="center">
  <img src="src/webduck/static/icon.svg" width="128">
  <h1>WebDuck</h1>

  A self-hosted DuckDB-as-a-Service server with REST API and Web UI.
  WebDuck is designed for web hosting providers who want to offer their customers
  a ready-to-use administration interface for DuckDB databases and data analytics
  — out of the box, just like the database admin tools that come with any hosting package.

  <img src="https://img.shields.io/badge/version-1.0.1-FFD54F">
  <img src="https://img.shields.io/badge/license-MIT-green">
  <img src="https://img.shields.io/badge/python-3.11+-blue">

  <br><br>
  <a href="https://webduck.autumo.ch">Website</a> · 
  <a href="https://github.com/autumoswitzerland/Webduck/issues">Issues</a> · 
  <a href="https://github.com/autumoswitzerland/Webduck/blob/main/LICENSE">License</a>
</div>

## Features

- **DuckDB storage engine** with file-locking for safe concurrent access
- **REST API** — admin endpoints (project/DB management) + database endpoints (SQL queries)
- **Web UI** (NiceGUI) — dark mode with yellow/amber theme
- **JWT authentication** for admin API, optional project-key auth for database access
- **bcrypt password hashing** (never plaintext)
- **SQL editor** — multi-statement support with sequential execution
- **SQL upload** — execute multi-statement SQL scripts from files
- **Browse view** — tree-based navigation of databases/tables/views with infinite scroll and cell editing
- **Import/Export** — CSV import via drag & drop, CSV export with browser download
- **Project ordering** — drag & drop reordering, persisted in `.projects.json`
- **i18n** — English (default), German, extensible via PO/Gettext
- **Configurable logging** — file rotation + console output, independently configurable
- **Configurable upload size** — max file size for CSV/SQL uploads
- **FK dependency hints** — DROP errors include referenced tables and constraint info
- **Docker** support out of the box
- **YAML configuration**

## Web UI

<table>
<tr>
<td align="center"><strong>Projects</strong><br><a href="src/webduck/static/screen_projects.webp"><img src="src/webduck/static/screen_projects.webp" width="450"></a></td>
<td align="center"><strong>Browse View</strong><br><a href="src/webduck/static/screen_browse.webp"><img src="src/webduck/static/screen_browse.webp" width="450"></a></td>
</tr>
</table>

## Installation

### pip (Python)

```bash
pip install webduck
```

### Docker

```bash
# docker-compose.yml herunterladen
curl -O https://raw.githubusercontent.com/autumoswitzerland/Webduck/main/docker-compose.yml

# Passwort setzen
export WEBDUCK_ADMIN_PASS=meinpasswort

# Starten (Admin-User wird automatisch angelegt)
docker compose up -d
```

## Quick Start

### pip

```bash
# Initialisieren (Admin-User anlegen)
webduck init

# Server starten
webduck start
```

### Docker

Docker startet automatisch — UI unter `http://localhost:8998/ui/login`.
Login mit `admin` / dem gesetzten Passwort.

## CLI Commands

| Command | Description |
|---------|-------------|
| `webduck init` | Create admin user + default config |
| `webduck start` | Start the server |
| `webduck status` | Show projects and databases |

Options:

```bash
webduck start --host 127.0.0.1 --port 8080 --config /path/to/webduck.yaml
```

## Configuration

`webduck.yaml` (generated by `webduck init`):

```yaml
icon: "icon.svg"
auth:
  jwt_algorithm: HS256
  jwt_expire_minutes: 60
  jwt_secret: CHANGE-ME-TO-A-SECRET-KEY-IN-PRODUCTION
logging:
  file:
    enabled: true
    level: error
    max_files: 5
    max_size_mb: 10
    query_log: false
    log_dir: log
  console:
    enabled: false
    access_log: false
    level: warning
server:
  data_dir: data
  host: 0.0.0.0
  port: 8998
  max_upload_mb: 5
```

## Web UI Pages

| Page | URL | Description |
|------|-----|-------------|
| Login | `/ui/login` | Admin login with language selection |
| Dashboard | `/ui/dashboard` | Server status, project/database overview |
| Projects | `/ui/projects` | Create/delete projects, manage databases, set passwords, drag & drop reorder |
| Queries | `/ui/query` | SQL editor (multi-statement) + SQL file upload |
| Browse | `/ui/browse` | Tree navigation, table/view browsing with infinite scroll, cell editing |
| Import/Export | `/ui/import` | CSV import (drag & drop) and export (browser download) |

## REST API Reference

### Admin API (JWT protected)

All admin endpoints require `Authorization: Bearer <jwt_token>`.

| Method | Endpoint | Description |
|--------|----------|-------------|
| `POST` | `/admin/login` | Login, returns JWT token |
| `GET` | `/admin/projects` | List all projects (ordered) |
| `POST` | `/admin/projects` | Create project |
| `DELETE` | `/admin/projects/{project}` | Delete project + all databases |
| `POST` | `/admin/reorder-projects` | Reorder projects |
| `GET` | `/admin/projects/{project}/databases` | List databases in project |
| `POST` | `/admin/projects/{project}/databases` | Create database |
| `DELETE` | `/admin/projects/{project}/databases/{db}` | Delete database |
| `PUT` | `/admin/projects/{project}/databases/{db}/password` | Set database password |
| `GET` | `/admin/users` | List admin users |
| `POST` | `/admin/users` | Create admin user |
| `DELETE` | `/admin/users/{username}` | Delete admin user |

### Database API (project-key protected)

All database endpoints require `X-Project-Key: <project>:<password>`.
Databases without a password set are publicly accessible.

| Method | Endpoint | Description |
|--------|----------|-------------|
| `GET` | `/db/projects` | List all projects (public) |
| `GET` | `/db/projects/{project}/databases` | List databases (public) |
| `POST` | `/db/projects/{project}/databases/{db}/query` | Execute SQL (read-only) |
| `POST` | `/db/projects/{project}/databases/{db}/write` | Execute SQL (read-write) |
| `GET` | `/db/projects/{project}/databases/{db}/tables` | List tables + columns |
| `POST` | `/db/projects/{project}/databases/{db}/import` | Import CSV |
| `GET` | `/db/projects/{project}/databases/{db}/export` | Export table to CSV |

### Example: Login + Query

```bash
# Login
TOKEN=$(curl -s -X POST http://localhost:8998/admin/login \
  -H "Content-Type: application/json" \
  -d '{"username":"admin","password":"secret"}' | jq -r '.access_token')

# Create project + database
curl -X POST http://localhost:8998/admin/projects \
  -H "Authorization: Bearer $TOKEN" \
  -d '{"name":"myapp"}'

curl -X POST http://localhost:8998/admin/projects/myapp/databases \
  -H "Authorization: Bearer $TOKEN" \
  -d '{"name":"main"}'

# Set database password (optional — without password, API is open)
curl -X PUT http://localhost:8998/admin/projects/myapp/databases/main/password \
  -H "Authorization: Bearer $TOKEN" \
  -d '{"password":"dbpass","access_level":"write"}'

# Execute a query via the Database API
curl -X POST http://localhost:8998/db/projects/myapp/databases/main/write \
  -H "X-Project-Key: myapp:dbpass" \
  -H "Content-Type: application/json" \
  -d '{"sql":"CREATE TABLE users (id INT, name VARCHAR); INSERT INTO users VALUES (1, '\''Alice'\'');"}'

# Read (with password)
curl -X POST http://localhost:8998/db/projects/myapp/databases/main/query \
  -H "X-Project-Key: myapp:dbpass" \
  -d '{"sql":"SELECT * FROM users"}'

# Read (without password — no header needed)
curl -X POST http://localhost:8998/db/projects/myapp/databases/main/query \
  -d '{"sql":"SELECT * FROM users"}'
```

## Docker

```bash
docker compose up -d
```

The data directory is mounted at `./data`. Initialize with:

```bash
docker compose exec webduck webduck init
```

## Architecture

```
FastAPI ──┬── /admin/*   (JWT auth)    → Project/DB management
          ├── /db/*      (project key) → SQL queries + CSV import/export
          ├── /ui/*      (cookie)      → NiceGUI Web UI
          ├── /api/*     (session)     → Internal UI endpoints
          └── /health                  → Health check
```

- **Storage:** DuckDB files in `data/<project>/<database>.duckdb`
- **Project order:** Persisted in `data/.projects.json`
- **Auth:** Admin passwords in `data/.users.json` (bcrypt), DB passwords in `data/<project>/.project.json`
- **User preferences:** Stored in `data/.user_preferences.json`
- **Logging:** Rotated log files in `log/` (configurable), independent console logging
- **Concurrency:** Single-writer via file-level locks (apps manage write contention)

## Development

```bash
git clone https://github.com/autumoswitzerland/Webduck.git
cd webduck
python3.13 -m venv .venv
source .venv/bin/activate
pip install -e ".[dev]"

# Run tests
pytest tests/ -v

# Run linter
ruff check src/ tests/
```

## Donate

WebDuck is free and open-source. If you find it useful, consider supporting the development:

[![Donate via PayPal](https://img.shields.io/badge/Donate-PayPal-blue)](https://www.paypal.com/ncp/payment/NZ4CC6SVF9HN8)

## License

MIT — see [LICENSE](LICENSE) for details.

<br>
<hr>

Copyright &copy; 2026 autumo GmbH
