SQL in QA work

How do you verify in the database the result of an action in the UI?

Example: a user topped up an account with 500 PLN. What I check:

1. The record is created: a new transaction with the right amount, type, currency, account.
2. The status matches the stage: pending → completed, not stuck.
3. Side effects: the balance changed by exactly the amount, a fee record was created if there is one.
4. Nothing extra: there is no duplicate, other people's accounts didn't change.
5. Service fields: created_at in the right time zone, an audit record, who performed the action.

-- the account's latest transaction
SELECT id, type, amount, status, created_at
FROM transactions
WHERE account_id = 10
ORDER BY created_at DESC, id DESC
LIMIT 3;

-- how many identical transactions in the last minute (a duplicate?)
SELECT COUNT(*) FROM transactions
WHERE account_id = 10 AND amount = 500 AND type = 'deposit'
  AND created_at > NOW() - INTERVAL '1 minute';

How do you find anomalies and data integrity violations?

A set of queries worth having at hand:

• Orphaned records: children without a parent (an anti-join).
• Duplicates where they can't be (GROUP BY + HAVING COUNT > 1).
• Impossible values: negative amounts, dates in the future, unknown statuses.
• Impossible combinations: a blocked account with new transactions, a completed transaction without a completion date.
• Stuck states: pending for longer than a reasonable time.

-- statuses that aren't in the documentation
SELECT status, COUNT(*) FROM transactions GROUP BY status;

-- negative balances
SELECT id, user_id, balance FROM accounts WHERE balance < 0;

-- transactions "stuck" in pending for more than an hour
SELECT id, account_id, amount, created_at FROM transactions
WHERE status = 'pending' AND created_at < NOW() - INTERVAL '1 hour';

-- transactions on blocked accounts after the block
SELECT t.id, a.id AS account_id
FROM transactions t JOIN accounts a ON a.id = t.account_id
WHERE a.status = 'blocked' AND t.created_at > a.blocked_at;

What should you pay attention to when working with dates and time zones?

