Chapter 4: What is Database Testing?
Your queries work. Your data is correct. But can the database handle 10,000 users hitting it at the same time? Can a hacker steal data through SQL injection? Will the system slow to a crawl when the table grows to 50 million rows? Non-functional testing answers these questions.
A query that runs in 0.1 seconds with 1,000 rows might take 30 seconds with 1,000,000 rows. This is not theoretical. I have seen production dashboards time out because nobody tested query performance with realistic data volumes.
-- Step 1: Check how the database executes your query
EXPLAIN ANALYZE
SELECT u.full_name, COUNT(o.order_id) AS total_orders
FROM users u
JOIN orders o ON u.user_id = o.user_id
WHERE o.order_date > '2024-01-01'
GROUP BY u.full_name
ORDER BY total_orders DESC;
-- Look for: Sequential Scan (bad) vs Index Scan (good)
-- Look for: Execution time > 1 second (investigate)
-- Step 2: Find the slowest queries in your database
-- PostgreSQL: Check slow query log
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;| Performance Red Flag | What It Means | Fix |
|---|---|---|
| Sequential Scan on large table | No index — DB reads every row | Add an index on the WHERE column |
| Query takes > 2 seconds | Likely missing index or bad join | Run EXPLAIN and optimize |
| High I/O wait | Disk is the bottleneck | Add indexes, optimize queries, consider caching |
| Lock waits | Transactions blocking each other | Check for long-running transactions, reduce lock scope |
Database security testing is about two things: can unauthorized people access data they should not see, and can attackers inject malicious SQL?
If a login form builds the query by concatenating user input directly, an attacker can type something malicious in the username field and bypass authentication entirely.
-- What the developer wrote (VULNERABLE)
-- query = "SELECT * FROM users WHERE username = '"
-- + userInput + "' AND password = '" + passInput + "'";
-- What the attacker types as username:
-- admin' OR '1'='1' --
-- The query becomes:
SELECT * FROM users
WHERE username = 'admin' OR '1'='1' --'
AND password = 'anything';
-- This returns ALL users because 1=1 is always true
-- The -- comments out the password check
-- SAFE version using parameterized query:
-- query = "SELECT * FROM users WHERE username = ? AND password = ?";Never test SQL injection on production databases or systems you do not own. Always test in a controlled test environment. SQL injection testing on unauthorized systems is illegal.
Scalability is about growth. Your app has 100 users today. What happens at 100,000? What happens when the orders table has 10 million rows? Scalability testing means running your test queries against large datasets and measuring the impact.
Q: How do you test database performance?
A: I start by running EXPLAIN ANALYZE on critical queries to check the execution plan. I look for sequential scans on large tables — that means a missing index. I test with realistic data volumes, not just 10 rows. I measure query execution time and check if it stays within acceptable limits (typically under 1-2 seconds for user-facing queries). I also check for N+1 query problems, missing indexes on JOIN columns, and long-running transactions that cause lock contention.
Key Point: Non-functional database testing covers performance (slow queries), security (SQL injection, access control), and scalability (large data handling).