Feature Request: Spawn a regular database shell using a squix connection

[chasinglogic]

I'm a Site Reliability Engineer and I use squix as my primary database client.

However, sometimes it would be really nice to just get a proper psql or mysql repl. Especially when I need to do a bunch of maintenance commands in a row as I don't want to put them all in one script since I need to check they all complete correctly before moving on (because it's production).

For that use case the SQL editor is a bit cumbersome so it would be nice if either squix could have it's own "repl/shell mode" or if it could simple spawn the appropriate CLI client for me using the saved connection information.

If you express which direction you would prefer I'd be happy to send a PR for this feature.

[eduardofuncao]

Thanks a lot for using squix! It's always a pleasure to hear of people using it in a production environment!

That's an amazing idea, I also have this problem when trying to create new queries or just to test things out, it becomes a little cumbersome to use the editor or needing to type squix run "<select-statement>" everytime. I threw together a really simple bash wrapper to test a possible shell mode for squix:

#!/usr/bin/env bash
squix status
echo "squix shell -> type queries, 'exit' to quit"
while IFS= read -r -p "squix> " query; do
  [[ "$query" == "exit" ]] && break
  [[ -z "$query" ]] && continue
  squix run "$query"
done

Is this similar to what you were imagining? We could implement something like this in go, creating the squix shell command for example.

PS. with this simple implementation, parameterized queries are not working correctly, but I'm sure it would be possible. We'd also have to make sure most of the squix run modes would work well.

[chasinglogic]

That would be perfect and even better than just spawning the actual database client IMO.

[eduardofuncao]

@chasinglogic nice! I am working on #30 and #35 right now, but I can definitely pick this one up next.

If you want to give it a go, you are more than welcome!

[eduardofuncao]

Hey @chasinglogic, just pushed the first version of squix shell to main with 6727cd7

All run commands should work (including running named queries and parameterized queries). The REPL will keep adding new lines to the queries until a ; char for multi-line queries, or if you press Enter twice in a row.

Running status and list <search-term> will also work.

I didn't find an easy way to get the completion for query or conn names working, but I think we could add this part in a future version.

I'll keep the issue opened for now, but I will include this in the v0.4.0-beta release, which should happen soon!

[chasinglogic]

Hey @eduardofuncao the nix build is broken so I can't test it at this moment. I'm looking into fixing the nix build for you

[chasinglogic]

I've submitted this PR to fix the nix build: #39

Will start testing the shell

[chasinglogic]

Here is the notes from my testing, overall this is pretty great. I've ordered the issues in priority for how much they affect the actual usability of the new shell command:

• With no tab complete the tables command needs to work in the shell because things like \dt also don't work which means you can't view the table list while in the shell.
• Pressing save in the result set after running a raw query like select * from users limit 1 doesn't populate the file with the query I just ran. In general some of the TUI table stuff is a bit confusing in the shell mode but most of it would be quite useful if it can work better contextually.
• The result table needs a good way to export the entire result set. By default it selects a single column from a single row and vsel'ing a big result set would be quite tedious.

and a very minor bug is when status is run or after first connecting (which runs status) the cursor is in front of the prompt which is just a bit confusing; it doesn't break anything as typing immediately moves the cursor to the correct position. Here is a screenshot:

[eduardofuncao]

That was my bad not updating the vendor hash, thanks a lot for including CI to the nix build as well

[eduardofuncao]

Something I thought about for making easier full table exports, was adding the uppercase V keybind on the results view, which would select the whole line. Then you would only need to press VG from the top row like in vim to select everything. what do you think?

Something else I'm working on and that might help is an option to export results for the run command to stdout instead of opening the table view. for example, squix run test_query --format json would export the full query result as json to stdout (related to #11).

If you have more general suggestions like this that would help your day to day, please shot them at me! I think they can be really helpful since you're also using squix as your main db client.

[eduardofuncao]

As for the squix shell issues, that was also my bad! I'll make sure all keybinds in the table view ar e functional and that we can use \dt or tables as well. And see if there's an easy way to move the cursor over when starting the shell.

I should put in the time to wire up the completion commends inside the shell as well, at least to autocomplet query names and available shell backslash commands.

One thing that would be really good in general as well would be to have table name completion. Querying for all table names sometimes takes a while though, so it would need to have some sort of caching to work flawlessly.

Thanks again for all the feedback! I'll create the child issues now so we can track everything ;)

[chasinglogic]

I think your vim "select all" addition is a good one. The only thing I would maybe suggest is X for just exporting the whole table instead of the selection as an additional change. For two reasons really:

1. I may be exploring the data with squix and don't want to lose my place to export it all
2. For large result sets I would be concerned that VG would be slow / unusable (think like 100k or 1M rows)

Also a side note the cursor over prompt issue happens when you status as well so I think it's related to that instead of specifically startup