How to test REST API where major bussiness logic is written in SQL

You have a couple of options:

• Use a test framework for SQL like pgTAP
• Analyze the database logic and test each set of inputs that can change the behavior.

There's not much to say about the first option. For the second, think of an API endpoint that creates a reservation for a hotel room. Your tests should initialize the database with one or more users and one or more rooms (shameless plug: I wrote a thing that makes that a lot more manageable).

Your basic test: given I already have users and rooms, I should be able to pass a request body with a user id, room id, and check in/out dates to my endpoint. After a successful request, I can retrieve a record from reservations matching my parameters. Access the database directly for setup and verification, but otherwise treat it as a "black box" and make sure you think through the different paths your logic takes based on the inputs you pass it.

To start TDD for your Express REST API using raw SQL, first install testing packages like Mocha, Chai, and Supertest. Write a test in a file like tests/api.test.js, checking an endpoint that calculates business days. For example, you can assert that the endpoint returns the correct number of business days for given dates.

Next, implement the corresponding route in your Express app, where you handle the logic for calculating business days. Ensure that your app returns the expected JSON response. Add a test script in your package.json to run the tests easily. Finally, run your tests and, once they pass, refactor your code as needed, making sure the tests still succeed. This approach helps you build reliable business logic in your API.