### Install Jetbase with pip or uv

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/index.md

Demonstrates how to install the Jetbase library using package managers pip and uv. No specific dependencies are mentioned for the installation itself.

```shell
pip install jetbase
```

```shell
uv add jetbase
```

--------------------------------

### Install Jetbase (Bash)

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/getting-started.md

Installs the Jetbase package using pip. For Snowflake support, install with extras.

```bash
pip install jetbase

```

```bash
pip install "jetbase[snowflake]"

```

--------------------------------

### Install Jetbase using pip or uv

Source: https://github.com/jetbase-hq/jetbase/blob/main/README.md

Install the Jetbase package using either pip or uv. For Snowflake support, install with the 'snowflake' extra.

```shell
pip install jetbase

```

```shell
uv add jetbase

```

```shell
pip install "jetbase[snowflake]"

```

--------------------------------

### Jetbase CLI Workflow Example

Source: https://context7.com/jetbase-hq/jetbase/llms.txt

A comprehensive example demonstrating the Jetbase command-line interface for initializing a project, creating, editing, previewing, and applying migrations, and checking the migration status and history.

```bash
# 1. Initialize Jetbase in your project
jetbase init
cd jetbase

# 2. Configure database connection in env.py
# sqlalchemy_url = "postgresql+psycopg2://admin:secret@localhost:5432/myapp"

# 3. Create your first migration
jetbase new "create users table" -v 1

# 4. Edit the migration file (migrations/V1__create_users_table.sql)
# -- upgrade
# CREATE TABLE users (
#     id SERIAL PRIMARY KEY,
#     email VARCHAR(255) UNIQUE NOT NULL,
#     name VARCHAR(100),
#     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
# );
# -- rollback
# DROP TABLE IF EXISTS users;

# 5. Preview the migration
jetbase upgrade --dry-run

# 6. Apply the migration
jetbase upgrade

# 7. Check status
jetbase status

# 8. Create another migration
jetbase new "add posts table" -v 2

# 9. Apply all pending migrations
jetbase upgrade

# 10. View history
jetbase history

```

--------------------------------

### Install PostgreSQL Driver with pip

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/database-connections.md

Installs the necessary driver for PostgreSQL connections using pip. Ensure you have pip installed. This command installs the psycopg2-binary package.

```bash
pip install psycopg2-binary

pip install "psycopg[binary]"
```

--------------------------------

### Example Migration File Content (SQL)

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/commands/new.md

An example of the content for a generated SQL migration file. It includes separate sections for 'upgrade' (to apply the migration) and 'rollback' (to undo the migration), which is a best practice for safe migration management.

```sql
-- upgrade
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


-- rollback
DROP TABLE IF EXISTS items;
DROP TABLE IF EXISTS users;
```

--------------------------------

### Example SQLite Connection String (Relative Path)

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/database-connections.md

Shows an example of connecting to a SQLite database using a relative path. The database file will be created or accessed relative to the directory where Jetbase is run.

```python
# jetbase/env.py
ssqlalchemy_url = "sqlite:///myapp.db"
```

--------------------------------

### Versioned Migration SQL Example (Upgrade/Rollback)

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/advanced/migration-types.md

An example SQL file demonstrating the 'upgrade' and 'rollback' sections for a Versioned migration, used for creating and dropping a 'users' table.

```sql
-- upgrade
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- rollback
DROP TABLE IF EXISTS users;
```

--------------------------------

### Jetbase Command Help Examples (Bash)

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/commands/index.md

Demonstrates how to access help information for Jetbase commands using the --help flag. This is useful for understanding command-specific options and usage.

```bash
jetbase --help           # General help
jetbase upgrade --help   # Help for upgrade command
jetbase rollback --help  # Help for rollback command
```

--------------------------------

### Example output of jetbase upgrade --dry-run

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/commands/upgrade.md

This is an example of the output you might see when running 'jetbase upgrade --dry-run'. It lists the SQL statements for migrations that would be applied, allowing for review before execution.

