Database, SSH, Filesystem, Process, and Infrastructure Automation Patterns: Guided Hands-On Workflow

1. Scenario and state map

The lab represents a small release-readiness check. A run creates an SQLite inventory, writes one marker file, invokes one harmless child process, and exercises the shape of a remote read-only command through a simulation. Every resource is either inside ${OUTPUTDIR}/scratch-rf18 or exists only as a child process for the duration of one keyword.

rf18-infra-lab/
├── resources/
│   └── infra.resource
├── tests/
│   └── infrastructure.robot
└── results/
    └── <run-specific output>/
        └── scratch-rf18/
            ├── inventory.sqlite3
            └── marker.txt

2. Preflight and installation

# Create/activate your isolated environment first.
python -m pip install "robotframework==7.4.2" "robotframework-databaselibrary==2.4.1"
python --version
python -m robot --version
python -m pip show robotframework-databaselibrary
python -c "import sqlite3; print('sqlite', sqlite3.sqlite_version)"

SSHLibrary is intentionally not required. If you install it for optional exploration, pin and record the version separately; do not let it change the mandatory lab dependency graph.

3. Build the domain infrastructure resource

*** Settings ***
Library    DatabaseLibrary
Library    OperatingSystem
Library    Process
Library    Collections

*** Variables ***
${SCRATCH_NAME}       scratch-rf18
${DB_NAME}            inventory.sqlite3
${MARKER_NAME}        marker.txt
${OWNED_ROOT}         ${False}
${DB_ALIAS}           labdb

*** Keywords ***
Prepare Owned Infrastructure Fixture
    ${root}=    Normalize Path    ${OUTPUTDIR}${/}${SCRATCH_NAME}
    VAR    ${LAB_ROOT}    ${root}    scope=SUITE
    ${db}=    Normalize Path    ${LAB_ROOT}${/}${DB_NAME}
    VAR    ${DB_PATH}    ${db}    scope=SUITE
    ${exists}=    Run Keyword And Return Status    Directory Should Exist    ${LAB_ROOT}
    Should Be Equal    ${exists}    ${False}    msg=Refusing to reuse pre-existing scratch directory: ${LAB_ROOT}
    Create Directory    ${LAB_ROOT}
    VAR    ${OWNED_ROOT}    ${True}    scope=SUITE
    ${python}=    Evaluate    sys.executable    modules=sys
    VAR    ${PYTHON}    ${python}    scope=SUITE
    Connect To Database    sqlite3    database=${DB_PATH}    alias=${DB_ALIAS}
    Execute Sql String    CREATE TABLE inventory (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, state TEXT NOT NULL)    alias=${DB_ALIAS}

Disconnect Lab Database
    Disconnect From Database    error_if_no_connection=${False}    alias=${DB_ALIAS}

Guarded Cleanup
    Disconnect Lab Database
    ${actual}=    Normalize Path    ${LAB_ROOT}
    ${allowed}=    Normalize Path    ${OUTPUTDIR}${/}${SCRATCH_NAME}
    Should Be Equal    ${actual}    ${allowed}    msg=Cleanup guard rejected path: ${actual}
    IF    $OWNED_ROOT
        Remove Directory    ${LAB_ROOT}    recursive=${True}
        VAR    ${OWNED_ROOT}    ${False}    scope=SUITE
    END

Insert Inventory Row
    [Arguments]    ${name}    ${state}
    ${params}=    Create List    ${name}    ${state}
    Execute Sql String    INSERT INTO inventory(name, state) VALUES (?, ?)    parameters=${params}    alias=${DB_ALIAS}

Get Inventory Row
    [Arguments]    ${name}
    ${params}=    Create List    ${name}
    ${rows}=    Query    SELECT id, name, state FROM inventory WHERE name = ?    parameters=${params}    return_dict=${True}    alias=${DB_ALIAS}
    RETURN    ${rows}

