Listing databases

There are four different commands for listing databases, depending on whether you want to show:

  • All databases.

  • A particular database.

  • The default database.

  • The home database.

These commands return the following columns:

Table 1. Listing databases output
Column Description Type

name

The name of the database. Default Output

STRING

type

The type of the database: system, standard, or composite. Default Output

STRING

aliases

The names of any aliases the database may have. Default Output

LIST<STRING>

access

The database access mode, either read-write or read-only. Default Output A database may be described as read-only when using ALTER DATABASE …​ SET ACCESS READ ONLY.

STRING

databaseID

The database unique ID.

STRING

serverID

The server instance ID.

STRING

address

Instance address in a clustered DBMS. The default for a standalone database is neo4j://localhost:7687. Default Output

STRING

role

The current role of the database (primary, secondary, unknown). Default Output

STRING

writer

true for the instance that accepts writes for this database (this instance is the leader for this database in a cluster or this is a standalone instance). Default Output

BOOLEAN

requestedStatus

The expected status of the database. The value can be either online or offline. Default Output

STRING

currentStatus

The actual status of the database. Default Output

The possible statuses are:

  • online

  • offline

  • starting

  • stopping

  • store copying

  • initial

  • deallocating

  • dirty

  • quarantined

  • unknown

See Database states for more information.

STRING

statusMessage

A message explaining the status of the database, often explaining why it is not in the correct state. Default Output

STRING

default

Show if this is the default database for the DBMS. Default Output Not returned by SHOW HOME DATABASE or SHOW DEFAULT DATABASE.

BOOLEAN

home

Shown if this is the home database for the current user. Default Output Not returned by SHOW HOME DATABASE or SHOW DEFAULT DATABASE.

BOOLEAN

currentPrimariesCount

Number of primaries for this database reported as running currently. It is the same as the number of rows where role=primary and name=this database.

INTEGER

currentSecondariesCount

Number of secondaries for this database reported as running currently. It is the same as the number of rows where role=secondary and name=this database.

INTEGER

requestedPrimariesCount

The requested number of primaries for this database. May be lower than current if the DBMS is currently reducing the number of copies of the database, or higher if it is currently increasing the number of copies.

INTEGER

requestedSecondariesCount

The requested number of secondaries for this database. May be lower than current if the DBMS is currently reducing the number of copies of the database, or higher if it is currently increasing the number of copies.

INTEGER

creationTime

The date and time at which the database was created.

ZONED DATETIME

lastStartTime

The date and time at which the database was last started.

ZONED DATETIME

lastStopTime

The date and time at which the database was last stopped.

ZONED DATETIME

store

Information about the storage engine and the store format.

The value is a string formatted as:

{storage engine}-{store format}-{major version}.{minor version}

STRING

lastCommittedTxn

The ID of the last transaction received.

INTEGER

replicationLag

Number of transactions the current database is behind compared to the database on the primary instance. The lag is expressed in negative integers. In standalone environments, the value is always 0.

INTEGER

constituents

The names of any constituents the database may have. Applicable only for composite databases. Default Output

LIST<STRING>

options

The map of options applied to the database.

MAP

The results of the SHOW DATABASES command are filtered according to the ACCESS privileges of the user. However, some privileges enable users to see additional databases regardless of their ACCESS privileges:

  • Users with CREATE/DROP/ALTER DATABASE or SET DATABASE ACCESS privileges can see all standard databases.

  • Users with CREATE/DROP COMPOSITE DATABASE or COMPOSITE DATABASE MANAGEMENT privileges can see all composite databases.

  • Users with DATABASE MANAGEMENT privilege can see all databases.

If a user has not been granted ACCESS privilege to any databases nor any of the above special cases, the command can still be executed but it will only return the system database, which is always visible.

Database states

A database’s currentStatus can be one of the following:

State Description

online

The database is running.

offline

The database is not running. If the statusMessage column is filled, the database is not running because of a problem.

starting

The database is not running, but is about to.

stopping

The database is not running anymore, but still has not stopped completely. No offline operations (e.g. load/dump) can be performed yet.

store copying

The database is currently being updated from another instance of Neo4j.

initial

The database has not yet been created.

deallocating

Only applies to databases in a cluster. The database is still online but will eventually be offline due to a transfer of its role in the cluster to a different member. The status is deallocting until the transfer is complete, which can take anything from a second to a day or more.

dirty

This state implies an error has occurred. The database’s underlying store files may be invalid. For more information, consult the statusMessage column or the server’s logs.

quarantined

The database is effectively stopped and its state may not be changed until no longer quarantined. For more information, consult the statusMessage column or the server’s logs.

