Database Operations

This section covers the core database management commands in SyndrDB: creating new databases, renaming existing databases, and switching the active database context.

Table of Contents

CREATE DATABASE

Creates a new database in the SyndrDB system.

Syntax

CREATE DATABASE "database_name"

Components

Component Description Required
database_name Name of the database to create Yes

Naming Rules

Database names must follow these rules:

  • Start with a letter (a-z, A-Z)
  • Can contain alphanumeric characters (a-z, A-Z, 0-9)
  • Can contain underscores (_) and hyphens (-)
  • Cannot start with numbers or special characters
  • Cannot contain spaces (use underscores instead)

Examples

Valid Database Names

-- Simple database name
CREATE DATABASE "MyApp"

-- Database with underscores
CREATE DATABASE "my_application_db"

-- Database with hyphens
CREATE DATABASE "app-production"

-- Alphanumeric with version
CREATE DATABASE "AppV2"

Invalid Database Names

-- Cannot start with number
CREATE DATABASE "2024_app"  -- Error!

-- Cannot contain spaces
CREATE DATABASE "My App"  -- Error!

-- Must use quotes
CREATE DATABASE MyApp  -- Error! (quotes required)

Permissions

  • Required Permission: Admin
  • Only users with Admin permissions can create databases
  • When user authentication is implemented, this will be enforced

Behavior

  1. Validation: Checks if database name is valid according to naming rules
  2. Existence Check: Verifies database doesn't already exist
  3. Creation: Creates database directory structure and metadata
  4. Catalog Registration: Registers database in system catalog
  5. Confirmation: Returns success message with database name

Error Cases

Error Cause Solution
database already exists Database with that name exists Use a different name or drop existing database
invalid database name Name violates naming rules Follow naming rules (start with letter, alphanumeric + _ -)
catalog registration failed Database created but not registered Check system catalog integrity

Best Practices

DO:

  • Use descriptive names that reflect the database purpose
  • Use consistent naming conventions (e.g., snake_case or kebab-case)
  • Include environment in name for multiple deployments (e.g., "app-dev", "app-prod")

DON'T:

  • Don't use overly long names (keep it under 50 characters)
  • Don't use special characters beyond _ and -
  • Don't create databases without a clear purpose

Complete Example

-- Create a new application database
CREATE DATABASE "user_management_system"
-- Response: Database 'user_management_system' created successfully.

-- Attempt to create duplicate (will fail)
CREATE DATABASE "user_management_system"
-- Error: database 'user_management_system' already exists

-- Create environment-specific databases
CREATE DATABASE "analytics-dev"
CREATE DATABASE "analytics-staging"
CREATE DATABASE "analytics-prod"

RENAME DATABASE

Renames an existing database, updating all references, file paths, and active sessions.

Syntax

-- Basic rename (fails if active sessions exist)
RENAME DATABASE "old_name" TO "new_name";

-- Force rename (terminates active sessions)
RENAME DATABASE "old_name" TO "new_name" FORCE;

Components

Component Description Required
old_name Current name of the database Yes
new_name New name for the database Yes
FORCE Force flag to terminate active sessions No

Naming Rules

New database names must follow the same rules as CREATE DATABASE:

  • Start with a letter (a-z, A-Z)
  • Can contain alphanumeric characters (a-z, A-Z, 0-9)
  • Can contain underscores (_) and hyphens (-)
  • Cannot start with numbers or special characters
  • Cannot contain spaces
  • Cannot be the same as the old name

Examples

Basic Rename

-- Rename a database (no active sessions)
RENAME DATABASE "old_app" TO "new_app";
-- Response: Database 'old_app' renamed to 'new_app' successfully.

Force Rename with Active Sessions

-- Attempt rename with active sessions (will fail)
RENAME DATABASE "production_db" TO "prod_v2";
-- Error: Cannot rename database 'production_db': 5 active session(s) detected.
-- Use FORCE to terminate sessions and proceed.

-- Force rename (terminates sessions)
RENAME DATABASE "production_db" TO "prod_v2" FORCE;
-- Response: Database 'production_db' renamed to 'prod_v2' successfully.
-- 5 session(s) were terminated.

Rename with Environment Migration

-- Rename for environment promotion
RENAME DATABASE "app-staging" TO "app-staging-old";
RENAME DATABASE "app-dev" TO "app-staging";

-- Rename for version upgrade
RENAME DATABASE "analytics_v1" TO "analytics_v1_archive";
RENAME DATABASE "analytics_v2_beta" TO "analytics_v2";

Permissions

  • Required Permission: Admin
  • Only users with Admin permissions can rename databases
  • This is strictly enforced to prevent unauthorized database modifications

Behavior

The RENAME DATABASE operation performs the following steps atomically:

  1. Permission Check: Verifies user has Admin permission
  2. Validation:
    • Confirms old database exists
    • Validates new database name
    • Ensures new name isn't already in use
    • Verifies old and new names are different
  3. Session Detection: Checks for active sessions using the database
  4. Session Handling:
    • Without FORCE: Fails if active sessions exist
    • With FORCE: Terminates all active sessions on the database
  5. Buffer Flush: Flushes all buffers to disk to ensure data consistency
  6. Directory Rename: Atomically renames the database directory using os.Rename
  7. In-Memory Update: Updates database service's internal mappings
  8. Catalog Update: Updates primary.Databases bundle with new name and file path
  9. WAL Logging: Logs the operation to Write-Ahead Log with:
    • Transaction ID
    • Old database name
    • New database name
    • Database ID
    • FORCE flag status
    • Number of sessions terminated
    • Timestamp
  10. Session Update: Updates remaining sessions to reference new database name

Error Cases

