trdsql

trdsql is a CLI tool that can execute SQL queries on CSV, LTSV, JSON, YAML and TBLN files.

This tool is similar to others such as q and textql, with a key distinction: it allows the use of PostgreSQL or MySQL syntax.

For usage as a library, please refer to the godoc and the provided examples.

<!-- vscode-markdown-toc -->

• 1. INSTALL
  • 1.1. go get
    • 1.1.1. Requirements
  • 1.2. Download binary
  • 1.3. Homebrew
  • 1.4. MacPorts
  • 1.5. FreeBSD
  • 1.6. Cgo free
• 2. Docker
  • 2.1. Docker pull
  • 2.2. image build
  • 2.3. Docker Run
• 3. Usage
  • 3.1. Global options
  • 3.2. Input formats
    • 3.2.1. Input options
  • 3.3. Output formats
    • 3.3.1. Output options
  • 3.4. Handling of NULL
  • 3.5. Multiple queries
• 4. Example
  • 4.1. STDIN input
  • 4.2. Multiple files
  • 4.3. Compressed files
  • 4.4. Output file
  • 4.5. Output compression
  • 4.6. Guess by output file name
  • 4.7. Columns is not constant
  • 4.8. TSV (Tab Separated Value)
  • 4.9. LTSV (Labeled Tab-separated Values)
  • 4.10. JSON
    • 4.10.1. jq expression
  • 4.11. JSONL(NDJSON)
  • 4.12. YAML
  • 4.13. TBLN
  • 4.14. WIDTH
  • 4.15. TEXT
  • 4.16. Raw output
  • 4.17. ASCII Table & MarkDown output
  • 4.18. Vertical format output
• 5. SQL
  • 5.1. SQL function
  • 5.2. JOIN
  • 5.3. PostgreSQL
    • 5.3.1. Function
    • 5.3.2. Join table and CSV file is possible
  • 5.4. MySQL
  • 5.5. Analyze
  • 5.6. Configuration
• 6. Library
• 7. See also
• 8. Learn More
• 9. License

<!-- vscode-markdown-toc-config numbering=true autoSave=true /vscode-markdown-toc-config --> <!-- /vscode-markdown-toc -->

1. <a name='install'></a>INSTALL

1.1. <a name='go-get'></a>go get

go get -d github.com/noborus/trdsql
cd $GOPATH/src/github.com/noborus/trdsql
make
make install

1.1.1. <a name='requirements'></a>Requirements

go 1.21 or higher.

1.2. <a name='download-binary'></a>Download binary

Download binary from the releases page(Linux/Windows/macOS).

1.3. <a name='homebrew'></a>Homebrew

brew install noborus/tap/trdsql

1.4. <a name='macports'></a>MacPorts

sudo port selfupdate
sudo port install trdsql

1.5. <a name='freebsd'></a>FreeBSD

freshports

pkg install trdsql

1.6. <a name='cgo-free'></a>Cgo free

Typically, go-sqlite3 is used for building. However, if you're building with CGO_ENABLED=0, consider using sqlite instead.

Building without CGO (CGO Free) can reduce issues related to cross-compiling, but it may result in slower execution times.

2. <a name='docker'></a>Docker

2.1. <a name='docker-pull'></a>Docker pull

Pull the latest image from the Docker hub.

docker pull noborus/trdsql

2.2. <a name='image-build'></a>image build

Or build it yourself.

docker build -t trdsql .

2.3. <a name='docker-run'></a>Docker Run

Docker run.

docker run --rm -it -v $(pwd):/tmp trdsql [options and commands]

3. <a name='usage'></a>Usage

To use trdsql, you can either specify an SQL query or simply provide a file for conversion.

trdsql [options] SQL

For file conversion, this is equivalent to executing 'trdsql -o[output format] "SELECT * FROM filename"'.

trdsql -o[output format] -t [input filename]

3.1. <a name='global-options'></a>Global options