unknown

This instance of Neo4j does not know the state of this database.

Note that for failed databases, the currentStatus and requestedStatus are different. This often implies an error, but that is not always the case. For example, a database may take a while to transition from offline to online due to a performing recovery. Or, during normal operation, a database’s currentStatus may be transiently different from its requestedStatus due to a necessary automatic process, such as one Neo4j instance copying store files from another.

Show all available databases

A summary of all available databases can be displayed using the command SHOW DATABASES.

Query
SHOW DATABASES
Result
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| name     | type       | aliases                      | access       | address          | role      | writer | requestedStatus | currentStatus | statusMessage | default | home  | constituents |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| "movies" | "standard" | ["films", "motion pictures"] | "read-write" | "localhost:7687" | "primary" | TRUE   | "online"        | "online"      | ""            | FALSE   | FALSE | []           |
| "neo4j"  | "standard" | []                           | "read-write" | "localhost:7687" | "primary" | TRUE   | "online"        | "online"      | ""            | TRUE    | TRUE  | []           |
| "system" | "system"   | []                           | "read-write" | "localhost:7687" | "primary" | TRUE   | "online"        | "online"      | ""            | FALSE   | FALSE | []           |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

As of Neo4j 5.3, databases hosted on servers that are offline are also returned by the SHOW DATABASES command. For such databases, the address column displays NULL, the currentStatus column displays unknown, and the statusMessage displays Server is unavailable.

Show detailed information for a particular database

In this example, the detailed information for a particular database can be displayed using the command SHOW DATABASE name YIELD *. When a YIELD clause is provided, the full set of columns is returned.

Query
SHOW DATABASE movies YIELD *
Result
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| name     | type       | aliases                      | access       | databaseID                                                         | serverID                               | address          | role      | writer | requestedStatus | currentStatus | statusMessage | default | home  | currentPrimariesCount | currentSecondariesCount | requestedPrimariesCount | requestedSecondariesCount | creationTime             | lastStartTime            | lastStopTime | store                | lastCommittedTxn | replicationLag | constituents | options |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| "movies" | "standard" | ["films", "motion pictures"] | "read-write" | "C066801F54B44EA1520F0FE392B4005AABF42D8DD0A5FD09969B955575D287D5" | "e3063985-e2f4-4728-824b-a7d53779667a" | "localhost:7687" | "primary" | TRUE   | "online"        | "online"      | ""            | FALSE   | FALSE | 1                     | 0                       | 1                       | 0                         | 2023-08-14T10:01:29.074Z | 2023-08-14T10:01:29.074Z | NULL         | "record-aligned-1.1" | 3                | 0              | []           | {}      |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Show the number of databases

The number of distinct databases can be seen using YIELD and a count() function in the RETURN clause.

Query
SHOW DATABASES YIELD name
RETURN count(DISTINCT name) AS count
Result
+-------+
| count |
+-------+
| 3     |
+-------+

By specifying the name column and sorting the results by distinct name, only the number of distinct databases are counted, not the number of allocations of databases in a clustered environment.

Show the default database

The default database can be seen using the command SHOW DEFAULT DATABASE.

Query
SHOW DEFAULT DATABASE
Result
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| name    | type       | aliases | access       | address          | role      | writer | requestedStatus | currentStatus | statusMessage | constituents |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| "neo4j" | "standard" | []      | "read-write" | "localhost:7687" | "primary" | TRUE   | "online"        | "online"      | ""            | []           |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+

Show the home database

The home database for the current user can be seen using the command SHOW HOME DATABASE.

Query
SHOW HOME DATABASE
Result
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| name    | type       | aliases | access       | address          | role      | writer | requestedStatus | currentStatus | statusMessage | constituents |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| "neo4j" | "standard" | []      | "read-write" | "localhost:7687" | "primary" | TRUE   | "online"        | "online"      | ""            | []           |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+

Filter the listed databases

It is also possible to filter and sort the results by using YIELD, ORDER BY, and WHERE.

Query
SHOW DATABASES YIELD name, currentStatus, requestedStatus
ORDER BY currentStatus
WHERE name CONTAINS 'o'

In this example:

  • The number of columns returned has been reduced with the YIELD clause.

  • The order of the returned columns has been changed.

  • The results are ordered by the currentStatus column using ORDER BY.

  • The results have been filtered to only show database names containing 'o'.

It is also possible to use SKIP and LIMIT to paginate the results.

Result
+--------------------------------------------+
| name     | currentStatus | requestedStatus |
+--------------------------------------------+
| "movies" | "online"      | "online"        |
| "neo4j"  | "online"      | "online"        |
+--------------------------------------------+