• TIMESTAMP stores time without a zone, TIMESTAMPTZ a point in time (in PostgreSQL it is stored in UTC and shown in the session's zone).
• The UI shows local time, the database often UTC. A difference of 1–2 hours in reconciliation isn't necessarily a bug, but you need to know what is where.
• Day boundaries: "transactions for 30 September" is >= '2026-09-30' AND < '2026-10-01', not = '2026-09-30'.
• Day boundaries in different zones: an operation at 23:30 in Warsaw is already the next day in UTC+3. "Per day" reports may diverge.
• The daylight saving change: an hour that repeats or disappears.

SHOW timezone;                                     -- the session's zone
SELECT NOW(), NOW() AT TIME ZONE 'Europe/Warsaw';

-- transactions for the day in Warsaw time
SELECT * FROM transactions
WHERE created_at >= '2026-09-30 00:00:00+02'
  AND created_at <  '2026-10-01 00:00:00+02';

How do you store money correctly and what bugs are there?

FLOAT and DOUBLE store numbers in binary form with an error: 0.1 + 0.2 gives 0.30000000000000004. For money this is unacceptable: cents get "lost" when summing thousands of operations.

The right way: NUMERIC(p, s) or an integer in minimal units (cents).

What to test:

• Amounts with cents: 0.01, 0.10, 99.99, 100.005.
• Rounding rules for fees and conversion: to which place, in which direction.
• Does the sum of the rounded parts equal the rounded sum? (splitting 100 into three parts).
• Currencies with different numbers of decimal places: JPY has none, some have three.

SELECT 0.1::float8 + 0.2::float8;          -- 0.30000000000000004
SELECT 0.1::numeric + 0.2::numeric;        -- 0.3

SELECT ROUND(2.675::numeric, 2);           -- 2.68
SELECT ROUND(2.675::float8::numeric, 2);   -- may give 2.67 because of the float error

How do you prepare and clean up test data with SQL?

• A marker: all test records are marked: an email prefix, a tag, a separate tenant. Then cleanup is a DELETE by the marker, with no risk of touching someone else's data.
• RETURNING: get the id of the created record and use it further.
• Idempotency: the script can be run again: ON CONFLICT DO NOTHING or clean up old data first.
• Order: create from parents to children, delete the other way, so as not to fail on foreign keys.
• Bulk data: generate_series for load and pagination tests.

-- 1000 transactions for checking pagination
INSERT INTO transactions (account_id, type, amount, status, created_at)
SELECT 10, 'deposit', (random() * 1000)::numeric(14,2), 'completed',
       NOW() - (g || ' minutes')::interval
FROM generate_series(1, 1000) AS g;

-- cleanup: from children to parents
DELETE FROM transactions WHERE account_id IN
  (SELECT a.id FROM accounts a JOIN users u ON u.id = a.user_id
   WHERE u.email LIKE 'qa_auto_%@test.com');
DELETE FROM accounts WHERE user_id IN
  (SELECT id FROM users WHERE email LIKE 'qa_auto_%@test.com');
DELETE FROM users WHERE email LIKE 'qa_auto_%@test.com';

What is SQL injection and how do you test for it?

SQL injection is when user input is inserted into a query as part of the code, not as data. Then a specially chosen string changes the query's logic: bypasses authorisation, reads other people's data, drops tables.

-- vulnerable code builds the query as a string:
--   "SELECT * FROM users WHERE email = '" + input + "'"
-- input:  ' OR '1'='1
-- we get: SELECT * FROM users WHERE email = '' OR '1'='1'   -> all users

-- defence: a parameterised query, input is always only data
--   SELECT * FROM users WHERE email = $1

How do you check that a balance matches the transaction history?

This is a reconciliation of denormalised data: the stored balance must equal the sum of all completed money movements on the account. The query returns only accounts with a discrepancy; normally the result is empty.

SELECT a.id, a.balance,
       COALESCE(SUM(CASE WHEN t.type = 'deposit' THEN t.amount ELSE -t.amount END), 0) AS calculated
FROM accounts a
LEFT JOIN transactions t
       ON t.account_id = a.id AND t.status = 'completed'
GROUP BY a.id, a.balance
HAVING a.balance <> COALESCE(SUM(CASE WHEN t.type = 'deposit' THEN t.amount ELSE -t.amount END), 0);

How do you use SQL in automated tests?

• Cypress: browser code can't connect to the database directly, so the query is executed in Node through cy.task (for example with the pg library).
• Playwright: tests run in Node anyway, so a database client is connected directly, conveniently through a fixture.

Rules: a separate database user for tests with minimal rights; credentials in environment variables; tests don't depend on data created by other tests; data is cleaned up by the marker.

// cypress.config.ts
import { defineConfig } from 'cypress';
import { Client } from 'pg';

export default defineConfig({
  e2e: {
    setupNodeEvents(on, config) {
      on('task', {
        async dbQuery({ sql, params }: { sql: string; params?: unknown[] }) {
          const client = new Client({ connectionString: config.env.DB_URL });
          await client.connect();
          try {
            const res = await client.query(sql, params);
            return res.rows;
          } finally {
            await client.end();
          }
        },
      });
    },
  },
});

// in the test
cy.task('dbQuery', {
  sql: 'SELECT status FROM transactions WHERE id = $1',
  params: [txId],
}).then((rows: any) => expect(rows[0].status).to.eq('completed'));

API validation through the DB

After a creation request I take the ID from the API response and do a SELECT by that ID in the relevant table. I check that the record exists and all fields match what was sent. I also check the fields the server fills in: created\_at, the default status, the author.

For an update I compare the state before and after: only the expected fields changed, and updated\_at was updated. For a delete I check whether the record was physically removed or it is a soft delete with a deleted\_at flag. It is important to check related tables too: for example, whether order items were created and the product's stock decreased.

I also check that data is stored in the right format: the password is hashed, dates are in UTC, amounts have the right precision. In automated tests such checks are done through a Node.js task cy.task with a DB client or a separate API helper. It is desirable to have read-only access to the DB on the test environment.