Save a run as a test and run it again

Turn a Scratchpad run into a test, then run one test, a group or all of them from the Tests board.

In short

A test is a saved run your team can repeat. Save one from the Scratchpad, add checks, and run one test, a group or all of them from the Tests board. A test runs when someone presses Run, or on a schedule you set (see "Run on a schedule" below).

Before you start

• Who: saving and changing a test needs Edit; running a saved test needs Run. A viewer reads tests and results.
• Plan: every test in a Run all tests counts as a run. A schedule counts against your plan's monitor checks a month instead.
• First: run a statement in the Scratchpad that you want to keep.

Save a test

1. In the Scratchpad, run a statement and press "Save as test". The editor opens with the SQL, parameters, environment and transaction filled in.
2. Give it a name the team will recognize, such as Create invoice rejects a negative amount, and choose a "Test group".
3. Add checks with "Add check", or run the test and click cells.
4. Press "Save test". It shows as "Not tested" until its first run.

Run from the Tests board

1. Press "Run" on a test, "Run group" on a test group, or "Run all tests" for the project.
2. To pass values from one test to the next, open "More ways to run" beside "Run all tests" and choose "Run all in order (chained)".
3. Run all tests, Run group and the chained run go to the background queue: their progress shows above the tests, and you can leave the screen while they run.
4. To stop one, press "Cancel" on its progress line. It stops after the test in hand; what ran is kept, and the tests it did not run are not counted.
5. When it ends, a message says how many passed and failed. Press "Show failing" to see only the failures.

Good to know

• History is where you spot a regression: yesterday 12 ms and 3 rows, today 900 ms and none. Click a row to open its result, or use Share to send a run to a person or conversation.
• A board card linked to a test turns red while the test fails and green when it passes.
• Tables linked from your repository show under "Tables the code expects" in the Catalog. Once you have synced the catalog of two or more environments, each table also shows whether it is there in every one of them, and "Not the same everywhere" marks a table that is, for example, in QA but missing in Production.

Find tests and keep views

• Type in "Search tests" to find tests by name, group, object or any text in their SQL. The chips narrow the board to Failing, Never run or Passing; their numbers always count the whole project.
• A big group shows its first 50 tests. Press "Show 50 more" under the table to read the next ones; the line beside it says how many are shown of how many.
• To keep a filter and search, open the view menu beside the chips and choose "Save as view". Pick it from the same menu next time, share it with your team, or have the board open with it.
• "Run the failing tests" in the Run all tests menu runs every test whose last run failed, including ones not on screen.

See how the database runs a query

• Write a SELECT in the Scratchpad, or open a saved query test, and press "Show plan". The database says how it would run the query. Nothing runs and nothing changes, on Production too.
• The steps show as a tree. Costly steps are marked, with their share of the estimated cost. SQLite gives no costs, so it marks full scans and temporary sorts instead.
• "Index advice" lists only indexes the plan points at: a table read in full and filtered, a table read in full and then sorted, a join that reads a whole table for every row, or an index the database itself reports as missing. Copy the CREATE INDEX and check it against the writes on that table before you add it.
• "Say it in plain words" asks AI to word that advice. AI reads the table and column names the advice names, never a value, and adds no index of its own. It uses your own key or your AI allowance, and only when you press it.
• Show plan reads one SELECT or WITH statement. It works on PostgreSQL, SQL Server, MySQL, MariaDB and SQLite connections and counts as one execution.

See the safety rules where you run

• Press "Safety" beside the environment’s tier at the top of the screen. It lists the rules the server applies there before any connection opens.
• On Production, and on an environment marked read-only, every run rolls back and a test set to commit is refused. On Preprod, Staging and Production, schema changes and UPDATE or DELETE without a WHERE are refused.
• A run that rolls back cannot be ended early: text with its own COMMIT, or a ROLLBACK that is not to a savepoint, is refused before anything is sent.
• The rules follow the environment’s tier and read-only switch, and the project’s production switch in Share. Change those to change the rules.

Change many tests at once

