Unit testing SQL scripts

Question

For below script written in .sql files:

if not exists (select * from sys.tables where name='abc_form')
    CREATE TABLE abc_forms (
        x BIGINT IDENTITY, 
        y VARCHAR(60), 
        PRIMARY KEY (x)
    )

Above script has a bug in table name.

For programming languages like Java/C, compiler help resolve most of the name resolutions

For any SQL script, How should one approach unit testing it? static analysis...

Write some batch scripts to run it through the sql plus command line and pipe any ORA errors to an output that you can then look at manually or automatically. — Sam M
– Sam M, Commented Mar 15, 2019 at 14:14
@SamM What about adding rules in Sonar using XPATH or Java to perform static code analysis? To avoid manual code(script) reviews — overexchange
– overexchange, Commented Mar 15, 2019 at 14:35
@overexchange I'm not familiar with that technology. Are you scripts all pure sql or are they pl/sql? — Sam M
– Sam M, Commented Mar 15, 2019 at 15:56

PowerStat · Accepted Answer · 2019-08-07 07:12:20Z

15 years ago I did something like you request via a lot of scripting. But we had special formats for the statements.

We had three different kinds of files:

One SQL file to setup the latest version of the complete database schema
One file for all the changes to apply to older database schema's (custom format like version;SQL)
One file for SQL statements the code uses on the database (custom format like statementnumber;statement)

It was required that every statement was on one line so that it could be extracted with awk!

1) At first I set up the latest version of the database by executing from statement after the other and logging the errors to a file.

2) Secondly I did the same for all changes to have a second schema

3) I compared the two database schemas to find any differences

4) I filled in some dummy test values in the complete latest schema for testing

5) Last but not least I executed every SQL statement against the latest schema with test data and logged every error again.

At the end the whole thing runs every night and there was no morning without new errors that one of 20 developers had put into the version control. But it saved us a lot of time during the next install at a new customer.

Tigerware · Accepted Answer · 2021-07-22 08:28:54Z

1

You could also generate the SQL scripts from your code.

Code first avoids these kinds of problems. Choosing between code first or database first usually depends on whether your main focus is on your data or on your application.

answered Jul 22, 2021 at 8:28

Tigerware

3,9723 gold badges27 silver badges42 bronze badges

1 Comment

overexchange Over a year ago

How generating SQL scripts will resolve this issue? by writing unit test case?

Collectives™ on Stack Overflow

Unit testing SQL scripts

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related