Error Cause Solution
permission denied User lacks Admin permission Use an account with Admin privileges
database not found Old database doesn't exist Verify database name with SHOW DATABASES
database already exists New name is already in use Choose a different name
invalid database name New name violates naming rules Follow naming rules (start with letter, alphanumeric + _ -)
active sessions detected Sessions exist without FORCE Add FORCE flag or wait for sessions to close
same database name Old and new names are identical Choose a different new name
rename failed Filesystem error during rename Check permissions and disk space
catalog update failed Rename succeeded but catalog update failed Database is renamed; catalog may need manual repair

FORCE Flag Details

The FORCE flag provides powerful session management:

Behavior:

  • Terminates ALL active sessions using the database
  • Returns count of terminated sessions in response
  • Allows rename to proceed immediately
  • Active queries are interrupted
  • Users lose unsaved work in terminated sessions

Use Cases for FORCE:

  • Emergency database renaming
  • Automated deployment scripts
  • Maintenance windows with known active sessions
  • Development/testing environments

When NOT to use FORCE:

  • Production databases with user traffic (unless planned maintenance)
  • When data integrity of in-flight transactions matters
  • Without notifying affected users

Safety Features

The rename operation includes several safety mechanisms:

  1. Atomic Rename: Uses OS-level atomic rename operation
  2. WAL Logging: Full operation logged before execution
  3. Rollback on Failure: Partial failures don't leave database in inconsistent state
  4. Catalog Consistency: Both database and catalog are updated
  5. Session Validation: All active sessions are properly tracked
  6. Buffer Flushing: Ensures all data is persisted before rename

Best Practices

DO:

  • Use FORCE during planned maintenance windows
  • Notify users before forcing session termination
  • Verify no critical operations are running before FORCE
  • Test rename operations in development first
  • Use consistent naming conventions (e.g., app-v2, not app_v2)
  • Document reason for rename in change logs

DON'T:

  • Don't use FORCE on production without warning users
  • Don't rename databases during peak usage
  • Don't forget to update application configuration
  • Don't rename system databases (like primary)
  • Don't use special characters beyond _ and -

Complete Example

-- Step 1: Check current databases
SHOW DATABASES;
-- Response: ["primary", "old_analytics", "user_service"]

-- Step 2: Attempt basic rename
RENAME DATABASE "old_analytics" TO "analytics_v2";
-- Error: Cannot rename database 'old_analytics': 3 active session(s) detected.

-- Step 3: Use FORCE to proceed
RENAME DATABASE "old_analytics" TO "analytics_v2" FORCE;
-- Response: Database 'old_analytics' renamed to 'analytics_v2' successfully.
-- 3 session(s) were terminated.

-- Step 4: Verify rename
SHOW DATABASES;
-- Response: ["primary", "analytics_v2", "user_service"]

-- Step 5: Switch to renamed database
USE "analytics_v2";
-- Response: Switched to database 'analytics_v2'

-- Step 6: Verify data integrity
SELECT * FROM BUNDLE "Events" LIMIT 10;
-- Data should be intact after rename

Monitoring and Verification

After a rename operation, verify:

-- 1. Database exists with new name
SHOW DATABASES;

-- 2. Can switch to new database
USE "new_database_name";

-- 3. Bundles are accessible
SHOW BUNDLES;

-- 4. Data is intact
SELECT COUNT(*) FROM BUNDLE "YourBundle";

WAL Entry Example

When a rename occurs, the WAL contains:

{
  "transaction_id": "a1b2c3d4e5f6g7h8",
  "operation": "RENAME_DATABASE",
  "timestamp": "2025-11-21T18:30:00Z",
  "old_name": "old_analytics",
  "new_name": "analytics_v2",
  "database_id": "db-uuid-12345",
  "force": true,
  "sessions_terminated": 3
}

USE DATABASE

Switches the active database context for subsequent operations. All commands after USE will execute against the specified database.

Syntax

USE "database_name";

Components

Component Description Required
database_name Name of the database to switch to Yes

Examples

Basic Usage

-- Switch to a database
USE "MyApp";

-- Now all commands use MyApp database
CREATE BUNDLE "Users" WITH FIELDS (
    {"id", STRING, true, true},
    {"name", STRING, true, false}
);

Switching Between Databases

-- Start with one database
USE "analytics-dev";
SELECT * FROM BUNDLE "Events";

-- Switch to another database
USE "analytics-prod";
SELECT * FROM BUNDLE "Events";  -- Now querying prod database

Behavior

  1. Validation: Checks if database exists in loaded databases
  2. Catalog Check: Verifies database is registered in system catalog
  3. Context Switch: Sets the active database for the session
  4. Confirmation: Returns success message

Error Cases

Error Cause Solution
database not found Database doesn't exist or isn't loaded Use SHOW DATABASES to see available databases
catalog verification failed Database exists but not in catalog Check catalog integrity
invalid syntax Missing quotes or incorrect format Use format: USE "database_name";

Database Context

The USE command sets the session-level database context:

  • All subsequent bundle/document operations use this database
  • The context persists until changed by another USE command
  • Each connection/session maintains its own database context

Best Practices

DO:

  • Always use USE before working with bundles or documents
  • Verify you're in the correct database before destructive operations
  • Use SHOW DATABASES to list available databases

DON'T:

  • Don't assume you're in the right database - always verify
  • Don't switch databases in the middle of a transaction (when transactions are implemented)

Complete Example

-- List available databases
SHOW DATABASES;

-- Switch to development database
USE "app-dev";

-- Create some test data
ADD DOCUMENT TO BUNDLE "Users"
WITH VALUES ({"id": "1", "name": "Test User"});

-- Switch to production database
USE "app-prod";

-- Now working with production data
SELECT * FROM BUNDLE "Users";

Last updated: March 2026