• On the Tests board, tick the tests you want in a group's table. The bar above the table offers "Switch on", "Switch off", "Move to group…" and "Remove".
• "Move to group…" moves them to one of the project's groups, or to a new group you type.
• "Remove" removes them at once and offers Undo in the message that follows. Tests hidden by a filter or a search are never changed.
• Changing many tests at once needs Edit on the project, as changing one does.

Run on a schedule

• Open the Tests board's … menu and choose "Run on a schedule", or press "Add schedule" in the Schedules section. Give it a name - or keep the suggested one - and choose what runs: every enabled test, or one group.
• Choose how often: every few minutes, every hour, every day, every week on a day, or every month on a day from the 1st to the 28th. A time of day is on your own clock and stays there when the clocks change. Press "Start schedule".
• A project can have several schedules - for example every 15 minutes for the quick checks and every night for the slow group. Each shows its own line in the Schedules section above the tests.
• Each run is one of your monitor checks, shared with API monitors. Plan & usage shows how many your plan allows a month; a choice your plan does not allow says "not on this plan".
• A schedule runs as the person who made it or saved it last, with their access, and tells them in their inbox when it starts failing and when it passes again. "Take over" in its … menu makes it run as you.
• Open a schedule's line to see its history: every run, newest first, with how many tests passed and failed. "Run now" runs it at once in the background, as you, without moving its next run.
• Adding, changing, taking over or removing a schedule needs Edit on the project; "Run now" needs Run. Everyone else who can open the project reads the schedules.
• Switch "On" off to pause a schedule without losing its settings. "Remove schedule" stops it and offers Undo; the runs it already made are kept.
• If someone else saved the schedule while you were editing it, you are asked whether to load their version or keep yours.
• If a schedule waits on your plan, is over your plan's limit or is paused by the allowance, its line says so. It starts again by itself when that ends.
• "Also tell a channel" posts a failed run - and one that could not run - to up to three of the workspace's webhooks too, such as a Slack or Microsoft Teams channel, besides the inbox of the person it runs as. Only switched-on webhooks of this workspace are offered; add one in Settings > Webhooks. The message names the schedule, the project and how many tests failed and links to them, never a value.

Gate a pipeline on the project

Your build pipeline can run the project and stop when a test fails. {{brand}} runs the tests on its own queue, against the environment the project keeps and under its safety rules - exactly like Run all tests - so no database address is in the pipeline.

• In the pipeline, run: kamocore sql run --project <the project's id> --junit sql-results.xml. The id is in the address of the project.
• The step fails when a test fails. It also fails - never passes - when a test could not run, the run was stopped, or it did not end within --timeout (30 minutes unless you say).
• The JUnit file shows each test by name in the pipeline's test tab. It never holds a row or a value an assertion saw.
• --group, --environment and --test narrow what runs; --failing-only runs only the tests whose latest run failed.
• The access token needs the scope SqlTester:write. Ready steps for GitHub Actions, GitLab and Azure Pipelines come with the CLI.

Move tests with a file

1. Open the Tests board's … menu and choose "Download cases file". Every case of the project is saved as one .sqlcases.json file named after the project.
2. Keep the file in your repository, or edit it, or take it to another project or workspace.
3. In the project that should get the tests, open the … menu, choose "Import tests file…" and pick the file.
4. Read what the import would do: each test is New, Changed, Same or Skipped, with the reason for a skipped one. Nothing is written yet.
5. Press "Import n tests".

• Back in the project it came from, a test changes the test it was downloaded from. Anywhere else, a test with the same group and name is changed and the rest are added.
• Connections never travel: the file names a test's connection, and a project without one of that name runs the test on its default connection.
• The file says its format version. A file from a newer {{brand}} is refused until this one is updated; every older version still imports.
• Downloading needs only access to the project; importing needs Edit. If someone changes one of the tests while you import, nothing is imported and you can try again.

If something goes wrong

• "A test needs a name."
• "A query test needs SQL text." / "A stored procedure test needs an object name."
• "Parameter names must be unique."
• "This file is not a SQL Tester test file.": pick a file made by "Download tests file".
• "This test file is format version …": the file comes from a newer {{brand}}, and this one reads it once it is updated.

Related

• /help/sql-tester/case-editor
• /help/sql-tester/assertions
• /help/sql-tester/variables-and-chaining