How to build a take-home assignment with a real database

A take-home with a database works best when the candidate gets a real database that is already running and seeded, so the time goes into the task rather than into setup. Keep the schema and seed data small, write a few tests that run against the real database, and make sure the environment starts the same way for every candidate.

Why use a real database instead of mocks?

Most backend bugs live at the boundary between code and data: a missing index, a query that returns duplicates after a join, a transaction that isn't one, a cache that goes stale. A mocked repository hides all of that. With a real database the candidate has to deal with the same problems the job will bring.

A real database also changes what you can ask:

• Write a migration that adds a column without losing data.
• Fix a slow query and show the query plan before and after.
• Make an endpoint safe under concurrency with a transaction or a lock.
• Add a cache in front of a hot read and invalidate it correctly.

None of these make sense against an in-memory fake.

The setup tax is the real risk

The classic objection is setup. Asking candidates to install PostgreSQL, create a role and load a dump costs them an hour before they write a line, and that hour favours people with the right laptop. Whatever tool you use, the database should be running when the candidate opens the project.

Seed it with a small, deliberate dataset

Seed data is part of the task design. A few rules:

• Small enough to read. Ten customers and forty orders let a candidate understand the data by running one SELECT. A million generated rows only make sense if the task is about performance.
• Shaped to the task. If the bug is about orders with no items, make sure the seed has one. If the task is pagination, seed enough rows for three pages.
• Schema first, data second. Split them into two files, 001_schema.sql and 002_seed.sql, so the candidate can find the table definitions quickly.
• No real customer data. Use made-up names and addresses, even for an internal hire.

If your project already has migrations (Prisma, Alembic, Django, Laravel), let the install step run them and keep the seed file for data only.

How big should the task be?

Aim for something you can finish in half the time limit. Database tasks take longer than they look, because the candidate has to explore the schema first. One focused change, such as one endpoint, one migration or one query fix, plus tests, is plenty for 90 minutes.

Write tests that hit the database

Tests tell the candidate what "done" means and let them check their own work. For a database task they should run against the same database the app uses.

• Read the connection from the environment, such as DATABASE_URL, never a hard-coded string.
• Clean up after each test. Wrap each one in a transaction and roll it back, or delete the rows it created. The database keeps its rows while the candidate works, so tests that leave data behind will fail the second time they run.
• Test behaviour, not SQL. Assert on what the endpoint returns or what ends up in the table, so any correct solution passes.

const { test } = require('node:test');
const assert = require('node:assert');
const { Client } = require('pg');

test('an order with no items has a zero total', async () => {
  const db = new Client({ connectionString: process.env.DATABASE_URL });
  await db.connect();
  await db.query('BEGIN');

  const { rows } = await db.query(
    "INSERT INTO orders (customer_id) VALUES (1) RETURNING id",
  );
  const total = await db.query('SELECT order_total($1) AS total', [rows[0].id]);
  assert.strictEqual(Number(total.rows[0].total), 0);

  await db.query('ROLLBACK');
  await db.end();
});

import os

import psycopg


def test_order_with_no_items_has_zero_total():
    with psycopg.connect(os.environ["DATABASE_URL"]) as db:
        row = db.execute(
            "INSERT INTO orders (customer_id) VALUES (1) RETURNING id"
        ).fetchone()
        total = db.execute("SELECT order_total(%s)", (row[0],)).fetchone()[0]
        assert total == 0
        db.rollback()

Should you add a cache or a queue?

Only if the task is about it. A Redis-compatible store makes sense for a rate limiter, a session store or a cache invalidation problem. Adding one "for realism" just adds a second thing to understand in a short window.

Doing this in Kendor

Kendor runs PostgreSQL and Valkey (Redis compatible) inside the candidate's sandbox, on 127.0.0.1. They start before your install commands, so migrations can run during install. You declare them in kendor.yaml at the root of the project. Full reference in Databases.

version: 1
runtime:
  type: node
  version: "22"
databases:
  - type: postgres
    version: "18"
    init: db
  - type: valkey
    version: "9"
install:
  commands:
    - npm ci
services:
  api:
    dev:
      command: node --watch src/server.js
      port: 3000
    test:
      command: node --test
preview:
  service: api

version: 1
runtime:
  type: python
  version: "3.12"
databases:
  - type: postgres
    version: "18"
    init: db
install:
  commands:
    - pip install --user -r requirements.txt
services:
  api:
    dev:
      command: uvicorn main:app --host $HOST --port $PORT --reload
      port: 8000
    test:
      command: pytest --tap-stream
preview:
  service: api

What each part does:

• databases lists at most one of each type. PostgreSQL 18 and Valkey 9 are available.
• init is a SQL file, or a folder whose .sql files run in name order. It runs once, right after the database is created, as one transaction. It must be a path inside the project. Only PostgreSQL takes init; seed Valkey from your app or test setup.
• name (optional, PostgreSQL only) sets the database name. The default is app.

Every process in the sandbox, including the app, terminals and tests, gets the connection details: DATABASE_URL, PGHOST, PGPORT, PGUSER and PGDATABASE for PostgreSQL, and REDIS_URL and VALKEY_URL for Valkey. Laravel and Spring variables (DB_HOST, SPRING_DATASOURCE_URL and so on) are set too. Candidates can run psql or valkey-cli in the terminal to look inside, and the databases can't be reached from outside the sandbox.

Set it up from the editor

If you'd rather not write YAML, open the challenge in the editor and use Runtime configuration. Under Databases, pick the engine and version, and point Seed file at your SQL. Switch to Raw YAML to see what it wrote. When you import a repository, Kendor often detects the database for you, from a Prisma schema, a docker-compose.yml or the project's dependencies, and pre-fills it.

Check it as a candidate

Choose Preview as candidate and run the tests from the Tests tab. The Tests tab runs the test command from kendor.yaml; TAP output (node --test, pytest --tap-stream) gives one row per test. See The Tests tab.