MCLIENT(1)

📦
version 11.55.7 · 2 versions
, in nixpkgs unstable

💅 MCLIENT(1) — the MonetDB command-line tool, explained by your favorite unix girlie

okay so real talk, this is mclient(1) from the MonetDB Applications manual, and by the end of this you're gonna be obsessed. periodt.

✨ NAME ✨

mclient — the MonetDB command-line tool. that's it. that's the icon.

🫶 SYNOPSIS 🫶

mclient [ options ] [ file or database [ file ... ] ]
mclient --help

🌸 DESCRIPTION 🌸

so first, the vibe check on MonetDB itself: it's a database management system built from a main-memory perspective, using a fully decomposed storage model, automatic index management, extensibility of data types and search accelerators, and an SQL front end. no cap, it's giving engineering excellence.

mclient is the command-line interface to the MonetDB server. it's literally the main character of your terminal.

Here's how it decides what to run, bestie:

  • if the --statement=query (-s query) option is given, that query gets executed.
  • if any files are listed after the options, queries get read from those files and executed.
  • the special filename - means standard input. iykyk.
  • lowkey important: if there's both a --statement option and filename arguments, the query given with --statement runs first.
  • if no --statement is given and no files are specified on the command line, mclient reads queries from standard input. default behavior, no notes.

