Rest API functional testing

JMeter provides JDBC Request Sampler which allows executing arbitrary SQL queries. You need to

1. Download relevant JDBC driver for your database management system and put it somewhere in JMeter Classpath (normally lib folder of your JMeter installation). JMeter restart will be required to pick the library up
2. Add JDBC Connection Configuration test element and specify database URL, credentials and other parameters if needed
3. Using JDBC Request sampler execute SQL query to validate that database contains the expected value(s)

See The Real Secret to Building a Database Test Plan With JMeter article for comprehensive instructions and configuration examples.

You could use JDBC Sampler & configurations for DB validation. However, I would suggest you to make use of other APIs (if any) to verify if they are present in the system/DB. some get request might bring the registered info.

I do a lot of API testing. sometimes we run these automated tests in higher environments like staging / PROD as part of sanity test after prod push. If you think that you might do something similar in the future, then you would not have prod DB config details - your test will be limited to run only in the lower environments & would not work in PROD. So, try to avoid DB validation.

Once you have successfully executed the API, add JDBC sampler after that, write query to count the number of rows in the db. If this count is growing which means the API is successfully inserting the data in the DB.

Once you have the count, write a beanshell script to print the count and compare it with older count and based on the comparison raise an assertion. This way you can be sure that the data is being inserted.

I will also recommend not to use this approach or any additional load when you are running your actual tests as the numbers you will get or system monitoring data you will collect will be with additional query which in turn will not be real life scenario or your actual test plan.

The best practices for API testing involve checking the result of the API call against an SQL query result.

Use the above details for setting your db connection.

I second with Vins. Using database validation to assert the reaponse from API limits the test capabilities and can not be scaled to higher environments where you have limited or no access. Also you can not reuse the functional tests to run load tests as the number of users increases the more data gets inserted into database and slows down the test execution as more data pump in.

Also might be case that some select queries gets struck on as data set is more or due to network bandwidth or less memory alloxated to Jmeter

You might also face out of memory errors in java as it keeps trying to garbage collection to accomodate the large data set Recommended apporach is to use front end validations if available and wherever applicable or make use of other APIs query and validate the data.