Write Owned Marker
    [Arguments]    ${content}
    ${path}=    Normalize Path    ${LAB_ROOT}${/}${MARKER_NAME}
    Create File    ${path}    ${content}    encoding=UTF-8
    File Should Exist    ${path}
    RETURN    ${path}

Run Harmless Child
    ${code}=    Catenate    SEPARATOR=;    import json    print(json.dumps({'worker':'rf18','status':'ok'}))
    ${result}=    Run Process    ${PYTHON}    -c    ${code}
    Should Be Equal As Integers    ${result.rc}    0
    Should Be Empty    ${result.stderr}
    RETURN    ${result}

Simulate Read Only SSH Contract
    ${code}=    Catenate    SEPARATOR=;    import json    print(json.dumps({'target':'simulated-loopback','command':'hostname','rc':0}))
    ${result}=    Run Process    ${PYTHON}    -c    ${code}
    Should Be Equal As Integers    ${result.rc}    0
    RETURN    ${result}

Read the resource top to bottom as ownership contracts. Setup calculates paths, refuses reuse, creates the directory, records ownership, captures the exact current Python executable, connects SQLite, and creates a schema. Teardown disconnects first, then proves the normalized cleanup path equals the one this suite was allowed to create, and only then performs recursive deletion.

4. Run the progressive suite

*** Settings ***
Resource         ../resources/infra.resource
Suite Setup      Prepare Owned Infrastructure Fixture
Suite Teardown   Guarded Cleanup

*** Test Cases ***
Database File And Row Are Owned By This Run
    File Should Exist    ${DB_PATH}
    Insert Inventory Row    alpha    ready
    ${rows}=    Get Inventory Row    alpha
    Length Should Be    ${rows}    1
    Should Be Equal    ${rows}[0][name]     alpha
    Should Be Equal    ${rows}[0][state]    ready

Filesystem Mutation Stays Inside Scratch Root
    ${marker}=    Write Owned Marker    owner=rf18\nstate=ready\n
    ${text}=    Get File    ${marker}    encoding=UTF-8
    Should Contain    ${text}    owner=rf18
    Should Contain    ${text}    state=ready

Child Process Returns Structured Evidence
    ${result}=    Run Harmless Child
    Should Contain    ${result.stdout}    "worker": "rf18"
    Should Contain    ${result.stdout}    "status": "ok"

SSH Boundary Is Simulated Unless A Safe Fixture Is Provisioned
    ${result}=    Simulate Read Only SSH Contract
    Should Contain    ${result.stdout}    simulated-loopback
    Should Contain    ${result.stdout}    hostname

The tests do not call low-level libraries everywhere. They call named resource keywords such as Insert Inventory Row, Write Owned Marker, and Run Harmless Child. This keeps target policy and cleanup rules in one layer while tests state the observable contract.

5. Execute into a dedicated result directory

# PowerShell
python -m robot --outputdir results\guided tests\infrastructure.robot

# Bash / POSIX shell
python -m robot --outputdir results/guided tests/infrastructure.robot

Expected: four tests pass. During execution, results/guided/scratch-rf18 exists and contains the SQLite file plus marker after the filesystem test. After suite teardown, the scratch directory is gone, while output.xml, log.html, and report.html remain.

6. Before/after evidence matrix

7. Observe transaction state deliberately

DatabaseLibrary normally commits or rolls back around Query/Execute keywords. To make transaction ownership visible, this controlled example uses no_transaction=True for an INSERT and the immediate SELECT. It then closes the SQLite connection without a commit and reconnects. With normal SQLite transactional mode, the pending change is not durable.

