API Test Automation with Robot Framework and Database Validation
This project automates API testing for a payment and ticketing system using Robot Framework with Python. The tests cover transaction flows, payment status checks, exception handling, and data integrity validation between the API layer and the database. The key differentiator in this project is the database validation layer — after every API call, the test doesn't just trust the response. It queries the database directly and verifies that what the API returned is actually what was saved.
Project structure
The project is organized into clear, separated layers — resources, database utilities, credentials, and custom libraries each live in their own folder. Test logic lives in resource files as reusable keywords. Database access lives in Python utility files. Credentials are handled through a dedicated credentials module. This separation means each layer can be updated independently without breaking anything else.
the implementation
Keyword-Driven Design
Robot Framework encourages writing tests in plain, readable language. Instead of writing low-level HTTP calls in every test, all the logic is encapsulated in custom keywords — reusable building blocks that describe what the test does, not how it does it. There are three main categories of keywords in this project:
01.
Response verification keywords
validate status codes, titles, messages, and transaction fields directly from the API response
02.
Database validation keywords
query the database and compare results against the API response
03.
Utility keywords
handle supporting logic like time conversion and credential setup
API Response Validation
Response validation keywords accept the full API response along with expected values as arguments. They check status codes, response body fields like payment status, ticket status, and message fields — all in one reusable call. Any test that needs to verify a transaction response calls the same keyword, keeping tests concise and consistent.
Database Validation — The Core Layer
This is the most important part of the project. After an API call returns a response, the test doesn't stop there. It takes the ticket number from the API response, queries the database directly, and compares the values side by side. Fields verified between API and database include:
01.
Vehicle type
matches what was submitted in the request
02.
Ticket number
confirms the record was actually created
03.
Payment status
ensures the API reflects what's stored
04.
Ticket status
confirms the correct state was persisted
05.
Payment method
verifies the correct payment channel was recorded
Secure Credential Management
Database credentials, API keys, and authentication data are never hardcoded. They're loaded at runtime through a dedicated credentials module that reads from environment variables. Log levels are temporarily suppressed when credentials are being retrieved to prevent sensitive data from appearing in test reports. This means the same test suite can run against different environments — staging, UAT, production — simply by changing environment variables. No code changes needed.
Database Access with SQLAlchemy
Database queries are written in Python using SQLAlchemy as the ORM layer. Two core query functions cover the needs of the entire test suite: one queries the ticket table by ticket number and returns any specified field and one queries the payment table by entity ID and returns any specified field Both functions accept a field_name parameter — so adding a new field to verify doesn't require writing a new database function. The same functions serve all validation needs across the project.
Most API automation projects treat the database as out of scope. The assumption is: if the API returns the right response, the data must be correct. That assumption fails more often than expected — especially in payment systems where the response and the actual state can diverge due to async processes, race conditions, or partial failures. By querying the database directly after every transaction, this project catches issues that response-only testing would miss entirely.
the testing stack
Every tool chosen with purpose — from feature to assertion.
Python
for writing custom keywords, database queries, and utility functions
Robot Framework
for test orchestration and keyword-driven automation