```sql
=== DRY RUN MODE ===
The following migrations would be applied:

--- V1__create_users_table.sql ---
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

--- V2__add_email_to_users.sql ---
ALTER TABLE users ADD COLUMN email VARCHAR(255);

=== END DRY RUN ===
```

--------------------------------

### Install Jetbase with pip or uv

Source: https://context7.com/jetbase-hq/jetbase/llms.txt

Install the Jetbase package and its optional dependencies for Snowflake support using pip or uv. This command makes the `jetbase` CLI tool available for use in your project.

```bash
# Standard installation
pip install jetbase

# Or using uv
uv add jetbase

# For Snowflake support, install with extras
pip install "jetbase[snowflake]"

# Verify installation
jetbase --help
```

--------------------------------

### Example PostgreSQL Connection String

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/database-connections.md

Provides an example of a PostgreSQL SQLAlchemy connection string using psycopg2. It specifies the username, password, host, port, and database name.

```python
# jetbase/env.py
ssqlalchemy_url = "postgresql+psycopg2://myuser:mypassword@localhost:5432/myapp"
```

--------------------------------

### Versioned Migration Filename Format and Examples

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/advanced/migration-types.md

Demonstrates the standard filename format for Versioned migrations, which includes a timestamp and a descriptive name. Provides examples of valid filenames.

```plaintext
V{YYYYMMDD.HHMMSS}__{description}.sql

V20251225.100000__create_users_table.sql
V1__add_email_to_users.sql
V2.1__create_orders_table.sql
```

--------------------------------

### Install Snowflake Driver with pip

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/database-connections.md

Installs Jetbase with the Snowflake extra dependencies. This command ensures all necessary libraries for Snowflake integration are installed.

```bash
pip install "jetbase[snowflake]"
```

--------------------------------

### Runs On Change Migration SQL Example (Functions)

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/advanced/migration-types.md

An example SQL file for a 'Runs On Change' migration, defining and replacing PostgreSQL functions 'calculate_order_total' and 'update_timestamp'. Includes rollback commands.

```sql
-- upgrade
CREATE OR REPLACE FUNCTION calculate_order_total(order_id INTEGER)
RETURNS DECIMAL AS $$
BEGIN
    RETURN (
        SELECT SUM(price * quantity)
        FROM order_items
        WHERE order_items.order_id = $1
    );
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- rollback
DROP FUNCTION IF EXISTS update_timestamp();
DROP FUNCTION IF EXISTS calculate_order_total(INTEGER);
```

--------------------------------

### Example PostgreSQL Connection String

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/configuration.md

Illustrates the format for a PostgreSQL connection string used with the sqlalchemy_url configuration option.

```python
sqlalchemy_url = "postgresql://username:password@host:port/database"
```

--------------------------------

### Preview migration changes with jetbase upgrade dry-run

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/commands/upgrade.md

This example illustrates using the `--dry-run` option with 'jetbase upgrade' to preview the SQL statements that would be applied without actually modifying the database. This is crucial for verifying changes before deployment.

```bash
jetbase upgrade --dry-run
```

--------------------------------

### Check Migration Status (Bash)

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/getting-started.md

Displays the current status of migrations, indicating which ones have been applied and which are pending.

```bash
jetbase status

```

--------------------------------

### Install PostgreSQL Database Driver

Source: https://github.com/jetbase-hq/jetbase/blob/main/README.md

Install the 'psycopg2' Python package, a required driver for using Jetbase with PostgreSQL. Other compatible drivers like 'asyncpg' or 'pg8000' can also be used.

```bash
pip install psycopg2

```

--------------------------------

### Example SQLite Connection String (In-Memory)

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/database-connections.md

Demonstrates how to use an in-memory SQLite database. This is useful for testing purposes as no file is persisted.

```python
# jetbase/env.py
ssqlalchemy_url = "sqlite:///:memory:"
```

--------------------------------

### Write Migration SQL (SQL)

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/getting-started.md

Defines SQL statements for upgrading and rolling back database schema changes. The '-- rollback' section specifies undo operations.

```sql
-- upgrade
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
);

-- rollback
DROP TABLE items;
DROP TABLE users;

```

--------------------------------

