1

I've been wanting to develop some unit tests for my ORDS functions as part of my push for better CI/CD - I am running ORDS functions on an Oracle Autonomous Database 19c, and while I'm developing a testing suite for the functions I've developed, I am probably not going about it the right way.

I've done a lot of googling on the topic of specifically testing ORDS functions and there doesn't seem to be much out there that I could find, so I was hoping people on here might have some experience with this :)

I have been introduced to a couple of methods on another question (utPLSQL and a method on SQLDeveloper) but they seem to only test the code - not the restful services, as well as requiring other setup that I have not done - I just have the code in ORDS. I also don't have all my queries in PL/SQL, some of them are in SQL such as my most basic ORDS function:

-- GET REQUEST
-- gets all client info ordered by name
select * from clients order by name asc

Ideally I would just have some code that ran through the ORDS module of my choice and told me if it passed the tests I define.

I'm currently wondering whether to have a script to:

  1. Set up a database with new schema etc and populate it with dummy data
  2. Run all the tests
  3. Drop all the tables to get back to before

Although that sounds like a hassle, it'll also take more and more time to run, seeing as it'll be doing many REST API calls by definition, while unit tests are supposed to be over and done with very quickly.

Things I'm wanting to test are:

  1. Authentication is working properly (should return 401 error when there is no auth and when auth is incorrect.
  2. Insert functions work as intended
  3. Select functions work as intended - this is a key one as they are the most complicated functions we have at the minute
  4. Delete functions work as intended

I'm quite new to unit testing with SQL, my previous experience being with JUnit and some Python/C++ unit testing libraries, but it does seem like there's an easier way, whether that be something that others have done in my situation, or whether there are steps I should take to make my own code more friendly to the system that already exists.

I'm also definitely open to the idea of having more than one set of tests, that run in different environments if there is a good case to do so. I'm still getting around CI/CD in general with databases so knowing where to start is a big help.

Improve this question
2
  • Just saw this. Did you get something sorted? or I can outline how we're showing people to use PDB + SQLcl for doing CI/CD with database things.
    – Kris Rice
    Commented Aug 26, 2021 at 19:33
  • Hi Kris! Please go ahead, I ended up doing some behemoth script that took a while but looped through the lot if I remember correctly, it's been a while since I worked on this particular project :)
    – Ash Oldershaw
    Commented Aug 31, 2021 at 12:34

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.