If you work with SQLite databases in Kubernetes or OpenShift, you’re probably familiar with using the remote shell (rsh) feature to access the database and run queries on the pod directly. The remote shell is great for one-off tasks with a table or exploratory work, but it will break your workflow and chain of thought when working with automation or running tasks on your local system.

But you can use the exec command in oc and kubectl to query the database and return results directly int to your local environment, making tasks faster, easier, and more fluid. This allows for automation and maintains your mental flow state.

Comparison between remote shell (rsh) and remote execute (exec)

Here is a side-by-side example of both techniques. Notice how the left side (rsh) has six distinct commands and the right side (remote exec) has just one.

Remote shell

Newlines have been added between commands so you can easily see individual commands within the remote shell. The command must be entered at each step (open the database, run the query, exit the database, exit the remote shell).

# use kubectl or oc 'rsh' to get a remote shell on the pod, then start sqlite3
oc --namespace=ste4site-test rsh svc/db-test

(app-root) sh-4.2$ sqlite3 /dev/shm/example.db

SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

sqlite> select * from fauna;
1|Lion|Panthera leo|Felidae|Savannah
2|Tiger|Panthera tigris|Felidae|Jungle
3|Elephant|Loxodonta africana|Elephantidae|Savannah
4|Giraffe|Giraffa camelopardalis|Giraffidae|Savannah
5|Zebra|Equus quagga|Equidae|Savannah
6|Panda|Ailuropoda melanoleuca|Ursidae|Forest
7|Kangaroo|Macropus rufus|Macropodidae|Outback
8|Penguin|Aptenodytes forsteri|Spheniscidae|Antarctica
9|Polar Bear|Ursus maritimus|Ursidae|Arctic
10|Wolf|Canis lupus|Canidae|Forest

sqlite> .exit

(app-root) sh-4.2$ exit

exit

Once the query results are on your screen, you copy the results from your terminal window. This is fine for exploratory or one-time tasks, but we can do the same thing faster with less work using exec instead of rsh.

Remote exec

Here we run one command on the remote pod and its output is returned to the current shell. Notice that we need to use the full path to the binary (/usr/bin/sqlite3) and the database (/dev/shm/example.db). The remote exec technique puts the query output directly into your local console, meaning you can capture the query output stream into a variable.

# Use kubectl or oc 'exec' command to run a command on a pod and return its output
oc -namespace=ste4site-test rsh svc/db-test -- /usr/bin/sqlite3 /dev/shm/example.db "select * from flora"
1|Rose|Rosa|Rosaceae|Garden
2|Lily|Lilium|Liliaceae|Garden
3|Tulip|Tulipa|Liliaceae|Garden
4|Daisy|Bellis perennis|Asteraceae|Meadow
5|Sunflower|Helianthus annuus|Asteraceae|Field
6|Daffodil|Narcissus|Amaryllidaceae|Garden
7|Orchid|Orchidaceae|Orchidaceae|Greenhouse
8|Iris|Iris|Iridaceae|Garden
9|Violet|Viola|Violaceae|Woodland
10|Lavender|Lavandula|Lamiaceae|Garden

This is perfect if you want a command in your notes that you can copy and paste into a terminal. Notice how the namespace is provided explicitly, so you don’t have to think about which project namespace is currently in use. This is how I store this kind of information notes in my notes:

Capturing output from commands run on pods

You can process the output directly, capture it as a variable, or convert it to an object (in PowerShell). For example, here we capture the query output into the variable $Records for use later.

# Bash
Records=`oc -namespace=ste4site-test rsh svc/db-test -- /usr/bin/sqlite3 /dev/shm/example.db "select * from flora"`

# PowerShell
$Result = oc -namespace=ste4site-test rsh svc/db-test -- /usr/bin/sqlite3 /dev/shm/example.db "select * from flora"

Adding parameters to the remote command

When calling SQLite from the command line, use the following parameters to make results more human-readable:

  • -header: print column headers
  • -column: pad and align column values, add lines under column names

Now it’s nicely formatted for your eyes:

oc --namespace=ste4site-test exec svc/db-test -- /usr/bin/sqlite3 /dev/shm/example.db -header -column "select * from flora"

id          common_name  scientific_name  family      location
----------  -----------  ---------------  ----------  ----------
1           Rose         Rosa             Rosaceae    Garden
2           Lily         Lilium           Liliaceae   Garden
3           Tulip        Tulipa           Liliaceae   Garden
4           Daisy        Bellis perennis  Asteraceae  Meadow
5           Sunflower    Helianthus annu  Asteraceae  Field
6           Daffodil     Narcissus        Amaryllida  Garden
7           Orchid       Orchidaceae      Orchidacea  Greenhouse
8           Iris         Iris             Iridaceae   Garden
9           Violet       Viola            Violaceae   Woodland
10          Lavender     Lavandula        Lamiaceae   Garden