### Apply Migrations with Jetbase

Source: https://github.com/jetbase-hq/jetbase/blob/main/README.md

Apply all pending database migrations using the 'jetbase upgrade' command. Ensure necessary database drivers are installed.

```bash
jetbase upgrade

```

--------------------------------

### Runs Always Migration SQL Example

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/advanced/migration-types.md

An example SQL file for a 'Runs Always' migration, showing operations to refresh materialized views and grant permissions. It highlights that rollback is often not needed.

```sql
-- upgrade
REFRESH MATERIALIZED VIEW CONCURRENTLY user_statistics;
REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary;

-- Grant permissions
GRANT SELECT ON user_statistics TO readonly_role;
GRANT SELECT ON order_summary TO readonly_role;

-- rollback
-- Usually no rollback needed for RA migrations
```

--------------------------------

### Write SQL Migration Script

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/index.md

Example SQL code for a migration file, including `-- upgrade` and `-- rollback` sections for applying and undoing changes. This script defines `users` and `items` tables.

```sql
-- upgrade
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
);

-- rollback
DROP TABLE items;
DROP TABLE users;
```

--------------------------------

### Runs On Change Migration Filename Format and Examples

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/advanced/migration-types.md

Shows the filename format for 'Runs On Change' migrations, identified by the 'ROC__' prefix. Provides examples for migration types like stored procedures and functions.

```plaintext
ROC__{description}.sql

ROC__stored_procedures.sql
ROC__functions.sql
ROC__triggers.sql
ROC__views.sql
```

--------------------------------

### Configure Database Connection URL

Source: https://github.com/jetbase-hq/jetbase/blob/main/README.md

Configure the SQLAlchemy database connection URL in the 'jetbase/env.py' file. Examples for PostgreSQL and SQLite are provided.

```python
sqlalchemy_url = "postgresql+psycopg2://user:password@localhost:5432/mydb"

```

```python
sqlalchemy_url = "sqlite:///mydb.db"

```

--------------------------------

### Example Migration SQL with Rollback Section

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/commands/rollback.md

Demonstrates a typical SQL migration file structure, including 'upgrade' and 'rollback' sections. The 'rollback' section contains the SQL necessary to undo the changes made in the 'upgrade' section.

```sql
-- upgrade
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total DECIMAL(10,2)
);
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- rollback
DROP INDEX IF EXISTS idx_orders_user_id;
DROP TABLE IF EXISTS orders;
```

--------------------------------

### Runs Always Migration Filename Format and Examples

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/advanced/migration-types.md

Illustrates the filename format for 'Runs Always' migrations, which use a descriptive name prefixed with 'RA__'. Includes examples for common use cases.

```plaintext
RA__{description}.sql

RA__refresh_materialized_views.sql
RA__update_permissions.sql
RA__rebuild_indexes.sql
```

--------------------------------

### Example Versioned Migrations (SQL)

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/advanced/migration-types.md

Demonstrates the typical file naming convention for versioned SQL migrations in Jetbase. Each file represents a specific change and is ordered chronologically.

```sql
V20251225.100000__create_calc_total_function.sql
V20251226.100000__update_calc_total_v2.sql
V20251227.100000__update_calc_total_v3.sql
V20251228.100000__fix_calc_total_bug.sql
```

--------------------------------

### Skip validation checks with jetbase upgrade

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/commands/upgrade.md

These examples show how to use validation skipping options (`--skip-validation`, `--skip-checksum-validation`, `--skip-file-validation`) with 'jetbase upgrade'. Use these with caution as they can lead to database inconsistencies.

```bash
jetbase upgrade --skip-validation
jetbase upgrade --skip-checksum-validation
jetbase upgrade --skip-file-validation
```

--------------------------------

### Apply a specific number of migrations with jetbase upgrade

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/commands/upgrade.md

This example demonstrates how to use the `--count` option with the 'jetbase upgrade' command to apply only a specified number of pending migrations. This is useful for phased rollouts or testing.

```bash
jetbase upgrade --count 2
```

--------------------------------

### SQL Migration File Structure

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/migrations/index.md

