# Testing SQL, scripts and PL/SQL

Create suites, run SQL, TypeScript, Bash, batch, PowerShell and utPLSQL tests, and understand their results.

Source: https://www.nexosqlstudio.com/docs/testing
Language: en
Type: docs

# Testing SQL, scripts and PL/SQL

The **Testing** region groups workspace tests and Oracle utPLSQL package tests. Create a suite, prepare sample data, run your checks, and review failures and reports in one place. You can also [run tests from your terminal](https://www.nexosqlstudio.com/docs/testing-cli).

## Choose a test type

| Type | File or object | Best for | Runtime |
| --- | --- | --- | --- |
| SQL | `*.test.sql` | Query results, database rules and expected errors | A saved database connection |
| TypeScript | `*.test.ts` | Several related assertions, typed binds and application logic | Nexo's Bun runtime, or your project's Vitest |
| Bash | `*.test.sh`, `*.test.bash` | Command line workflows and SQL checks | Bash; Git Bash on Windows, plus curl for SQL |
| Windows batch | `*.test.bat`, `*.test.cmd` | Existing Windows command scripts | Windows cmd.exe; Windows PowerShell for SQL |
| PowerShell | `*.test.ps1` | PowerShell automation and SQL checks | Windows PowerShell on Windows; `pwsh` on macOS/Linux |
| utPLSQL | An Oracle package with `%suite` / `%test` annotations | PL/SQL unit tests and line coverage | utPLSQL 3 installed in the Oracle database |

SQL syntax and database features depend on the selected engine. The complete examples below use Oracle and a saved connection named **Oracle Free APP_00001**. Change that name to your own development connection. Batch tests run on Windows; choose Bash or PowerShell when you need the same suite on different operating systems.

## Your first run

1. Open a project folder in VS Code and connect to your database in Nexo SQL Studio.
2. Open **Testing** from the Nexo menu. Select **Create your first test suite**, give it a name, and choose a connection.
3. Use **New test** to create SQL, TypeScript, shell or utPLSQL tests. **Create shell test** asks you to choose Bash, PowerShell or Windows batch.
4. Save the files. Expand the suite to see the discovered tests. Refresh Testing after changing configuration or installing package tests.
5. Run the suite with its run action. Open a result to see its assertions, SQL errors, script output and source location.

An ordinary saved `.sql` test file attached to a connection supports database autocomplete, just like a worksheet. The connection shown for that editor controls completion. The test's `@connection`, enclosing suite or default configuration controls execution.

## A complete Oracle example

Create these files in your project:

```text
nexo.test.json
tests/
  orders/
    nexo.suite.json
    prepare.sql
    finalize.sql
    orders.test.sql
    orders.test.ts
    orders.test.sh
    orders.test.bat
    orders.test.ps1
```

### 1. Choose connections and discovery patterns

Put this in **nexo.test.json**, at the project root. The alias `app` keeps connection names out of your test files. This example includes the Windows scripts; omit `bat,cmd` when running on macOS or Linux.

Discovery supports up to 5,000 matching files. If your patterns match more, Nexo stops with an error; narrow `include` or add `exclude` patterns before running again. It never treats a partial file list as a complete run.

```json
{
  "include": ["tests/**/*.test.{sql,ts,sh,bash,bat,cmd,ps1}"],
  "exclude": ["**/node_modules/**", "**/.git/**", "**/.nexo/**"],
  "connections": {
    "app": { "connection": "Oracle Free APP_00001" }
  },
  "defaultConnection": "app",
  "timeoutMs": 30000,
  "parallel": 4,
  "ts": { "runner": "auto", "fileTimeoutMs": 1800000 }
}
```

Connection names refer to profiles saved in Nexo. Keep passwords in the connection settings. They do not belong in test files or this configuration.

### 2. Group files and add setup and cleanup

Put this in **tests/orders/nexo.suite.json**:

```json
{
  "name": "Order checks",
  "connection": "app",
  "tags": ["smoke", "orders"],
  "parallel": false,
  "timeoutMs": 30000,
  "prepare": ["prepare.sql"],
  "finalize": ["finalize.sql"]
}
```

`prepare` runs before the suite's tests. `finalize` runs afterwards, including after a failed setup or cancelled run. Lists run in their declared order. Nested folders with their own `nexo.suite.json` create nested suites. Use `parallel: false` when files share data or must run in order; the normal file ordering is alphabetical. Use `true` for the configured worker count, or a number such as `2` for a limit. The workspace's `parallel` setting caps the total number of active test files and hooks across all suites, including nested suites.

Put this in **prepare.sql**. It creates a dedicated sample table and resets only its contents. Run this tutorial in a development schema where creating that table is appropriate.

```sql
begin
  execute immediate 'create table nexo_test_items (
    id number primary key,
    label varchar2(100) not null,
    amount number(10,2),
    note varchar2(100)
  )';
exception
  when others then
    if sqlcode != -955 then raise; end if;
end;
/

delete from nexo_test_items;
insert into nexo_test_items values (1, 'Notebook', 10, null);
insert into nexo_test_items values (2, 'Keyboard', 30, 'Office');
```

Put this in **finalize.sql**:

```sql
-- Remove any temporary example rows that were explicitly committed.
-- Keep the two baseline rows so you can inspect the table after a run.
delete from nexo_test_items where id >= 900;
```

Successful SQL prepare/finalize files commit their changes. SQL file-level `@beforeAll` and `@afterAll` hooks also commit. Script hooks can use the same database helpers as tests; SQL they send through the helper commits only when the script succeeds. A failed, timed-out or cancelled script hook rolls back that transaction. Use hooks to establish shared data, and individual tests for changes you want rolled back.

### 3. Write SQL checks

Put this in **orders.test.sql**:

```sql
-- @suite SQL order checks
-- @connection app
-- @tag smoke

-- @beforeEach
insert into nexo_test_items values (900, 'Temporary item', 5, null);

-- @test "sees the baseline and the temporary row"
select count(*) as item_count from nexo_test_items;
-- @expect item_count = 3
-- @expect rows = 1

-- @test "calculates the baseline total"
select sum(amount) as total from nexo_test_items where id < 900;
-- @expect total = 40
-- @expect duration < 2s

-- @test "matches a label and checks a null"
select label, note from nexo_test_items where id = 1;
-- @expect label like 'Note%'
-- @expect note is null

-- @test "returns the expected set of IDs"
select id from nexo_test_items where id < 900;
-- @expect same as: select 1 as id from dual union all select 2 as id from dual

-- @test "rejects division by zero"
select 1 / 0 as value from dual;
-- @expect error ORA-01476

-- @test "example to implement later"
-- @skip Waiting for the discount rule
select 1 as value from dual;
-- @expect value = 1
```

Each SQL test runs in a transaction and rolls back afterwards. `@beforeEach` and `@afterEach` run in that transaction. The next test starts from the baseline again. Add `-- @commit` inside an individual test only when you deliberately want its changes to remain.

Column assertions inspect the **first result row**. Use a count, aggregate, a query with a `where` clause, or a set comparison when checking multiple rows. A file can contain several SQL statements per test; assertions inspect the last result. End a PL/SQL block with `/` on its own line.

| Assertion | Example |
| --- | --- |
| Result row count | `-- @expect rows >= 1` |
| First-row value | `-- @expect total = 40` |
| Comparison | `-- @expect total > 0` (also `!=`, `<`, `<=`, `>=`) |
| Pattern | `-- @expect label like 'Note%'` or `label not like 'Old%'` |
| Null | `-- @expect note is null` or `note is not null` |
| Expected database error | `-- @expect error`, `error ORA-01476`, `error like '%divisor%'` |
| Duration | `-- @expect duration < 500ms` or `duration <= 2s` |
| Same rows | `-- @expect same as: select ...` (compares rows without requiring the same order) |

Names after `@test` appear in results. `@suite` names the file. File-level and test-level `@tag` values let you filter runs. Set timeouts in the workspace or suite configuration; SQL tests do not have a per-test `@timeout` directive.

### 4. Write TypeScript checks

Put this in **orders.test.ts**:

```typescript
// @suite TypeScript order checks
// @connection app
// @tag smoke
import { describe, expect, it } from 'vitest';
import { db } from 'nexo:test';

describe('orders', () => {
  it('reads one value', async () => {
    const count = await db().scalar(
      'select count(*) as item_count from nexo_test_items'
    );
    expect(Number(count)).toBe(2);
  });

  it('uses a bind rather than building SQL strings', async () => {
    const rows = await db('app').query(
      'select label as "label" from nexo_test_items where id = :id',
      { id: 1 }
    );
    expect(rows[0].label).toBe('Notebook');
  });

  it('updates data within the file transaction', async () => {
    const changed = await db().exec(
      'update nexo_test_items set amount = :amount where id = :id',
      { amount: 12, id: 1 }
    );
    expect(changed).toBe(1);
    expect(Number(await db().scalar(
      'select amount from nexo_test_items where id = 1'
    ))).toBe(12);
  });

  it.skip('discounts: implement later', () => {});
});
```

`db()` uses the file, suite or default connection; `db('app')` chooses an alias. `query()` returns rows, `scalar()` returns the first column of the first row, and `exec()` returns the affected row count. Quote Oracle column aliases when you want predictable lowercase keys in JavaScript.

SQL sent through `db()` shares a transaction for the **whole file** and rolls back when the file finishes. Tests in that file can see earlier changes. Use Vitest setup/cleanup functions when each test needs a fresh state. A header `// @commit` keeps the file's changes instead.

`timeoutMs` limits each TypeScript test. The separate `ts.fileTimeoutMs` setting limits the whole file, including startup and cleanup; its default is 30 minutes. Increase it for large parameterized files rather than increasing every individual test's timeout.

With `ts.runner: "auto"`, Nexo uses your project's installed Vitest when available, otherwise Bun's compatible test runner. Set `"bun"` or `"vitest"` to choose explicitly. Your Vitest configuration still applies, including file patterns and setup files; include the new test folder in those patterns or choose `"bun"` for these examples. The create action adds editor declarations for `nexo:test`. For Vitest-specific APIs or plugins, install Vitest in the workspace and select its runner. Hooks in `.ts`, `.js` and `.mjs` run with Bun.

### 5. Write Bash checks

Put this in **orders.test.sh**:

```bash
# @suite Bash order checks
# @connection app
set -euo pipefail

# @test "database has two baseline rows"
count=$(nexo_sql "$NEXO_CONNECTION" "select count(*) from nexo_test_items" | tail -n +2)
test "$count" = "2"
echo "Baseline is ready"
# @expect output like '%Baseline is ready%'

# @test "an expected command failure"
exit 7
# @expect exit 7
```

`nexo_sql <alias> "<SQL>"` prints a header line followed by tab-separated rows. The example uses `tail -n +2` to remove that header before comparing a single value. An empty alias uses the file/suite/default connection. Database errors cause a nonzero exit code. Bash tests require `curl` when using this helper.

### 6. Write Windows batch checks

Put this in **orders.test.bat** (the same syntax works in `.test.cmd`):

```bat
REM @suite Windows batch order checks
REM @connection app
@echo off

REM @test "reads the database"
call "%NEXO_SQL_HELPER%" "%NEXO_CONNECTION%" "select count(*) from nexo_test_items"
if errorlevel 1 exit /b %errorlevel%
echo Database check finished
REM @expect output like '%2%'
REM @expect output like '%Database check finished%'

REM @test "an expected command failure"
exit /b 7
REM @expect exit 7
```

Use `call` for the database helper, and check `errorlevel` immediately afterwards. Batch continues after a failed command unless your script exits. Directives use `REM @...` or `:: @...`; `%` in an expectation is a pattern wildcard because the runner reads that comment before executing the script.

### 7. Write PowerShell checks

Put this in **orders.test.ps1**:

```powershell
# @suite PowerShell order checks
# @connection app

# @test "reads the database"
$tsv = nexo_sql $env:NEXO_CONNECTION 'select count(*) from nexo_test_items'
$count = ($tsv -split '\r?\n')[1] # First data row, after the header.
if ($count.Trim() -ne '2') { throw "Expected 2 rows, got $count" }
Write-Output 'Baseline is ready'
# @expect output like '%Baseline is ready%'

# @test "an expected command failure"
exit 7
# @expect exit 7
```

PowerShell tests start with `$ErrorActionPreference = 'Stop'`. A thrown exception or failed database request fails the test. When calling native programs, check `$LASTEXITCODE` yourself and use `exit $LASTEXITCODE` if needed. On Windows, the process uses an execution-policy override for this invocation; it does not change the machine's saved policy. Your organization's enforced policy can still prevent execution.

### Script rules shared by all three languages

Each `@test` runs in a fresh process. Lines before the first `@test` are prepended to each case; variables created in another case do not carry over. A file with no `@test` runs as one case. The working directory is the original script's folder. PowerShell's `$PSScriptRoot` and batch's `%~dp0` refer to the generated script. Use `$env:NEXO_TEST_FILE` or `%NEXO_TEST_FILE%` when you need the original path.

Exit code `0` is expected unless `@expect exit <number>` says otherwise. `@expect output like '%ready%'` and `output not like '%error%'` check combined stdout/stderr. `%` matches any text and `_` matches one character. Use `@timeout 5s`, `@timeout 500ms` or `@timeout 1m` at file or case level. `@skip <reason>`, `@tag` and `@commit` are also supported.

Output assertions need complete captured output. If a script produces more than 100,000 characters, Nexo reports those assertions as errored. Reduce the output, or check the condition inside your script and return an exit code.

SQL sent through the Nexo helper shares a transaction per script **case** and rolls back at the end, unless that case uses `@commit`. External programs, files, network requests, and connections opened by your own tools follow those tools' rules; Nexo cannot roll them back. Oracle DDL and explicit commits also persist regardless of the test's automatic rollback.

Scripts receive these useful environment variables:

| Variable | Meaning |
| --- | --- |
| `NEXO_WORKSPACE` | Project root |
| `NEXO_RUN_ID` | Unique run ID |
| `NEXO_CONNECTION` | File or suite connection reference, when set |
| `NEXO_ENVIRONMENT` | Selected environment, when set |
| `NEXO_SUITE`, `NEXO_SUITE_DIR` | Enclosing suite name and folder |
| `NEXO_TEST_NAME`, `NEXO_TEST_FILE` | Script case name and original file |
| `NEXO_HOOK_PHASE` | `prepare` or `finalize`, in suite hooks |

The authenticated local SQL bridge is supplied automatically during the run. Use the helpers rather than saving its temporary token or endpoint.

## Create and run utPLSQL tests

utPLSQL tests are compiled **Oracle packages**, separate from workspace `.test.sql` files. They appear under Oracle package tests in Testing after connecting and refreshing.

### Set up utPLSQL once

1. Connect to your application's Oracle schema and open its package-testing setup action in Nexo. The setup assistant checks the installed version and accessible APIs.
2. If the framework is missing, use the install/setup flow with a **SYS AS SYSDBA** connection to the same pluggable database as the application. A SYSTEM account with a DBA role is not a SYSDBA session.
3. If you cannot install directly, generate the **DBA handoff script**. Give it to the DBA with the target PDB and application schema. It includes installation and application-access instructions; follow the official utPLSQL source installation steps it references.
4. Recheck access from the application connection. Run tests from that connection, rather than from SYS. An `ORA-06598` error means the utPLSQL owner needs `INHERIT PRIVILEGES` on that application user; the setup/handoff flow explains the required grant.

For a local Docker database, connect the administrator and application profiles to the same service, such as `FREEPDB1`. An installation in the root container or a different database will not make it available to your application schema.

### Create a package from the UI

Choose **New test → utPLSQL test**, select the Oracle application connection, and enter a package name. Nexo opens a specification and body attached to that connection. Replace the sample assertion, compile both parts into the database, then refresh package tests. Keep the blank line between suite annotations and the first routine's annotations.

Here is a complete package you can run on an application connection after installing utPLSQL:

```sql
create or replace package nexo_test_math as
  --%suite(Arithmetic examples)
  --%suitepath(demo)

  --%test(Adds two numbers)
  procedure adds_numbers;

  --%test(Recognizes a null)
  procedure accepts_null;

  --%test(Example for later)
  --%disabled(Not implemented yet)
  procedure later_example;
end nexo_test_math;
/

create or replace package body nexo_test_math as
  procedure adds_numbers is
    l_total number;
  begin
    l_total := 2 + 3;
    ut.expect(l_total).to_equal(5);
  end;

  procedure accepts_null is
    l_value varchar2(10);
  begin
    ut.expect(l_value).to_be_null();
  end;

  procedure later_example is
  begin
    ut.fail('Replace with an assertion when ready.');
  end;
end nexo_test_math;
/
```

Run the package or an individual test from Testing. Dashboard rerun controls apply to workspace tests; rerun utPLSQL tests from the Oracle package tree. Use **Run with coverage** to collect PL/SQL line coverage and open the results. If your own packages are compiled as native code, compile the packages you want to measure as interpreted code before collecting coverage:

```sql
alter package nexo_test_math compile body
  plsql_code_type = interpreted plsql_optimize_level = 1 reuse settings;
```

Coverage is meaningful for code actually executed by those tests. A low overall schema percentage can include many unrelated packages. Inspect object-level results to see which lines your test exercised. The [CLI guide](https://www.nexosqlstudio.com/docs/testing-cli#run-oracle-utplsql-packages) explains package execution and reports from the terminal; use the Testing UI for utPLSQL line coverage.

## Read results and export a report

The dashboard shows passed, failed, errored and skipped tests, elapsed time, suite hooks and available coverage. Select a case to inspect expected and actual values, missing/extra rows for set comparisons, database errors or captured script output. Source links take you back to the test. Use **Rerun failed** after a repair, or rerun the suite when setup must run again.

Here is a larger sample suite running against Oracle Free: **67 passed, 9 deliberately skipped**, with all six prepare/finalize steps passing. The skipped cases are disabled examples of failures and timeouts. Select a screenshot to view it full size.

[![Testing dashboard with SQL, TypeScript and shell suites, passing results and six successful setup and cleanup steps](https://www.nexosqlstudio.com/images/docs/testing-suite-results.png "Passing suites and their setup and cleanup steps")](https://www.nexosqlstudio.com/images/docs/testing-suite-results.png "Open the full-size suite results screenshot")

When those deliberately failing examples are enabled, the dashboard shows the problems with expected and actual values. This screenshot is an intentional failure demonstration:

[![An intentional failure run showing failed and errored tests, successful cleanup, and expected versus actual assertion values](https://www.nexosqlstudio.com/images/docs/testing-failure-results.png "Intentional failures with expected and actual values")](https://www.nexosqlstudio.com/images/docs/testing-failure-results.png "Open the full-size failure results screenshot")

Oracle package runs use the same dashboard. This example has **14 passed and 1 disabled** utPLSQL test. The overall coverage includes other objects in the schema, so use the Coverage tab to inspect individual packages.

[![Real utPLSQL results with 14 passed tests, one skipped test, duration and coverage](https://www.nexosqlstudio.com/images/docs/testing-utplsql-results.png "utPLSQL package results with duration and coverage")](https://www.nexosqlstudio.com/images/docs/testing-utplsql-results.png "Open the full-size utPLSQL results screenshot")

Run history is saved privately under `.nexo/test/runs` in the workspace. Export a run as **JUnit XML** for CI tools or **JSON** for complete Nexo results. A failed prepare/finalize step matters even when individual assertions pass; inspect the hook results too.

## Switch environments without editing tests

Use environment mappings in **nexo.test.json**:

```json
{
  "connections": {
    "app": {
      "connection": "Local app",
      "environments": {
        "local": "Local app",
        "qa": "QA app"
      }
    }
  },
  "defaultConnection": "app",
  "environment": "local"
}
```

Tests and suites continue to use `app`. The CLI can choose another mapping with `--environment qa`. If you use literal connection names or IDs in a test, those explicit references take precedence over the default alias.

## When something does not run

| Symptom | What to check |
| --- | --- |
| No workspace tests | Save a `*.test.sql`, `*.test.ts` or supported script file; check `include`/`exclude` and refresh Testing. |
| Connection not found | Match the saved connection name or configured alias; check the chosen environment. |
| No SQL suggestions | Attach the saved SQL file to a connection and make sure it is connected. |
| Shell runtime missing | Install Git Bash on Windows, Bash on Unix, or `pwsh` for PowerShell on Unix. Batch needs Windows. |
| Database changes remain | Check `@commit`, explicit COMMIT, DDL, and committed suite/file setup hooks. |
| TypeScript tests share state | Their `db()` transaction lasts for the file; reset data in `beforeEach`/`afterEach` or separate files. |
| utPLSQL not visible | Connect to the correct PDB, compile the test package, and recheck setup/access from the application connection. |
| utPLSQL coverage is empty | Confirm the relevant packages are interpreted and their lines are reached by the tests. |
| CLI access denied | Review workspace/client authorization and the connection/environment safeguards in Nexo; see the CLI guide. |

[Continue to the testing CLI guide →](https://www.nexosqlstudio.com/docs/testing-cli)

![Testing dashboard with SQL, TypeScript and shell suites, passing results and six successful setup and cleanup steps](https://www.nexosqlstudio.com/images/docs/testing-suite-results.png)

![An intentional failure run showing failed and errored tests, successful cleanup, and expected versus actual assertion values](https://www.nexosqlstudio.com/images/docs/testing-failure-results.png)

![Real utPLSQL results with 14 passed tests, one skipped test, duration and coverage](https://www.nexosqlstudio.com/images/docs/testing-utplsql-results.png)