When reading from standard input — if standard input is a terminal or if the --interactive (-i) option is given — mclient interprets lines starting with \ (backslash) specially. see the BACKSLASH COMMANDS section below. (that's later in the page, not here, keep scrolling energy.)

🎀 the config file lore 🎀

Before mclient even starts parsing command line options, it reads a configuration file. the order is very specific, so pay attention:

  1. If the environment variable DOTMONETDBFILE is set and not empty, it reads the file pointed to by that variable.
  2. If it's set but empty, no configuration file is read. at all. nothing.
  3. If it's unset, mclient first looks for a file .monetdb in the current working directory. if that doesn't exist, it looks for a file monetdb in the XDG configuration directory ($XDG_CONFIG_HOME, which defaults to $HOME/.config if not set). and finally for a file .monetdb in the current user's home directory.

This file can contain defaults for the flags user, password, language, database, save_history, format, host, port, and width. slay, a whole defaults system.

For example, an entry in a .monetdb file that sets the default language for mclient to mal looks like this:

language=mal

To disable reading the .monetdb file, set the variable DOTMONETDBFILE to the empty string in the environment. bye felicia.

💅 the interrupt moment 💅

When working interactively, an interrupt (usually Ctrl+C) will clear any query being edited and will stop any running query. genuinely so useful, we love a clean escape.

⭐ OPTIONS ⭐

💖 General Options 💖

  • --help, -? — print usage information and exit. your lil cheat sheet.
  • --version, -v — print version information and exit. for when you gotta know.
  • --quiet, -q — be quiet: don't print the welcome message at startup. no small talk, just vibes.
  • --encoding=encoding, -E encoding — specify the character encoding of the input. heads up though, this option applies to both the standard input of mclient and to the argument of the --statement (-s) option, but not to the contents of files specified on the command line (except for -, which refers to standard input) or to files specified using the \< command — those must be encoded using UTF-8. the default encoding is taken from the locale.
  • --language=language, -l language — specify the query language. the following languages are recognized: mal and sql. a unique prefix suffices. when the --language option is omitted, the default of sql is assumed. iconic default tbh.
  • --database=database, -d database — specify the name or URI of the database to connect to. the -d can be omitted if an equally named file does not exist in the current directory. as such, the first non-option argument will be interpreted as database to connect to if the argument does not exist as file. valid URIs are as returned by monetdb discover, see monetdb(1), and look like mapi:monetdb://hostname:port/database. fancy ✨
  • --host=hostname, -h hostname — specify the name of the host on which the server runs (default: localhost). when the argument starts with a forward slash (/), host is assumed to be the directory where the UNIX sockets are stored, for platforms where these are supported.
  • --port=portnr, -p portnr — specify the portnumber of the server (default: 50000).
  • --interactive, -i — when reading from standard input, interpret lines starting with \ (backslash) specially. see the section BACKSLASH COMMANDS below.
  • --timer=timermode, -t timermode — the timer command controls the format of the time reported for queries. the default mode is none, which turns off timing reporting. the timer mode clock reports the client-side wall-clock time (clk) in a human-friendly format. the timer mode performance reports client-side wall-clock time (clk) as well as detailed server-side timings, all in milliseconds (ms): the time to parse the SQL query, optimize the logical relational plan and create the initial physical (MAL) plan (sql); the time to optimize the physical (MAL) plan (opt); the time to execute the physical (MAL) plan (run). all timings are reported on stderr.
note that the client-measured wall-clock time is reported per query only when options --interactive or --echo are used, because only then does mclient send individual lines (statements) of the SQL script to the server. otherwise, when mclient sends the SQL script in large(r) batch(es), only the total wall-clock time per batch is measured and reported. the server-measured detailed performance timings are always measured and reported per query. 💀 so specific, so important.
  • --user=user, -u user — specify the user to connect as. if this flag is absent, the client will ask for a user name, unless a default was found in the .monetdb or $DOTMONETDBFILE file.
  • --format=format, -f format — specify the output format. the possible values are sql, expanded, x, csv, csv-noquote, tab, raw, xml, trash, and rowcount. here's the rundown, bestie:
    • csv is comma-separated values.
    • csv-noquote is comma-separated values without escaping any quotes.
    • tab is tab-separated values.
    • raw is no special formatting (data is dumped the way the server sends it to the client).
    • sql is a pretty format meant for human consumption where columns are clearly shown.
    • expanded and x are synonyms and are another pretty format meant for human consumption where column values are printed in full and below each other.
    • xml is a valid (in the XML sense) document.
    • trash does not render any output, enabling performance measurements free of any output rendering/serialization costs.
    • rowcount is a variation on trash where only the number of affected rows is printed.

Normal csv and tab formatting will use double quotes around any fields that contain double quotes, white space or the separator. the csv-noquote format will prevent that and dump the contents of the field without any interpretation. in addition to plain csv, two other forms are possible: csv=c uses c as column separator; csv+c uses c as column separator and produces a single header line in addition to the data. 🦋

  • --echo, -e — echo the query. note that using this option slows down processing. (worth it sometimes, just saying.)
  • --history, -H — if compiled with the readline(3) library, load and save the command line history (default off).
  • --log=logfile, -L logfile — save client/server interaction in the specified file. receipts, literally.
  • --statement=stmt, -s stmt — execute the specified query. the query is run before any queries from files specified on the command line are run.
  • --timezone, -z — do not tell the client's timezone to the server.
  • --Xdebug, -X — trace network interaction between mclient and the server. peek behind the curtain ✨
  • --pager=cmd, -| cmd — send query output through the specified cmd. one cmd is started for each query. note that the | will have to be quoted or else the shell will interpret it. (shell girlies know.)

🍓 SQL Options 🍓

  • --null=nullstr, -n nullstr — set the string to be used as NULL representation when using the sql, csv, or tab output formats. if not used, NULL values are represented by the string "null" in the sql output format, and as the empty string in the csv and tab output formats. note that an argument is required, so in order to use the empty string, use -n "" (with the space) or --null=. don't forget the space, bestie.
  • --autocommit, -a — switch autocommit mode off. by default, autocommit mode is on. so this flag is a little chaos gremlin, but a useful one.
  • --allow-remote, -R — allow remote content (URLs) in the COPY INTO table FROM file ON CLIENT ... query. remote content is retrieved by mclient.
  • --rows=nr, -r nr — if specified, query results will be paged by an internal pager at the specified number of lines. if set to 0 (zero), use the height of the terminal. the default is -1, which means no pager is used.
  • --width=nr, -w nr — specify the width of the screen. the default is the (initial) width of the terminal. 🎀
  • --dump, -D — create an SQL dump.
  • --inserts, -N — use INSERT INTO statements instead of COPY INTO + CSV values when dumping the data of a table. this option can be used when trying to load data from MonetDB into another database, or when e.g. JDBC applications are used to reload the dump.

(part 1 done, but like... there's more, don't go anywhere 💜)

🎀 BACKSLASH COMMANDS 🎀

okay so these are the little backslash thingies you type right into the client — no semicolon needed, they're client-side ✨

General Commands

  • \? — pops up a help message that explains all the backslash commands. your built-in cheat sheet, bestie 💅
  • \q — exits mclient. bye bestie 🦋
  • \< file — reads input from the named file.
  • \> file — writes output to the named file. if you don't name a file, it writes to standard output instead.
  • \| command — pipes output to the given command. heads up tho: each query gets piped to a new invocation of the command 😭 and if you don't give a command, it reverts to writing output to standard output.
  • \h — shows the readline(3) history. all your past queries, right there 👀
  • \L file — logs client/server interaction into the given file. if you don't name a file, it stops logging information.
  • \X — traces what mclient is doing. mostly for debugging purposes, so like, dev mode 🧚
  • \e — echoes the query in SQL formatting mode.
  • \f format — uses the specified format mode to format the output. the possible modes are the same as for the --format (-f) option 💖
  • \w width — sets the max page width when rendering in the sql formatting mode. if width is -1 the page width is unlimited, if width is 0 it uses the terminal width, and if width is greater than 0 it uses that given width. pick your vibe ✨
  • \r rows — uses an internal pager with that many rows per page. if rows is -1 it stops using the internal pager, and if rows is 0 it uses the height of the terminal.

SQL Commands

  • \D — dumps the complete database. this is equivalent to using the program msqldump(1) 👑
  • \D table — dumps the given table.
  • \d — alias for \dvt.
  • \d[Stvmsfn]+ — lists database objects of the given type. you can stack multiple type specifiers at the same time, no cap. the specifiers S, t, v, m, s, f and n stand for System, table, view, merge table, sequence, function and schema respectively. note that S just switches on viewing system catalog objects, which is orthogonal to the other specifiers 🌀
  • \d[Stvsfnm]+ object — describes the given object in the database using SQL statements that reconstruct the object. same specifiers as above, same rules too. when you give no specifiers, vt is assumed. the object can be given with or without a schema, separated by a dot. the object name can contain the wildcard characters * and _, which represent zero or more, and exactly one character respectively. an object name gets converted to lowercase, unless it's quoted with double quotes ("). examples of this are e.g. *.mytable, tabletype*, or "myschema.FOO". note that wildcard characters do not work in quoted objects, and quoting follows SQL quoting rules: arbitrary parts can be quoted, and two quotes following each other in a quoted string represent the quote itself 💀
  • \A — enables auto commit mode.
  • \a — disables auto commit mode.

✨ EXAMPLES ✨

so you wanna import data from a CSV (comma-separated values) file into a table, efficiently? say less 🍓

Method 1: the file has to be readable by the server

here $file is the absolute path name of the file, $table is the name of the table, and $db is the name of the database:

mclient -d $db -s "COPY INTO $table FROM '$file' USING DELIMITERS ',',E'\\n','\"'"

Method 2: when the file is read by mclient itself

this one's for when the server has no access to the file 🙈 $file is the (absolute or relative) path name of the file, $table is the name of the table, $db is the name of the database:

mclient -d $db -s "COPY INTO $table FROM STDIN USING DELIMITERS ',',E'\\n','\"'" - < $file
⚠️ note that in this latter case, if a count of records is supplied, it should be at least as large as the number of records actually present in the CSV file. this, because otherwise the remainder of the file will be interpreted as SQL queries 😭 literally not what you want.

Method 3: another, easier way

another, easier method to have the client read the file content is as follows:

mclient -d $db -s "COPY INTO $table FROM '$file' ON CLIENT USING DELIMITERS ',',E'\\n',\"'"

in this case the value of $file can be a path name relative to the directory in which mclient was started. and if, in addition, the option --allow-remote is passed to mclient, the $file in the above query can also be a URL 🌈 it then has to have the form schema://string, e.g., https://www.example.org/dumpdata.csv.

for more information about the COPY INTO query, see https://www.monetdb.org/documentation/user-guide/sql-manual/data-loading/copy-from/ 📚

🌈 SEE ALSO 🌈

msqldump(1), mserver5(1) — go give them a read bestie, iykyk 🫶

and that's the whole page, babes 💖 this was MonetDB, dated 2025-12-09, and you were reading MCLIENT(1) — iconic, no notes, periodt 👑✨

📦 where this page comes from