Example of a SQL migration file for Jetbase. It includes an upgrade section with SQL statements to create tables and a rollback section to drop them. Statements must end with a semicolon.

```sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
);

-- rollback
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS items;
```

--------------------------------

### Snowflake Username/Password Authentication

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/database-connections.md

Provides an example of a Snowflake SQLAlchemy connection string using username and password authentication. This is the simplest method for connecting.

```python
# jetbase/env.py
ssqlalchemy_url = "snowflake://myuser:mypassword@myaccount.us-east-1/my_db/public?warehouse=COMPUTE_WH"
```

--------------------------------

### SQL Migration with Comments and Rollback

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/migrations/index.md

Demonstrates a Jetbase SQL migration file with comments and a distinct rollback section. Comments start with `--`, and the rollback section is separated by `-- rollback`.

```sql
-- upgrade

-- my first comment
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE
);

-- my second comment
CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
);

-- rollback
DROP TABLE IF EXISTS items;
DROP TABLE IF EXISTS users;
```

--------------------------------

### Example Repeatable On Change Migration (SQL)

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/advanced/migration-types.md

Illustrates the simpler file naming convention for Repeatable On Change (ROC) SQL migrations. This approach is suitable for managing evolving database objects like functions and procedures.

```sql
ROC__order_functions.sql  # Just edit this file
```

--------------------------------

### Initialize Jetbase Project

Source: https://github.com/jetbase-hq/jetbase/blob/main/README.md

Initialize a new Jetbase project. This command creates a 'jetbase/' directory containing 'migrations/' and 'env.py'.

```bash
jetbase init
cd jetbase

```

--------------------------------

### Initialize Jetbase Project Directory

Source: https://context7.com/jetbase-hq/jetbase/llms.txt

The `jetbase init` command sets up the necessary directory structure and configuration files for your project. After initialization, navigate into the created `jetbase` directory and configure your database connection in `env.py`.

```bash
# Initialize Jetbase in your project root
jetbase init

# Output:
# Initialized Jetbase project in /path/to/your/project/jetbase
# Run 'cd jetbase' to get started!

# Move into the jetbase directory (required for all subsequent commands)
cd jetbase

# Edit env.py with your database connection string
# jetbase/env.py contents:
# sqlalchemy_url = "postgresql+psycopg2://user:password@localhost:5432/mydb"
```

--------------------------------

### Initialize Jetbase Project (Bash)

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/commands/init.md

The 'jetbase init' command initializes a new Jetbase project in the current directory. It creates the necessary directory structure and configuration files, including a 'jetbase/' directory with 'migrations/' and 'env.py'.

```bash
jetbase init
```

--------------------------------

### Create a New Migration

Source: https://github.com/jetbase-hq/jetbase/blob/main/README.md

Create a new migration file using the 'jetbase new' command with a description and optional version. Alternatively, create SQL files manually in the 'jetbase/migrations' directory following the 'V<version>__<description>.sql' naming convention.

```bash
jetbase new "create users table" -v 1

```

--------------------------------

### Create a New Migration File

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/index.md

Command to generate a new SQL migration file with a specified name and version. The output is a SQL file ready for editing.

```bash
jetbase new "create users table" -v 1
```

--------------------------------

### Create Migration File with Version (Bash)

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/commands/new.md

Generates a new SQL migration file with a user-specified version and description. The file is created in the 'migrations/' directory with the format V<version>__<description>.sql. If the version is omitted, a timestamp is used.

```bash
jetbase new "description of the migration" -v <version>
```

--------------------------------

### Python Configuration (env.py)

Source: https://context7.com/jetbase-hq/jetbase/llms.txt

Configures database connections and validation settings using the env.py file. Supports various database URLs (PostgreSQL, SQLite, Snowflake) and allows specifying a custom schema. Validation flags can be set to skip specific checks.

