2. Your first database

• Category: Tutorial

What you will learn

• What is inside a KDB database folder.
• How to use kdb shell (type SQL) and kdb sql (run SQL once).
• What the exit codes mean.

The examples use the database market with the sample data (see the quick start).

A database is a folder

A KDB database is one folder. You choose the name. When you open a folder that does not exist, KDB creates a new empty database there.

Get-ChildItem market -Name

files
wal
wal_archive
_trash
kdb.lock
_manifest.a
_manifest.b

Rules:

• Do not change or delete files in the folder by hand. Use kdb commands.
• To copy a database, stop every program that uses it first. Better: use kdb backup (chapter 9).
• Only one program can open a database at a time. While kdb serve runs, kdb sql on the same folder fails with "the database is open in another program". Use the server's HTTP API then (chapter 8).

kdb shell: type SQL

kdb shell <folder> opens a prompt. Type SQL and end it with ;. A statement can use many lines. The prompt ...> means "the statement is not finished yet".

PS D:\kdb-work> kdb shell market
KDB 0.2.0-dev shell on market
End statements with ';'. Type .help for commands, .exit to quit.
kdb> .help
  .tables              list tables
  .schema <table>      show CREATE TABLE
  .format table|csv|json
  .maxrows <n>         rows shown per query (0 = all)
  .check               read every file and report damage
  .backup              make a verified backup (needs --backups)
  .exit                quit
kdb> .tables
name         | kind       | partition | columns | rows
-------------+------------+-----------+---------+-----
brokers      | relational | NONE      |       2 |    5
trade_log    | timeseries | DAY       |       8 |   32
live_data    | timeseries | DAY       |      12 |   12
ochl_day     | timeseries | YEAR      |       7 |   57
ochl_minutes | timeseries | MONTH     |       7 |  956
securities   | relational | NONE      |       3 |    5
(6 rows, 0.0 ms)
kdb> SELECT symbol, close
...> FROM ochl_day
...> WHERE time IN '2026-09-10';
symbol | close
-------+------
CITY   | 504.9
COAST  | 376.1
VOLT   | 191.2
(3 rows, 0.4 ms)
kdb> .format csv
kdb> .schema brokers
create_sql
"CREATE TABLE brokers (
  id INT PRIMARY KEY,
  name STRING NOT NULL
)"
kdb> SELECT id, name FROM brokers LIMIT 3;
id,name
1,Keystone Securities
34,Vision Securities
45,Imperial Securities
kdb> .exit

Shell commands

Shell commands start with a dot. They do not need ;.

Tips:

• In table format, long text is cut after 48 characters (...). Use .format csv to see all of it, for example for .schema.
• Keywords and names do not care about case: select = SELECT, OCHL_DAY = ochl_day.
• Comments: -- to the end of the line and /* inside */.

kdb sql: run SQL once

kdb sql <folder> "SQL" opens the database, runs the SQL, prints the result and closes the database. This is good for scripts.

kdb sql market "SELECT count(*) AS minute_rows FROM ochl_minutes"

minute_rows
-----------
        956
(1 row, 0.5 ms)

Many statements

Separate statements with ;. KDB prints one result for each statement, with an empty line between them.

kdb sql market "SELECT count(*) AS securities FROM securities; SELECT count(*) AS brokers FROM brokers"

securities
----------
         5
(1 row, 0.2 ms)

brokers
-------
      5
(1 row, 0.1 ms)

If one statement fails, KDB stops there. The statements before it are already saved. The statements after it do not run. See chapter 4.

SQL from a file

Write the SQL in a file. Then send the file into kdb sql with a pipe. The - means "read the SQL from the pipe".

@"
-- report.sql: closing prices on the last day
SELECT symbol, close
FROM ochl_day
WHERE time IN '2026-09-10'
ORDER BY close DESC;
"@ | Set-Content report.sql -Encoding UTF8

Get-Content report.sql -Raw | kdb sql market -

symbol | close
-------+------
CITY   | 504.9
COAST  | 376.1
VOLT   | 191.2
(3 rows, 0.2 ms)

• Use -Raw. It sends the file as one piece of text.
• If the file has text in another language (not English), see chapter 7.
• In cmd.exe and bash you can write kdb sql market - < report.sql.

Options of kdb sql

Chapter 7 shows the formats in detail.

Exit codes

Every kdb command ends with an exit code. Scripts use it to know if the command worked.

In PowerShell the exit code is in $LASTEXITCODE:

kdb sql market "SELECT count(*) AS n FROM brokers"
"exit code: $LASTEXITCODE"
kdb sql market "SELECT count(*) FROM brokerz"
"exit code: $LASTEXITCODE"

n
-
5
(1 row, 0.2 ms)
exit code: 0
error: line 1, column 22: table or view 'brokerz' does not exist
exit code: 1

Error messages say where the problem is: line 1, column 22 is the place of brokerz in the SQL. In bash the exit code is in $?.

kdb shell ends with the exit code of the last statement or command you ran.

Try it yourself

1. Open the shell on market and list the columns of live_data.
2. With one kdb sql command, count the rows of trade_log and live_data.
3. Run kdb sql market "SELECT * FROM nothing". What is the exit code?

Next: 3. Tables