SAP HANA App Unit Testing and Debugging: A Practical Workflow

Applications built on SAP HANA usually fail at a boundary: a database procedure receives unexpected data, an application service transforms a value incorrectly, a privilege blocks execution, or a deployment artifact does not match the runtime object. A reliable test process identifies that boundary before changing code.

The most effective approach combines small database tests, service-level checks, controlled test data, and runtime evidence from the SAP HANA database explorer or SAP HANA cockpit. Keep each test focused on one behavior and preserve the inputs that produced a failure.

01Separate the test layers

Start by dividing the application into four testable layers:

1. Database logic — SQLScript procedures, functions, table functions, and calculation-view logic.
2. Application service — Node.js, Java, XSJS, or another service that calls the database.
3. Authorization — object privileges, application roles, and the technical user used at runtime.
4. Integration behavior — payload mapping, transaction handling, error propagation, and response formatting.

A database test should call the database object directly. This shows whether the defect exists in SQLScript or in the calling service. A service test should use a controlled database response so that mapping and error handling can be checked independently.

For a new project, document the expected input, output, side effects, and failure behavior for every public procedure or service endpoint. This definition becomes the test boundary and prevents a test from depending on unrelated implementation details.

02Build deterministic database tests

A useful unit test has repeatable input and a predictable result. Use a dedicated test schema or HDI container objects, create only the fixture rows needed by the test, and clean up after execution. Avoid relying on rows inserted manually by another developer or on the current contents of a shared development table.

For a SQLScript procedure, cover at least these cases:

• A valid request with one ordinary business case.
• An empty input set.
• A boundary value such as the earliest or latest permitted date.
• Duplicate or conflicting input.
• A missing reference row.
• An invalid value that should produce a controlled error.
• A transaction failure or rollback path where the procedure changes data.

Assert business results rather than incidental row order. When ordering is part of the contract, state the ordering explicitly in the query and test it directly. Compare complete result sets when the procedure returns multiple columns, and include a test for null handling whenever null is a valid input or output.

The test should also verify side effects. If a procedure inserts an audit row, updates a status, or writes an error record, assert that behavior separately from the returned result. This makes it easier to identify whether the calculation succeeded while persistence failed.

For projects using SAP HANA XS classic, XSUnit can organize unit tests for application artifacts and make repeatable execution part of the development workflow. Keep XSUnit tests narrow, isolate their fixtures, and retain the failing assertion together with the input data. For HDI-based applications, use the project’s database test artifacts and deployment pipeline so that the test runs against the same object model used by the application.

03Debug SQLScript procedures

Begin debugging with a direct call in the SQL console of SAP HANA database explorer. Use the exact parameter values supplied by the failing application request. Record the user, schema or container context, transaction state, and timestamp because a direct call under a different identity can hide an authorization problem.

Work from the outside of the procedure toward the failing statement:

1. Confirm that the procedure or function exists in the expected container or schema.
2. Confirm that the input values have the expected data types and nullability.
3. Execute the smallest relevant query independently.
4. Check intermediate table variables or result sets at logical boundaries.
5. Validate joins, filters, aggregation, and conversion expressions one at a time.
6. Re-run the complete procedure after the isolated expression behaves correctly.

When a procedure returns an unexpected result, inspect the row counts after each filtering or joining step. A join that multiplies rows, a predicate that excludes nulls, or an implicit conversion can change the result without producing a syntax error. Replace complicated expressions temporarily with named intermediate results during local diagnosis, then keep the production implementation clear and intentional.

For runtime-only failures, compare the direct SQL-console execution with the application execution. Differences in current user, default schema, session settings, transaction boundaries, or parameter serialization often explain why the same apparent call behaves differently.

Use SQLScript debugger features available in the development environment when stepping through a supported procedure is more efficient than adding diagnostic output. Keep diagnostic logging temporary or route it to a controlled test location; avoid writing sensitive payloads to ordinary application logs.

The SQLScript procedures guide provides the relevant implementation context, while SAP HANA developer performance tuning is useful when the result is correct but execution is unexpectedly slow.

04Test application service boundaries

A service test should verify how the application constructs a database call and interprets the result. Test the request validation, parameter mapping, database invocation, response mapping, and error response as separate assertions.

Use representative payloads that include missing optional fields, explicit null values, empty arrays, long strings, and invalid types. Confirm that the service rejects invalid data before it reaches the database when validation belongs at the service boundary. Confirm that database errors are converted into stable application responses without exposing internal SQL text or credentials.

For Node.js or Java services, a database integration test should run against a controlled SAP HANA test environment rather than a production schema. Keep connection configuration outside the test code, use a technical user with only the required privileges, and make cleanup part of the test lifecycle. The Node.js and Java integration guide covers the application-to-database boundary that these tests exercise.

Test transaction behavior explicitly. A successful service call should commit the intended changes, while a failure in a later operation should leave the transaction in the expected state. Verify whether the service owns the transaction or whether the database procedure does so, because unclear ownership can produce partial updates or unexpected locks.

05Use deployment-aware troubleshooting

A test can pass locally while the deployed application fails because the runtime object, synonym, role, or service binding differs from the local environment. Compare the deployed artifact version, object names, generated database objects, application binding, and runtime user before changing application logic.

For HDI applications, treat the container deployment as part of the test path. Confirm that database artifacts deploy before application code that depends on them, that grants are available to the runtime user, and that the service binding points to the intended container. The SAP HANA HDI containers guide gives the deployment context for this workflow.

Use a small smoke test immediately after deployment. It should establish a connection, execute one read operation, execute one representative business operation in a controlled test area, and verify the expected response. A smoke test is not a substitute for unit tests; it detects missing bindings, grants, endpoints, or deployed artifacts.

Keep application, database, and configuration changes traceable to the same build or commit. When a failure appears after deployment, compare the last known-good build with the failing build and reproduce the smallest difference in a non-production environment.

06Read runtime evidence

Collect evidence before restarting services or changing several settings at once. Capture the request identifier, application log entry, database error text, user context, object name, and timestamp. Correlate these values across the application log and database diagnostics.

SAP HANA cockpit helps review system health, alerts, and related operational information. SAP HANA database explorer is useful for direct SQL execution and inspecting database objects during development. Use the least privileged account that can reproduce the issue, and remove credentials and personal data from shared diagnostic files.

Classify the failure before selecting a fix:

• Syntax or compilation: the object cannot be created or activated.
• Data logic: the object runs but returns an incorrect result.
• Authorization: the same operation succeeds under one identity and fails under another.
• Connectivity: the service cannot establish or reuse a database connection.
• Deployment: the expected object or binding is absent in the target environment.
• Performance: the result is correct but exceeds the response-time target.
• Concurrency: behavior changes when multiple requests run together.

This classification prevents a performance trace from being used to investigate a missing privilege or a code change from being used to address a broken service binding.

07Create a regression loop

Every resolved defect should produce a small regression test that fails against the old behavior and passes against the fix. Include the original input, expected result, relevant authorization context, and any required fixture data.

Run fast unit tests on every change, integration tests when database or service boundaries change, and deployment smoke tests after an artifact is promoted. Keep slow or environment-dependent tests separate so that developers receive rapid feedback without losing broader coverage.

Review failing tests for isolation problems before modifying application logic. A failure caused by shared data, execution order, time-dependent values, or an unfinished cleanup step is a test defect that should be corrected at the test boundary.

A practical debugging record contains the observed behavior, reproduction input, first failing layer, evidence collected, root cause, fix, and regression test. This record shortens future incidents and helps distinguish recurring environment problems from defects in SQLScript or service code.