```python
# jetbase/env.py

# PostgreSQL connection
sal={ "alchemy_url" } = "postgresql+psycopg2://user:password@localhost:5432/mydb"

# Optional: Specify PostgreSQL schema (defaults to 'public')
postgres_schema = "my_schema"

# SQLite connection (alternative)
# sqlalchemy_url = "sqlite:///mydb.db"

# In-memory SQLite for testing
# sqlalchemy_url = "sqlite:///:memory:"

# Snowflake connection with password
# sqlalchemy_url = "snowflake://user:password@account.us-east-1/database/schema?warehouse=COMPUTE_WH"

# Snowflake with key pair auth (password omitted)
# sqlalchemy_url = "snowflake://user@account.us-east-1/database/schema?warehouse=COMPUTE_WH"

# Validation settings (all default to False)
skip_validation = False           # Skip all validations
skip_checksum_validation = False  # Skip checksum validation only
skip_file_validation = False      # Skip file validation only
```

--------------------------------

### SQL Migration without Explicit Rollback

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/migrations/index.md

An example of a Jetbase SQL migration file where only the upgrade statements are provided. If a `-- rollback` section is omitted, all statements are treated as upgrade statements.

```sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE
);

-- items table
CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
);
```

--------------------------------

### Write SQL Migration with Upgrade and Rollback

Source: https://github.com/jetbase-hq/jetbase/blob/main/README.md

Write SQL statements for database schema changes within a migration file. Use '-- upgrade' to define the changes to apply and '-- rollback' to define how to revert them.

```sql
-- upgrade
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- rollback
DROP TABLE items;
DROP TABLE users;

```

--------------------------------

### Jetbase env.py Configuration File (Python)

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/commands/init.md

The 'env.py' file is generated by 'jetbase init' and contains the database connection string for your Jetbase project. You must update the 'sqlalchemy_url' variable with your actual database credentials.

```python
# Jetbase Configuration
# Update the sqlalchemy_url with your database connection string.

soundcloud_url = "postgresql://user:password@localhost:5432/mydb"

```

--------------------------------

### Environment Variables Configuration

Source: https://context7.com/jetbase-hq/jetbase/llms.txt

Sets Jetbase configuration parameters using environment variables, offering an alternative to the env.py file. This includes database connection strings, schema, validation settings, and Snowflake key pair authentication details.

```bash
# Database connection
export JETBASE_SQLALCHEMY_URL="postgresql+psycopg2://user:password@localhost:5432/mydb"

# PostgreSQL schema
export JETBASE_POSTGRES_SCHEMA="my_schema"

# Validation settings
export JETBASE_SKIP_VALIDATION="False"
export JETBASE_SKIP_CHECKSUM_VALIDATION="False"
export JETBASE_SKIP_FILE_VALIDATION="False"

# Snowflake key pair authentication
export JETBASE_SNOWFLAKE_PRIVATE_KEY=$(cat snowflake_private_key.pem)
export JETBASE_SNOWFLAKE_PRIVATE_KEY_PASSWORD="your-key-password"
```

--------------------------------

### TOML Configuration

Source: https://context7.com/jetbase-hq/jetbase/llms.txt

Configures Jetbase settings in either jetbase.toml or pyproject.toml files using the TOML format. This includes database connection URLs, schema specification, and validation behavior.

```toml
# jetbase/jetbase.toml
sal={ "alchemy_url" } = "postgresql://user:password@localhost:5432/mydb"
postgres_schema = "public"
skip_validation = false
skip_checksum_validation = false
skip_file_validation = false

# Or in pyproject.toml
[tool.jetbase]
sal={ "alchemy_url" } = "postgresql://user:password@localhost:5432/mydb"
postgres_schema = "public"

```

--------------------------------

### Load Snowflake Private Key from File

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/database-connections.md

Provides a bash command to read a Snowflake private key from a file and export it as an environment variable. This is a convenient way to manage keys locally.

```bash
export JETBASE_SNOWFLAKE_PRIVATE_KEY=$(cat snowflake_private_key.pem)
```

--------------------------------

### Preview Rollback (Dry Run) (Jetbase CLI)

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/commands/rollback.md

Simulates the rollback process without actually executing any SQL commands. The `--dry-run` option shows which migrations would be rolled back and the SQL that would be executed.

```bash
# Preview rolling back the last migration
jetbase rollback --dry-run
```