*** Test Cases ***
Uncommitted Insert Is Not Durable After Disconnect
    ${insert_params}=    Create List    transient    pending
    Execute Sql String
    ...    INSERT INTO inventory(name, state) VALUES (?, ?)
    ...    parameters=${insert_params}
    ...    no_transaction=${True}
    ...    alias=${DB_ALIAS}
    ${query_params}=    Create List    transient
    ${visible}=    Query
    ...    SELECT name FROM inventory WHERE name = ?
    ...    parameters=${query_params}
    ...    no_transaction=${True}
    ...    alias=${DB_ALIAS}
    Length Should Be    ${visible}    1
    Disconnect Lab Database
    Connect To Database    sqlite3    database=${DB_PATH}    alias=${DB_ALIAS}
    ${after}=    Query
    ...    SELECT name FROM inventory WHERE name = ?
    ...    parameters=${query_params}
    ...    alias=${DB_ALIAS}
    Length Should Be    ${after}    0

This is a teaching probe, not a recommended application transaction manager. In production automation, transaction policy belongs in a well-designed domain keyword or application/API boundary. Do not scatter no_transaction=True across tests.

8. Prove that SQL data stays data

${payload}=    Set Variable    literal-' OR 1=1 --
Insert Inventory Row    ${payload}    ready
${rows}=    Get Inventory Row    ${payload}
Length Should Be    ${rows}    1
Should Be Equal    ${rows}[0][name]    ${payload}

The scary-looking string must be stored literally as a row value. If it changes the WHERE clause, your abstraction is vulnerable because statement and data boundaries were lost.

9. Process evidence and cleanup

Run Process returns only after this short child exits. For long-running processes, use Start Process, retain the returned handle/alias, and terminate or await that exact process during teardown. Never “clean up” by killing all processes with a matching name.

${result}=    Run Harmless Child
Log    rc=${result.rc}
Log    stdout=${result.stdout}
Log    stderr=${result.stderr}
Should Be Equal As Integers    ${result.rc}    0

For large/unbounded output, redirect stdout/stderr to files in the owned output area. Capturing an unlimited stream in memory is not an observability strategy.

10. SSH simulation versus a real loopback fixture

The simulation preserves the important Robot architecture: a domain keyword invokes an external execution boundary and returns stdout/status evidence. It does not claim to test SSH protocol, encryption, authentication, or host-key trust. If your team already has a disposable localhost/container SSH fixture with an independently verified host key, you may replace the simulator with an allowlisted read-only command. Keep that path optional because the current stable SSHLibrary trust default is not an acceptable production baseline by itself.

11. Challenge: choose the right layer

You must verify that a generated application config file contains one value, then verify the database contains the corresponding record. Should the test run grep and a raw SQL string directly? A better answer is two domain keywords: one filesystem assertion using OperatingSystem and one parameterized database query. The test should express the invariant while the resource owns path, connection, and driver details.

12. Cleanup verification

# After Robot exits, the scratch directory should be gone, but results remain.
# PowerShell
Test-Path .\results\guided\scratch-rf18
Get-ChildItem .\results\guided

# Bash
test ! -e results/guided/scratch-rf18 && echo "scratch removed"
ls -la results/guided

If scratch remains, do not blindly delete it. Inspect why teardown failed and preserve the run artifacts first. Manual cleanup is permitted only after confirming the exact path is the disposable lab directory.

Knowledge check

13. Summary and next lesson

You now have one coherent local infrastructure workflow with database, file, process, and simulated remote-execution boundaries. Lesson 3 asks when each boundary is appropriate and how to keep convenience from becoming hidden coupling.

References and version anchors

• Robot Framework 7.4.2 User Guide, Process 7.4.2, and OperatingSystem 7.4.2 — core execution, variables/lifecycle, structured child processes, filesystem operations, and current standard-library semantics.
• DatabaseLibrary 2.4.1 keyword documentation, PyPI release, and maintainer repository — connection, query, transaction wrapper, parameter, retry, alias, and SQLite guidance.
• SSHLibrary keyword documentation, SSHLibrary 3.8.0 on PyPI, and maintainer repository — connection/session behavior and current release status. The mandatory lab does not rely on a real SSH server.
• Python sqlite3 documentation — SQLite connection/transaction behavior used by the local database fixture.