There are two ways to run SQL queries against your database:
dolt sql runs SQL queries from your shell
dolt sql-server starts a MySQL-compatible server
dolt sql you can issue SQL statements against a local database without starting a server.
With no arguments,
dolt sql begins an interactive shell.
% dolt sql# Welcome to the DoltSQL shell.# Statements must be terminated with ';'.# "exit" or "quit" (or Ctrl-D) to exit.menus> show tables;+------------+| Table |+------------+| menu_items |+------------+menus> exitBye
-q flag, it executes queries specified as arguments.
% dolt sql -q "show tables"+------------+| Table |+------------+| menu_items |+------------+
You can also feed a file of queries to the
dolt sql command with standard input redirection. This is useful for importing a dump from another database.
% dolt sql < mysqldump.sql
dolt sql command documentation here.
dolt sql-server command runs a MySQL compatible server which clients can connet to and execute queries against. Any library or tool that can connect to MySQL can connect to Dolt.
% dolt sql-serverStarting server with Config HP="localhost:3306"|U="root"|P=""|T="28800000"|R="false"|L="info"
The host, user, password, timeout, logging info and other options can be set on the command line or via a config file.
dolt sql-server command documentation here.
Most of Dolt's CLI commands, e.g.
dolt pull, are available in the SQL context, either via system tables or as custom SQL functions. All CLI functionality will eventually be exposed in SQL.
Many of Dolt's unique features are accessible via system tables. These tables allow you to query the same information available from various Dolt commands, such as branch information, the commit log, and much more. You can write queries that examine the history of a table, or that select the diff between two commits.
Dolt supports SQL transactions using the standard transaction control statements:
@@autocommit session variable is also supported, and behaves identically as in MySQL.
@@autocommit is enabled by default using the
dolt sql shell and the MySQL shell, but some other clients turn it off by default (notably the Python mysql connector.
Dolt SQL supports a variant of SQL 2011 syntax to query non-HEAD revisions of a database via the
AS OF clause:
SELECT * FROM myTable AS OF 'kfvpgcf8pkd6blnkvv8e0kle8j6lug7a';SELECT * FROM myTable AS OF 'myBranch';SELECT * FROM myTable AS OF 'HEAD^2';SELECT * FROM myTable AS OF TIMESTAMP('2020-01-01');SELECT * FROM myTable AS OF 'myBranch' JOIN myTable AS OF 'yourBranch' AS foo;
AS OF expression must name a valid Dolt reference, such as a commit hash, branch name, or other reference. Timestamp / date values are also supported. Each table in a query can use a different
AS OF clause.
In addition to this
AS OF syntax for
SELECT statements, Dolt also supports an extension to the standard MySQL syntax to examine the database schema for a previous revision:
SHOW TABLES AS OF 'kfvpgcf8pkd6blnkvv8e0kle8j6lug7a';
You can also connect to a non-HEAD revision in your connection string or with a
USE statements. See working with multiple HEADS