--------------------------------

### Configure Snowflake Private Key Environment Variable

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/database-connections.md

Shows how to set the Snowflake private key as an environment variable, which is required for key pair authentication. The private key should be in PEM format.

```bash
# Set the private key (PEM format)
export JETBASE_SNOWFLAKE_PRIVATE_KEY="-----BEGIN PRIVATE KEY-----
MIIEvgIBADANBgkqhkiG9w0BAQEFAASC...
-----END PRIVATE KEY-----"

# Optional: if your private key is encrypted
export JETBASE_SNOWFLAKE_PRIVATE_KEY_PASSWORD="your-key-password"
```

--------------------------------

### Apply Pending Database Migrations

Source: https://context7.com/jetbase-hq/jetbase/llms.txt

The `jetbase upgrade` command applies pending SQL migrations to your database. Options allow applying a specific count of migrations, upgrading to a particular version, or performing a dry run to preview changes without execution. Validation checks can be skipped if necessary.

```bash
# Apply all pending migrations
jetbase upgrade
# Output: Migration applied successfully: V1__create_users_table.sql

# Apply only the next 2 migrations
jetbase upgrade --count 2

# Apply migrations up to and including version 5
jetbase upgrade --to-version 5

# Preview changes without applying them (dry run)
jetbase upgrade --dry-run
# Output:
# === DRY RUN MODE ===
# The following migrations would be applied:
# --- V1__create_users_table.sql ---
# CREATE TABLE users (
#     id SERIAL PRIMARY KEY,
#     username VARCHAR(50) NOT NULL
# );
# === END DRY RUN ===

# Skip validation checks (use with caution)
jetbase upgrade --skip-validation
jetbase upgrade --skip-checksum-validation
jetbase upgrade --skip-file-validation
```

--------------------------------

### Execute jetbase upgrade command

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/commands/upgrade.md

This snippet shows the basic usage of the 'jetbase upgrade' command to apply all pending database migrations. It's the standard way to keep your database schema up-to-date.

```bash
jetbase upgrade
```

--------------------------------

### Snowflake Key Pair Authentication Connection String

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/database-connections.md

Demonstrates setting up a Snowflake connection string for key pair authentication. The password is omitted, and the private key is configured via environment variables.

```python
# jetbase/env.py
ssqlalchemy_url = "snowflake://myuser@myaccount.us-east-1/my_db/public?warehouse=COMPUTE_WH"
```

--------------------------------

### Combine Rollback Options (Jetbase CLI)

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/commands/rollback.md

Allows combining options such as `--count` and `--dry-run` to preview the rollback of a specific number of migrations.

```bash
# Dry-run rolling back 2 migrations
jetbase rollback --count 2 --dry-run
```

--------------------------------

### Create New SQL Migration File

Source: https://context7.com/jetbase-hq/jetbase/llms.txt

Generate new SQL migration files using `jetbase new`. You can specify a version number and a descriptive name, or let Jetbase auto-generate a timestamped version. The generated file contains `-- upgrade` and `-- rollback` sections for you to define SQL statements.

```bash
# Create a migration with a specific version number
jetbase new "create users table" -v 1
# Output: Created migration file: V1__create_users_table.sql

# Create a migration with auto-generated timestamp version
jetbase new "add email column"
# Output: Created migration file: V20251225.143022__add_email_column.sql

# The generated file (V1__create_users_table.sql) should be edited:
# -- upgrade
# CREATE TABLE users (
#     id SERIAL PRIMARY KEY,
#     name VARCHAR(100) NOT NULL,
#     email VARCHAR(255) UNIQUE NOT NULL
# );
#
# -- rollback
# DROP TABLE IF EXISTS users;
```

--------------------------------

### Migration File SQL Format

Source: https://context7.com/jetbase-hq/jetbase/llms.txt

Migration files are written in SQL and must include an `upgrade` section for applying changes and a `rollback` section for reverting them. These sections are separated by a `-- rollback` comment. The filename typically follows the pattern `V{version}__{description}.sql`.

