Examples

This section provides practical examples of using Dubnium for common database management scenarios.

Getting Started Example

1. Setting up a new project

# Create a new directory for your database project
mkdir MyAppDatabase
cd MyAppDatabase

# Initialize Dubnium configuration
dbm init

# This creates dubnium.yml with default settings

2. Configure your database

Edit the generated dubnium.yml:

ProjectName: 'MyApp Database'
MigrationPath: 'migrations'
DatabaseOne:
  ConnectionString: 'Server=localhost;Database=MyApp_Dev;Integrated Security=true;TrustServerCertificate=true'
  Name: 'Development Database'
TempPath: 'temp'
Verbose: true

3. Set up the database environment

# Create the migrations directory
mkdir migrations

# Setup Dubnium system tables in your database
dbm setup

Migration Script Management

Creating and organizing migration scripts

# Create a new migration script
dbm new

This creates a file like: 001-main-InitialSchema.sql

Example migration script structure:

-- File: 001-main-CreateUsersTable.sql
-- Description: Create the Users table with basic authentication fields

BEGIN TRANSACTION;

CREATE TABLE Users (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Username NVARCHAR(50) NOT NULL UNIQUE,
    Email NVARCHAR(255) NOT NULL UNIQUE,
    PasswordHash NVARCHAR(255) NOT NULL,
    CreatedDate DATETIME2 DEFAULT GETUTCDATE(),
    LastLoginDate DATETIME2 NULL,
    IsActive BIT DEFAULT 1
);

CREATE INDEX IX_Users_Username ON Users(Username);
CREATE INDEX IX_Users_Email ON Users(Email);

COMMIT TRANSACTION;

Sequential script example

-- File: 002-main-CreateProductsTable.sql
-- Description: Add Products table for inventory management

BEGIN TRANSACTION;

CREATE TABLE Products (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL,
    Description NVARCHAR(500),
    Price DECIMAL(10,2) NOT NULL,
    CategoryId INT,
    CreatedDate DATETIME2 DEFAULT GETUTCDATE(),
    ModifiedDate DATETIME2 DEFAULT GETUTCDATE()
);

COMMIT TRANSACTION;

Database Comparison Workflow

Comparing development and production

1. Configure both databases in dubnium.yml:

DatabaseOne:
  ConnectionString: 'Server=dev-server;Database=MyApp_Dev;Integrated Security=true;'
  Name: 'Development'
DatabaseTwo:
  ConnectionString: 'Server=prod-server;Database=MyApp_Prod;User Id=produser;Password=pass;'
  Name: 'Production'

2. Compare the databases:

# Compare development (DatabaseOne) with production (DatabaseTwo)
dbm compareone

# Set development as the source schema
dbm setsource

# Compare production against the source
dbm comparetwo

Data Export Example

Configuring and running data export

1. Configure tables to export in dubnium.yml:

TablesToDump:
  - 'Users'
  - 'Products'
  - 'Categories'
  - 'Settings'
  - 'Roles'

2. Export the data:

dbm dumpdata

3. Result: CSV files created in temp/DataDump/:
   • data-Users.csv
   • data-Products.csv
   • data-Categories.csv
   • data-Settings.csv
   • data-Roles.csv

CI/CD Integration Example

Azure DevOps Pipeline

# azure-pipelines.yml
trigger:
  branches:
    include:
      - main
      - develop

pool:
  vmImage: 'windows-latest'

steps:
- task: UseDotNet@2
  displayName: 'Install .NET'
  inputs:
    packageType: 'sdk'
    version: '8.x'

- script: |
    dotnet tool install --global dubnium-cli
  displayName: 'Install Dubnium CLI'

- script: |
    dbm verify
  displayName: 'Verify Configuration'
  workingDirectory: '$(Build.SourcesDirectory)/database'

- script: |
    dbm test
  displayName: 'Test Database Connection'
  workingDirectory: '$(Build.SourcesDirectory)/database'

- script: |
    dbm build
  displayName: 'Build Database'
  workingDirectory: '$(Build.SourcesDirectory)/database'
  condition: and(succeeded(), eq(variables['Build.SourceBranch'], 'refs/heads/main'))

GitHub Actions

# .github/workflows/database.yml
name: Database CI/CD

on:
  push:
    branches: [ main, develop ]
  pull_request:
    branches: [ main ]

jobs:
  database:
    runs-on: windows-latest
    
    steps:
    - uses: actions/checkout@v3
    
    - name: Setup .NET
      uses: actions/setup-dotnet@v3
      with:
        dotnet-version: '8.x'
    
    - name: Install Dubnium CLI
      run: dotnet tool install --global dubnium-cli
    
    - name: Verify Configuration
      run: dbm verify
      working-directory: ./database
    
    - name: Test Database Connection
      run: dbm test
      working-directory: ./database
    
    - name: Build Database (Production)
      if: github.ref == 'refs/heads/main'
      run: dbm build
      working-directory: ./database

Development Workflow Example

Daily development routine

1. Start of day - verify environment:

cd MyAppDatabase
dbm verify
dbm test

2. Create new feature migration:

# Create new migration for user profiles feature
dbm new
# Edit the generated script file

3. Test changes locally:

# Apply changes to development database
dbm build

4. Compare with other environments:

# Compare local changes with staging
dbm compareone

5. Export reference data if needed:

dbm dumpdata

Troubleshooting Examples

Common scenarios and solutions

Connection Issues

# Test connections first
dbm test

# Enable verbose logging for details
# Edit dubnium.yml: Verbose: true
dbm verify

Script Sequence Issues

# Verify all scripts and sequence
dbm verify

# Clean temporary files if needed
dbm clean

Permission Problems

Ensure your database user has the following minimum permissions:

-- For setup and build operations
GRANT CREATE TABLE TO [dubnium_user];
GRANT ALTER TO [dubnium_user];
GRANT SELECT, INSERT, UPDATE, DELETE TO [dubnium_user];

-- For schema comparison
GRANT VIEW DEFINITION TO [dubnium_user];

Project Structure Example

MyAppDatabase/
├── dubnium.yml                 # Configuration file
├── migrations/                 # Migration scripts directory
│   ├── 001-main-InitialSchema.sql
│   ├── 002-main-CreateUsers.sql
│   ├── 003-feature-AddProducts.sql
│   └── 004-bugfix-FixUserIndex.sql
├── temp/                      # Temporary files
│   └── DataDump/             # Exported data files
├── docs/                     # Project documentation
└── README.md                 # Project readme