# How to Perform Database Initialization with createTable.py in Social-Auto-Upload

> Learn how to perform database initialization with createTable.py in Social-Auto-Upload. This script generates the SQLite database and necessary tables for storing user data and upload history.

- Repository: [Alleria/social-auto-upload](https://github.com/dreammis/social-auto-upload)
- Tags: how-to-guide
- Published: 2026-05-31

---

**Run `python db/createTable.py` from the project root to generate the SQLite database file (`db/database.db`) with all required tables for storing user accounts, video metadata, and upload history.**

The `dreammis/social-auto-upload` repository uses a local SQLite database to persist authentication cookies, video metadata, and upload logs across sessions. Before the backend server or CLI tools can function, you must perform database initialization with **createTable.py** to establish the required schema.

## What createTable.py Does

Located at [`db/createTable.py`](https://github.com/dreammis/social-auto-upload/blob/main/db/createTable.py), this utility script performs three critical operations:

1. **Opens or creates** the SQLite file at `db/database.db`.
2. **Executes DDL statements** using `CREATE TABLE IF NOT EXISTS` to define the schema without risking duplicate tables.
3. **Commits and closes** the connection, leaving a ready-to-use database file.

The script is designed to be **idempotent**—running it multiple times will not corrupt data or create duplicate tables because each statement checks for existence before creation.

## Database Schema Overview

The initialization script creates the following tables to support the application's core functionality:

- **`user`** – Stores authentication cookies and profile information for each configured social-media account.
- **`video`** – Holds metadata for uploaded videos, including titles, descriptions, tags, and file paths.
- **`upload_log`** – Records every upload attempt with timestamps, status codes, and error messages.
- **`schedule`** – (Optional) Maintains scheduled-release data when using the scheduling feature.

## When to Run the Script

You must initialize the database in these scenarios:

- **First-time setup** – Immediately after cloning the repository and installing dependencies.
- **After database deletion** – If you manually delete `db/database.db` to reset the system state.

Once initialized, both [`sau_backend.py`](https://github.com/dreammis/social-auto-upload/blob/main/sau_backend.py) (the REST API server) and [`cli_main.py`](https://github.com/dreammis/social-auto-upload/blob/main/cli_main.py) (the command-line interface) automatically connect to `db/database.db` on startup.

## How to Run createTable.py

Execute the script from the project root directory using either direct execution or module syntax.

### Direct Execution

```bash
python db/createTable.py

```

### Module Syntax

```bash
python -m db.createTable

```

Both commands produce identical results: a SQLite database file at `db/database.db` containing the full schema required by the application.

## Programmatic Database Initialization

You can trigger the initialization from within another Python script using the `subprocess` module:

```python
import subprocess

# Re-initialize the database programmatically

subprocess.run(["python", "db/createTable.py"], check=True)
print("Database initialised successfully.")

```

This approach is useful for automated deployment scripts or test suites that require a fresh database state.

## Verifying the Database Schema

To confirm the tables were created correctly, inspect the database using Python's built-in `sqlite3` module:

```python
import sqlite3

conn = sqlite3.connect("db/database.db")
cursor = conn.cursor()

# List all tables in the database

cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
tables = [row[0] for row in cursor.fetchall()]

print("Tables created:", tables)
conn.close()

```

This verification step lists `user`, `video`, `upload_log`, and `schedule` when initialization completes successfully.

## Summary

- The **[`db/createTable.py`](https://github.com/dreammis/social-auto-upload/blob/main/db/createTable.py)** script generates `db/database.db` with the complete SQLite schema required by the application.
- The script is **idempotent** and safe to run multiple times thanks to `CREATE TABLE IF NOT EXISTS` statements.
- Core tables include **`user`**, **`video`**, **`upload_log`**, and **`schedule`**.
- Both **[`sau_backend.py`](https://github.com/dreammis/social-auto-upload/blob/main/sau_backend.py)** and **[`cli_main.py`](https://github.com/dreammis/social-auto-upload/blob/main/cli_main.py)** require this database to exist before they can read or write persistent data.

## Frequently Asked Questions

### Is it safe to run createTable.py multiple times?

Yes. According to the `social-auto-upload` source code, the script uses `CREATE TABLE IF NOT EXISTS` for every table definition. This makes the operation idempotent—running it repeatedly will not duplicate tables, corrupt existing data, or throw errors.

### Where is the database file stored after initialization?

The script creates **`db/database.db`** relative to the project root. This file path is hardcoded in the initialization script and expected by [`sau_backend.py`](https://github.com/dreammis/social-auto-upload/blob/main/sau_backend.py) and the uploader modules unless you manually modify the source code.

### Do I need to configure database credentials before running the script?

No configuration is necessary. SQLite operates via file-based storage, requiring no username, password, or connection string. The script uses default settings to create the database locally, making it immediately usable by the backend and CLI components.

### Why does the backend fail if I skip database initialization?

Both [`sau_backend.py`](https://github.com/dreammis/social-auto-upload/blob/main/sau_backend.py) and [`cli_main.py`](https://github.com/dreammis/social-auto-upload/blob/main/cli_main.py) attempt to read from and write to the database tables on startup. Without running [`createTable.py`](https://github.com/dreammis/social-auto-upload/blob/main/createTable.py) first, the application cannot store user cookies, log upload attempts, or retrieve video metadata, resulting in runtime connection errors.