DHIVA LABS · LEARN

Appearance

Run queries ​

The Query tab runs what you type against the open database: SQL for SQLite, MySQL / MariaDB and PostgreSQL, a JSON command for MongoDB, and a key prefix for LMDB.

Run a SQL query ​

  1. Open a connection and select the Query tab.

  2. Type a statement in the box, for example:

    sql
    SELECT * FROM my_table LIMIT 100;

  3. Select Run (Ctrl+Enter), or press Ctrl + Enter while the cursor is in the box.

What you see next depends on the statement:

  • A statement that returns rows, such as SELECT, fills the grid below. To the right of the button the app shows the number of rows and the time taken, for example 42 rows · 8 ms.
  • A statement that changes data, such as INSERT, UPDATE or DELETE, shows a line such as 3 row(s) affected.
  • A statement that fails shows the database's error message in red.

DANGER

The Query tab runs any statement at once, without a confirmation. DELETE FROM orders; empties the table. Read Edit data safely before running statements that change data.

TIP

Add a LIMIT to SELECT statements on large tables. The Query tab shows every row the database returns, in one grid, with no paging.

Start from a search you built ​

If you are not sure how to write a query, build it with the find-rows panel on the Data tab, select Show the query this runs, then the arrow icon (Open in Query tab to customize). The query appears in the box for you to change. See Browse and find rows.

Good to know for SQL databases ​

  • The grid in the Query tab is read-only. To edit rows by clicking, use the Data tab.
  • In SQLite, statements that start with SELECT, WITH, PRAGMA or EXPLAIN return a grid. Other statements run and report the affected rows.
  • The table list on the left does not refresh by itself. After a CREATE TABLE or DROP TABLE, select the refresh button in the title bar.
  • Selecting a table in the list switches to the Data tab. Your query text stays in the box.

MongoDB: run a command ​

For MongoDB, the box takes one database command written as a JSON object.

Find documents:

json
{"find": "users", "filter": {"age": {"$gt": 21}}, "limit": 50}

Count documents:

json
{"count": "users"}

Run an aggregation:

json
{"aggregate": "users", "pipeline": [{"$group": {"_id": "$city", "total": {"$sum": 1}}}], "cursor": {}}
  • Commands that return documents, such as find and aggregate, fill the grid.
  • Other commands show the server's reply as formatted JSON.
  • If the text is not a JSON object, the app shows Command must be a JSON object.
  • For aggregate, the app adds the "cursor": {} part if you leave it out.

Write the command as strict JSON: double quotes around every key and text value. The shell style, such as db.users.find(), is not accepted.

Good to know

The grid shows the first batch of documents the server returns for a command. Set "limit" in a find command to control how many you get.

LMDB: scan keys by prefix ​

LMDB has no query language. The box takes the beginning of a key and lists every entry whose key starts with it.

You typeYou get
NothingEvery entry in the main database
user:Entries in the main database whose key starts with user:
sessions: abcEntries in the sub-database named sessions whose key starts with abc

A scan returns at most 1000 entries. To match binary keys, type the prefix as 0x followed by hexadecimal digits.

The text before the first colon is treated as a sub-database name only when a sub-database with that name exists. Otherwise the whole text is the prefix.

Save a result ​

When a query has returned rows, an Export CSV button appears next to Run. See Export and back up.

Next steps ​