• -a filename analyze the file and suggest SQL.
• -A filename analyze the file but only suggest SQL.
• -config filename configuration file location.
• -db db name specify db name of the setting.
• -dblist display db list of configure.
• -driver string database driver. [ mysql | postgres | sqlite3 | sqlite(CGO Free) ] (default "sqlite3")
• -dsn string database driver specific data source name.
• -debug debug print.
• -help display usage information.
• -q filename read query from the specified file.
• -t filename read table name from the specified file.
• -version display version information.

3.2. <a name='input-formats'></a>Input formats

• -ig guess format from extension. (default)
• -icsv CSV format for input.
• -ijson JSON format for input.
• -iltsv LTSV format for input.
• -iyaml YAML format for input.
• -itbln TBLN format for input.
• -iwidth width specification format for input.
• -itext text format for input.

3.2.1. <a name='input-options'></a>Input options

• -ih the first line is interpreted as column names(CSV only).
• -id character field delimiter for input(default ",")(CSV only).
• -ijq string jq expression string for input(JSON/JSONL only).
• -ilr int limited number of rows to read.
• -inull string value(string) to convert to null on input.
• -inum add row number column.
• -ir int number of rows to preread. (default 1)
• -is int skip header row.

3.3. <a name='output-formats'></a>Output formats

• -ocsv CSV format for output. (default)
• -ojson JSON format for output.
• -ojsonl JSONL(JSON Lines) format for output.
• -oltsv LTSV format for output.
• -oat ASCII Table format for output.
• -omd Markdown format for output.
• -oraw Raw format for output.
• -ovf Vertical format for output.
• -oyaml YAML format for output.
• -otbln TBLN format for output.

Or, guess the output format by file name.

3.3.1. <a name='output-options'></a>Output options

• -out filename output file name.
• -out-without-guess output without guessing (when using -out).
• -oh output column name as header.
• -od character field delimiter for output. (default ",")(CSV and RAW only).
• -oq character quote character for output. (default """)(CSV only).
• -oaq enclose all fields in quotes for output(CSV only).
• -ocrlf use CRLF for output. End each output line with '\r\n' instead of '\n'."(CSV only).
• -onowrap do not wrap long columns(AT and MD only).
• -onull value(string) to convert from null on output.
• -oz string compression format for output. [ gzip | bz2 | zstd | lz4 | xz ]

3.4. <a name='handling-of-null'></a>Handling of NULL

NULL is undecided in many text formats. JSON null is considered the same as SQL NULL. For formats other than JSON, you must specify a string that is considered NULL. In most cases you will need to specify an empty string ("").

If -inull "" is specified, an empty string will be treated as SQL NULL.

SQL NULL is an empty string by default. Specify the -onull "(NULL)" option if you want a different string.

$ echo "1,,v" | trdsql -inull "" -onull "(NULL)" "SELECT * FROM -"
1,(NULL),v

In the case of JSON, null is NULL as it is, and the specified string is converted to NULL.

$ echo '[1,null,""]' | trdsql -inull "" -ojson -ijson "SELECT * FROM -"
[
  {
    "c1": "1"
  },
  {
    "c1": null
  },
  {
    "c1": null
  }
]

3.5. <a name='multiple-queries'></a>Multiple queries

Multiple queries can be executed by separating them with a semicolon. Update queries must be followed by a SELECT statement.

$ trdsql "UPDATE SET c2='banana' WHERE c3='1';SELECT * FROM test.csv"
1,Orange
2,Melon
3,banana

You can perform multiple SELECTs, but the output will be in one format.

$ trdsql -oh "SELECT c1,c2 FROM test.csv;SELECT c2,c1 FROM test.csv"
c1,c2
1,Orange
2,Melon
3,Apple
c2,c1
Orange,1
Melon,2
Apple,3

4. <a name='example'></a>Example

test.csv file.

1,Orange
2,Melon
3,Apple

Please write a file name like a table name.

trdsql "SELECT * FROM test.csv"

-q filename can execute SQL from file

trdsql -q test.sql

4.1. <a name='stdin-input'></a>STDIN input

"-" or "stdin" is received from standard input instead of file name.

cat test.csv | trdsql "SELECT * FROM -"

or

cat test.csv | trdsql "SELECT * FROM stdin"

4.2. <a name='multiple-files'></a>Multiple files

Multiple matched files can be executed as one table.

$