This is perfect if you want a command in your notes that you can copy and paste into a terminal.

Store pre-made commands in your notes app

Notice how the namespace above is provided explicitly, so you don’t have to think about which project namespace is currently in use. This is how I store this kind of information notes in my notes:

Get all rows from the flora table

# DEV 
oc --namespace=ste4site-dev exec svc/db-dev -- /usr/bin/sqlite3 /dev/shm/example.db -header -column "select * from flora"

# TEST
oc --namespace=ste4site-test exec svc/db-testtest -- /usr/bin/sqlite3 /dev/shm/example.db -header -column "select * from flora"

# PROD
oc --namespace=ste4site-prod exec svc/db-prod -- /usr/bin/sqlite3 /dev/shm/example.db -header -column "select * from flora"

Storing explicit commands for each environment is an important step to making your notes easy to read so you don’t accidentally copy and paste the wrong command. Make it easier on your future self by clearly separating commands for all your environments, so when you’re focused on a task you can find what you need quickly, use it, get the results, and move on. Maintaining your focus while working is critical.

It will also make it fast and easy if you want to share a command with a co-worker. You will be able to copy the command (including the comment) and paste it into a chat or email. The command will be narrowly focused and it should be clear what it does. If you or your co-worker come back to the conversation thread later, it will clear what you were doing at the time. For example:

2023-05-13 09:22: Hey Jo, use this command to get flora in the DEV environment:

# DEV
oc --namespace=ste4site-dev exec svc/rdb-dev -- /usr/bin/sqlite3 /dev/shm/example.db -header -column "select * from flora"

Jo can see exactly what the command does and get results quickly by pasting the command into a terminal window. You’ve made the task faster and easier for yourself and Jo, in the present and in the future if you have to revisit it later.

Processing the command output

Now that you can run SQLite database queries on a remote pod and get the results directly, you can easily automate tasks or reports. You just need to have a valid authentication token. You can use this on your local system, or as part of a continuous deployment or continuous integration process.

Example 1: convert SQL results to CSV format

If you need to convert the results to CSV format, you can process each line separately and replace the pipe characters with commas using the PowerShell ForEach-Object command:

# PowerShell
oc --namespace=ste4site-test exec svc/db-test -- /usr/bin/sqlite3 /dev/shm/example.db -header "select * from flora" | ForEach-Object { ($_ -split '\|' -join ",")} 

id,common_name,scientific_name,family,location
1,Rose,Rosa,Rosaceae,Garden
2,Lily,Lilium,Liliaceae,Garden
3,Tulip,Tulipa,Liliaceae,Garden
4,Daisy,Bellis perennis,Asteraceae,Meadow
5,Sunflower,Helianthus annuus,Asteraceae,Field
6,Daffodil,Narcissus,Amaryllidaceae,Garden
7,Orchid,Orchidaceae,Orchidaceae,Greenhouse
8,Iris,Iris,Iridaceae,Garden
9,Violet,Viola,Violaceae,Woodland
10,Lavender,Lavandula,Lamiaceae,Garden

Example 2: convert SQL results to a .NET table object

See Convert oc command output to a PowerShell table. In PowerShell, you can convert the query results to a .NET table using the PowerShell ConvertFrom-Csv cmdlet to query the results directly and access the individual cells as properties. For example, this command converts the CSV output from above into a .NET object:

oc --namespace=ste4site-test exec svc/db-test -- /usr/bin/sqlite3 /dev/shm/example.db -header "select * from flora limit 3" | ForEach-Object { ($_ -split '\|' -join ",")} | ConvertFrom-Csv


id              : 1
common_name     : Rose
scientific_name : Rosa
family          : Rosaceae
location        : Garden


id              : 2
common_name     : Lily
scientific_name : Lilium
family          : Liliaceae
location        : Garden


id              : 3
common_name     : Tulip
scientific_name : Tulipa
family          : Liliaceae
location        : Garden

Manipulate this object as needed. For example, here we use the Format-Table cmdlet to show just the common_name and location columns:

oc --namespace=ste4site-test exec svc/db-test -- /usr/bin/sqlite3 /dev/shm/example.db -header "select * from flora limit 3" | ForEach-Object { ($_ -split '\|' -join ",")} | ConvertFrom-Csv | Format-Table -Property common_name, location


common_name location
----------- --------
Rose        Garden
Lily        Garden
Tulip       Garden

Tested with:

  • PowerShell 7.3.4
  • SQLite 3.7.17
  • oc client 4.7.0
  • Kubernetes version 1.24.6.
  • kubectl