```sql
-- V1__create_users_and_orders.sql

-- upgrade
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_orders_user_id ON orders(user_id);

-- rollback
DROP INDEX IF EXISTS idx_orders_user_id;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS users;

```

--------------------------------

### Skip Jetbase Validations via Command Line

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/validations/index.md

Allows skipping specific or all validations during the 'jetbase upgrade' process. Use with caution as it can lead to inconsistent database states. Options include skipping all validations, only file validation, or only checksum validation.

```bash
# Skip all validations
jetbase upgrade --skip-validation

# Skip only file validation
jetbase upgrade --skip-file-validation

# Skip only checksum validation
jetbase upgrade --skip-checksum-validation
```

--------------------------------

### Configure Snowflake Private Key via Environment Variable

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/configuration.md

Sets the JETBASE_SNOWFLAKE_PRIVATE_KEY environment variable for Snowflake key pair authentication. It demonstrates how to load a private key from a file.

```bash
export JETBASE_SNOWFLAKE_PRIVATE_KEY="-----BEGIN PRIVATE KEY-----
MIIEvQIBADANBgkqhkiG9w0BAQEFA...
-----END PRIVATE KEY-----"
```

```bash
export JETBASE_SNOWFLAKE_PRIVATE_KEY=$(cat snowflake_private_key.pem)
```

--------------------------------

### Apply migrations up to a specific version with jetbase upgrade

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/commands/upgrade.md

This snippet shows how to use the `--to-version` option with 'jetbase upgrade' to apply all migrations up to and including a particular version. This is helpful for targeting a known stable schema version.

```bash
jetbase upgrade --to-version 5
```

--------------------------------

### PostgreSQL Connection String with Schema

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/database-connections.md

Shows how to specify a particular schema within the PostgreSQL connection string. This is useful when working with non-default schemas.

```python
# jetbase/env.py
ssqlalchemy_url = "postgresql://myuser:mypassword@localhost:5432/myapp"
postgres_schema = "public"
```

--------------------------------

### Check Migration Status and History

Source: https://context7.com/jetbase-hq/jetbase/llms.txt

The `jetbase status` command provides a clear overview of your database migration status. It displays tables listing both applied migrations and those that are pending, including their version numbers and descriptions.

```bash
jetbase status

# Output:
#                     Migrations Applied
# +-------------------+--------------------------------------+
# | Version           | Description                          |
# +-------------------+--------------------------------------+
# | 1                 | create_users_table                   |
# | 2                 | add_email_to_users                   |
# 
```

--------------------------------

### Show Database Migration History (Bash)

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/commands/history.md

The `jetbase history` command displays a detailed table of all migrations applied to the database. It includes the migration version, order executed, description, and the timestamp when it was applied. This command is useful for auditing and understanding database schema evolution.

```bash
jetbase history
```

--------------------------------

### Repeatable Migrations: Always Run

Source: https://context7.com/jetbase-hq/jetbase/llms.txt

Migrations marked as 'Runs Always' execute on every upgrade. This is suitable for tasks like refreshing materialized views or updating permissions that should be consistently applied.

```sql
-- RA__refresh_materialized_views.sql

-- upgrade
REFRESH MATERIALIZED VIEW CONCURRENTLY user_statistics;
REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary;

-- Grant permissions
GRANT SELECT ON user_statistics TO readonly_role;
GRANT SELECT ON order_summary TO readonly_role;

-- rollback
-- Usually no rollback needed for RA migrations
```

--------------------------------

### Override Validation via Command-Line Flags

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/configuration.md

Demonstrates how to override validation settings directly from the command line when running Jetbase commands.

```bash
# Skip all validation
jetbase upgrade --skip-validation
```

```bash
# Skip only checksum validation
jetbase upgrade --skip-checksum-validation
```

```bash
# Skip only file validation
jetbase upgrade --skip-file-validation
```

--------------------------------

### Repeatable Migrations: Run On Change

Source: https://context7.com/jetbase-hq/jetbase/llms.txt

Migrations marked as 'Runs On Change' execute only when their file content has changed since the last migration. This is ideal for stored procedures, functions, or triggers that need to be updated based on code modifications.

