Extending UNIX grep to databases

Figure 962. UNIX grep sample Slide presentation
> grep --color  -i  fraction App.java
package de.hdm_stuttgart.mi.sd1.fraction;
 * Playing with fraction objects.
    final Fraction threeSeven = 
        new Fraction(3, 7);
    final Fraction

Colorize matched text in output

Ignore case when matching text.


The grep command among with its myriad of options allows searching text files for the occurrence of strings or patterns. This can be extended both to RDBMS and non-SQL databases like Mongodb.

The database query problem however is more complex than simply query lines of text files. A practically useful CLI must address:

Figure 963. Database grep requirements Slide presentation
  • Connection profile handling

  • Search level specification:

    • Whole database

    • Table(s)

    • Table column(s)

    • Recordsets

  • Output formatting, limiting/filtering and paging


Figure 964. Connection profile handling Slide presentation
Command File ~/.dbgrep/Profiles/postgresTest.cfg
dbgrep --profile postgresTest ...
host=myserver.companynet.com
port=5432
database=main_test_data
user=testuser
password=secret
driver=Driver/postgresql-42.6.0.jar

Figure 965. Search level specification Slide presentation
dbgrep ... 
dbgrep ... --table User --table Stocks ...
dbgrep ... --column User.userId  ...
dbgrep ... --table Stocks --column User.userId ... 

Search whole database i.e. all tables (and views?).

Search all compatible columns from tables User and Stocks.

Search column userId from table User.

Combined search of and .


Figure 966. Property based searches Slide presentation
dbgrep ... --equal 237 
dbgrep ... --greater 4.43 
dbgrep ... --like 'Smit%' 
dbgrep ... --like 'Smit%' --and --greater 4 
dbgrep ... --range [-3:17] 

Search for integer values equal to 237. Return either of:

  • All records from all tables containing an integer type value of 237.

  • A list of all tables among with column names of integer type containing at least one value of 237 but not the records themselves. This resembles the grep -l ... aka --files-with-matches argument.

Search for numeric values being greater than 4.43.

Texts starting with Smit.

Conjunction: Records containing text starting with Smit and at least one numerical value being greater than 4.

Search for integer values between and including -3 and 17.


A tool might be implemented either as a single command or as a terminal based application keeping state information.