Database, SSH, Filesystem, Process, and Infrastructure Automation Patterns: Core Concepts and Mental Model

1. The problem: an integration check can become an administration script

Chapter 17 deliberately kept HTTP state inside a loopback fixture. Infrastructure automation raises the stakes because a database statement, filesystem deletion, process termination, or SSH command can change state outside the Robot process. The same syntax can be harmless against a disposable SQLite file and destructive against a production database. The difference is target, privilege, ownership, and lifecycle, not the keyword name.

Robot Framework is valuable here as an orchestration and evidence layer. It should make a small integration contract readable: connect to an explicitly allowed target, inspect current state, perform one bounded mutation, verify the exact outcome, and roll back or delete only resources created by this run. It should not become a generic fleet-management replacement for Terraform, Ansible, Salt, or operating-system administration tooling.

2. Read-only preflight before any mutation

python --version
python -m robot --version
python -m pip show robotframework-databaselibrary robotframework-sshlibrary
# Python stdlib SQLite version:
python -c "import sqlite3; print(sqlite3.sqlite_version)"
# Record the intended Robot output directory before the suite creates scratch state.

Nothing above changes a database, process, or filesystem target. Record Python/Robot/library versions, current working directory, output directory, and intended target identifiers. In the mandatory path the database is a file that will be created under Robot's run-specific output directory; there is no public host and no remote credential.

3. Mental model: infrastructure state is a set of independent lifecycles

flowchart TD
A[Robot suite] --> B[Domain infrastructure resource]
B --> C1[DatabaseLibrary]
B --> C2[OperatingSystem]
B --> C3[Process]
B -. optional .-> C4[SSHLibrary]
C1 --> D1[SQLite connection / transaction]
C2 --> D2[Owned filesystem]
C3 --> D3[Child process]
C4 --> D4[SSH connection + trust]
D1 --> E[Assertions]
D2 --> E
D3 --> E
D4 --> E
E --> F[output.xml / log.html]

Each branch has a different owner. Closing a database connection does not terminate a child process. Removing a file does not roll back a database transaction. Closing an SSH connection does not prove a remote command left no state behind. Robot variables merely reference these stores; they do not unify them. The result model records observations but is not itself the external state.

4. Terms and ownership before mutation

5. DatabaseLibrary: query adapter plus explicit connection lifecycle

DatabaseLibrary 2.4.1 is an external Robot library. It does not ship database engines or all drivers; it calls a Python DB-API module. For SQLite, the driver is Python's built-in sqlite3, making it ideal for a disposable course fixture.

*** Settings ***
Library    DatabaseLibrary

*** Test Cases ***
Read Local Database
    Connect To Database    sqlite3    database=${DB_PATH}    alias=labdb
    ${rows}=    Query    SELECT name, state FROM inventory    return_dict=${True}    alias=labdb
    Log    ${rows}
    Disconnect From Database    alias=labdb

The alias identifies library connection state; it is not the database transaction itself. DatabaseLibrary's query/execute keywords normally manage commit/rollback around their operation. The no_transaction option disables that wrapper when a lab deliberately needs to observe pending transaction state. Treat that option as an advanced contract, not a speed flag.

6. SQL text and data are different trust boundaries

A test parameter such as a user name is data. It must not become executable SQL syntax through string concatenation. DatabaseLibrary exposes a parameters argument so the underlying DB driver can bind values.

${params}=    Create List    alpha
${rows}=    Query
...    SELECT id, name, state FROM inventory WHERE name = ?
...    parameters=${params}
...    return_dict=${True}
...    alias=labdb

The placeholder style depends on the database driver. SQLite uses ?. A PostgreSQL or Oracle driver may use a different parameter style. The invariant is the same: keep the statement structure constant and bind values separately.

7. Filesystem state needs an ownership proof

A path inside ${OUTPUTDIR} is convenient, but location alone does not prove ownership. The suite must refuse to reuse a pre-existing scratch directory, mark ownership only after successful creation, normalize the path before destructive cleanup, and delete only the exact owned root.

${root}=    Normalize Path    ${OUTPUTDIR}${/}scratch-rf18
${exists}=    Run Keyword And Return Status    Directory Should Exist    ${root}
Should Be Equal    ${exists}    ${False}
Create Directory    ${root}
VAR    ${OWNED_ROOT}    ${True}    scope=SUITE

This is deliberately stricter than “delete and recreate.” A stale directory is evidence that a prior run or another process owns something there. Failing preflight is safer than silently erasing it.

8. Process is structured execution; shell text is not the default

Robot Framework's Process library passes a program and arguments to the operating system and returns a result object with return code, stdout, stderr, and optional redirected paths. Keep shell=False unless a shell is genuinely required and controlled. This preserves argument boundaries and avoids a large class of command-injection problems.

${result}=    Run Process    ${PYTHON}    -c    print("rf18-ok")
Should Be Equal As Integers    ${result.rc}    0
Should Be Equal    ${result.stdout}    rf18-ok
Should Be Empty    ${result.stderr}

For high-volume child output, redirect stdout/stderr to files rather than retaining unbounded output in memory. Chapter 08 already established that Process is preferred over deprecated OperatingSystem Run-style command keywords.

9. SSH has two separate security questions: who are you, and who is the host?

Authentication proves the client identity to the server. Host-key verification proves the server identity to the client. A password or private key can authenticate perfectly while the client is connected to the wrong host.

If your organization needs real Robot-driven SSH, establish the host-key policy outside the test first, use a disposable non-production host, least-privilege account, explicit command allowlist, and a library/client stack whose trust behavior satisfies policy. Never solve a host-key failure by disabling verification.

10. DevOps connection: integration evidence, not fleet administration

Knowledge check

11. Summary and bridge

Infrastructure-facing Robot automation works when state stores remain explicit: database connection/transaction, SSH session/trust, filesystem tree, child process, Robot scope, and result artifact. Lesson 2 turns this model into a fully disposable SQLite/filesystem/process workflow with an SSH simulation and concrete before/after evidence.

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.