```sql
-- ROC__order_functions.sql

-- upgrade
CREATE OR REPLACE FUNCTION calculate_order_total(order_id INTEGER)
RETURNS DECIMAL AS $$
BEGIN
    RETURN (
        SELECT SUM(price * quantity)
        FROM order_items
        WHERE order_items.order_id = $1
    );
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- rollback
DROP FUNCTION IF EXISTS update_timestamp();
DROP FUNCTION IF EXISTS calculate_order_total(INTEGER);
```

--------------------------------

### Jetbase Validation Check Commands

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/validations/index.md

Provides commands to independently verify database validations without performing migrations. These commands check for checksum mismatches and file integrity, with options to fix detected issues.

```bash
# Check for checksum mismatches
jetbase validate-checksums

# Fix checksum mismatches by updating stored checksums
jetbase validate-checksums --fix

# Check for missing or out-of-order files
jetbase validate-files

# Alias for validate-checksums --fix
jetbase fix-checksums
```

--------------------------------

### Custom Delimiter for SQL Stored Procedures

Source: https://context7.com/jetbase-hq/jetbase/llms.txt

Allows custom delimiters (e.g., '~') for SQL statements within migration files, particularly useful for stored procedures or functions containing semicolons. The delimiter is specified using the '-- jetbase: delimiter=' directive.

```sql
-- jetbase: delimiter=~

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL,
    updated_at TIMESTAMP
);~

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;~

CREATE TRIGGER users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();~

-- rollback
DROP TRIGGER IF EXISTS users_updated_at ON users;
DROP FUNCTION IF EXISTS set_updated_at();
DROP TABLE IF EXISTS users;~
```

--------------------------------

### Configure Snowflake Private Key Password via Environment Variable

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/configuration.md

Sets the SNOWFLAKE_PRIVATE_KEY_PASSWORD environment variable to provide the password for an encrypted Snowflake private key.

```bash
export SNOWFLAKE_PRIVATE_KEY_PASSWORD=my-secret-password
```

--------------------------------

### Create Migrations using Jetbase CLI

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/migrations/index.md

Generate new migration files using the Jetbase command-line interface. You can specify a version number or let it default to a timestamp-based version. This command ensures properly formatted migration files.

```bash
jetbase new "create users table" -v 1

# if you want a timestamp-based version, do not specify a version
jetbase new "create users table"
```

--------------------------------

### Check Migration Status (Jetbase CLI)

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/commands/rollback.md

Displays the current state of applied and pending migrations. This command is useful for understanding the migration history before performing a rollback.

```bash
jetbase status
```

--------------------------------

### Jetbase Runs On Change (ROC) Migration File Naming Convention

Source: https://github.com/jetbase-hq/jetbase/blob/main/docs/commands/upgrade.md

This shows the naming convention for 'Runs On Change' migrations in Jetbase. These migrations are executed only when their file content has been modified since the last upgrade.

```plaintext
ROC__update_view.sql
```

--------------------------------

### Rollback Applied Database Migrations

Source: https://context7.com/jetbase-hq/jetbase/llms.txt

Undo previously applied migrations using `jetbase rollback`. This command executes the `-- rollback` SQL statements in reverse order. You can specify the number of migrations to roll back, roll back to a specific version, or perform a dry run to preview the rollback.

```bash
# Roll back the last migration (default behavior)
jetbase rollback
# Output: Rollback applied successfully: V10__add_email_to_users.sql

# Roll back the last 3 migrations
jetbase rollback --count 3
# Output:
# Rollback applied successfully: V10__add_email_to_users.sql
# Rollback applied successfully: V9__add_index_on_users.sql
# Rollback applied successfully: V8__create_users_table.sql

# Roll back everything after version 5 (version 5 remains applied)
jetbase rollback --to-version 5

# Preview rollback without executing
jetbase rollback --dry-run
# Output:
# === DRY RUN MODE ===
# The following migrations would be rolled back:
# --- V10__add_email_to_users.sql ---
# ALTER TABLE users DROP COLUMN email;
# === END DRY RUN ===
```