Managing databases

Neo4j supports the management of multiple databases within the same DBMS. The metadata for these databases, including the associated security model, is maintained in a special database called the system database. All multi-database administrative commands must be run against the system database. These administrative commands are automatically routed to the system database when connected to the DBMS over Bolt.

Administrative commands should not be used during a rolling upgrade. For more information, see Upgrade and Migration Guide → Upgrade a cluster.

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"        |
+--------------------------------------------+

Create databases

Databases can be created using CREATE DATABASE.

Database names are subject to the rules specified in Database names section. Having dots (.) in the database names is not recommended. This is due to the difficulty of determining if a dot is part of the database name or a delimiter for a database alias in a composite database.
Query
CREATE DATABASE customers

When a database has been created, it will show up in the listing provided by the command SHOW DATABASES.

Query
SHOW DATABASES YIELD name
Result
+-------------+
| name        |
+-------------+
| "customers" |
| "movies"    |
| "neo4j"     |
| "system"    |
+-------------+

Use IF EXISTS or OR REPLACE when creating databases

The CREATE DATABASE command is optionally idempotent, with the default behavior to fail with an error if the database already exists. There are two ways to circumvent this behavior.

First, appending IF NOT EXISTS to the command ensures that no error is returned and nothing happens should the database already exist.

Query
CREATE DATABASE customers IF NOT EXISTS

Second, adding OR REPLACE to the command results in any existing database being deleted and a new one being created.

Query
CREATE OR REPLACE DATABASE customers

This is equivalent to running DROP DATABASE customers IF EXISTS followed by CREATE DATABASE customers.

The behavior of IF EXISTS and OR REPLACE apply to both standard and composite databases (e.g. a composite database may replace a standard database or another composite database).

The IF NOT EXISTS and OR REPLACE parts of these commands cannot be used together.

Create database options

The CREATE DATABASE command can have a map of options, e.g. OPTIONS {key: 'value'}.

Key Value Description

existingData

use

Controls how the system handles existing data on disk when creating the database. Currently this is only supported with existingDataSeedInstance and must be set to use which indicates the existing data files should be used for the new database.

existingDataSeedInstance

ID of the cluster server

Defines which server is used for seeding the data of the created database. The server ID can be found in the serverId column after running SHOW SERVERS.

seedURI

URI to a backup or a dump from an existing database.

Defines an identical seed from an external source which will be used to seed all servers.

seedConfig

comma separated list of configuration values.

Defines additional configuration specified by comma separated name=value pairs that might be required by certain seed providers.

seedCredentials

credentials

Defines credentials that need to be passed into certain seed providers.

txLogEnrichment

FULL | DIFF | NONE

Defines level of enrichment applied to transaction logs for Change Data Capture (CDC) purposes.

For details about enrichment mode, see Change Data Capture Manual → Getting Started → Enrichment mode.

The existingData, existingDataSeedInstance, seedURI, seedConfig, and seedCredentials options cannot be combined with the OR REPLACE part of this command. More details about seeding options can be found in Seed a cluster.

Alter databases

Standard databases can be modified using the command ALTER DATABASE.

Alter database access mode

By default, a database has read-write access mode on creation. The database can be limited to read-only mode on creation using the configuration parameters dbms.databases.default_to_read_only, dbms.databases.read_only, and dbms.database.writable. For details, see the section on Configuration parameters. A database that was created with read-write access mode can be changed to read-only. To change it to read-only, you can use the ALTER DATABASE command with the sub-clause SET ACCESS READ ONLY. Subsequently, the database access mode can be switched back to read-write using the sub-clause SET ACCESS READ WRITE. Altering the database access mode is allowed at all times, whether a database is online or offline.

If conflicting modes are set by the ALTER DATABASE command and the configuration parameters, i.e. one says read-write and the other read-only, the database will be read-only and prevent write queries.

Modifying access mode is only available to standard databases and not composite databases.

Alter database access mode to read-only

Query
ALTER DATABASE customers SET ACCESS READ ONLY

The database access mode can be seen in the access output column of the command SHOW DATABASES.

Query
SHOW DATABASES yield name, access
Result
+----------------------------+
| name        | access       |
+----------------------------+
| "customers" | "read-only"  |
| "movies"    | "read-write" |
| "neo4j"     | "read-write" |
| "system"    | "read-write" |
+----------------------------+

Alter database access using IF EXISTS

ALTER DATABASE commands are optionally idempotent, with the default behavior to fail with an error if the database does not exist. Appending IF EXISTS to the command ensures that no error is returned and nothing happens should the database not exist.

Query
ALTER DATABASE nonExisting IF EXISTS
SET ACCESS READ WRITE

Alter database topology

In a cluster environment, you can use the ALTER DATABASE command to change the number of servers hosting a database. For more information, see Managing databases in a cluster.

Alter database options

The ALTER DATABASE command can be used to set or remove specific options for a database.

Key Value Description

txLogEnrichment

FULL|DIFF|NONE

Defines the level of enrichment applied to transaction logs for Change Data Capture (CDC) purposes. For details about enrichment mode, see Change Data Capture Manual → Getting Started → Enrichment mode.

There are no available OPTIONS values for composite databases.

Alter the options set for a database

Query
ALTER DATABASE `movies`
SET OPTION txLogEnrichment 'FULL'

The database set options can be seen in the options output column of the command SHOW DATABASES.

Query
SHOW DATABASES yield name, options
Table 2. Result
name options

"customers"

{}

"movies"

{txLogEnrichment: "FULL"}

"neo4j"

{}

"system"

{}

Rows: 4

Remove the options set for a database

Query
ALTER DATABASE `movies`
REMOVE OPTION txLogEnrichment

The REMOVE OPTION clause removes the specified option from the database using the ALTER DATABASE command.

Query
SHOW DATABASES YIELD name, options
Table 3. Result
name options

"customers"

{}

"movies"

{}

"neo4j"

{}

"system"

{}

Rows: 4

Stop databases

Databases can be stopped using the command STOP DATABASE.

Query
STOP DATABASE customers

Both standard databases and composite databases can be stopped using this command.

The status of the stopped database can be seen using the command SHOW DATABASE name.

Query
SHOW DATABASE customers YIELD name, requestedStatus, currentStatus
Result
+-----------------------------------------------+
| name        | requestedStatus | currentStatus |
+-----------------------------------------------+
| "customers" | "offline"       | "offline"     |
+-----------------------------------------------+

Databases that are stopped with the STOP command are completely shut down and may be started again through the START command. In a cluster, as long as a database is in a shutdown state, it can not be considered available to other members of the cluster. It is not possible to do online backups against shutdown databases and they need to be taken into special consideration during disaster recovery, as they do not have a running Raft machine while shutdown. Unlike stopped databases, dropped databases are completely removed and are not intended to be used again at all.

Start databases

Databases can be started using the command START DATABASE.

Query
START DATABASE customers

Both standard databases and composite databases can be started using this command.

The status of the started database can be seen using the command SHOW DATABASE name.

Query
SHOW DATABASE customers YIELD name, requestedStatus, currentStatus
Result
+-----------------------------------------------+
| name        | requestedStatus | currentStatus |
+-----------------------------------------------+
| "customers" | "online"        | "online"      |
+-----------------------------------------------+

Delete databases

Databases can be deleted by using the command DROP DATABASE. Note that all database aliases must be dropped before dropping a database.

Query
DROP DATABASE customers

Both standard databases and composite databases can be deleted using this command.

The DROP DATABASE command removes a database entirely. Therefore, it no longer shows up in the listing provided by the command SHOW DATABASES.

Query
SHOW DATABASES YIELD name
Result
+---------------------+
| name                |
+---------------------+
| "movies"            |
| "neo4j"             |
| "system"            |
+---------------------+

Use IF EXISTS when deleting databases

The DROP DATABASE command is optionally idempotent, with the default behavior to fail with an error if the database does not exist. Appending IF EXISTS to the command ensures that no error is returned and nothing happens should the database not exist. It will always return an error if there is an existing alias that targets the database. In that case, the alias needs to be dropped before dropping the database.

Query
DROP DATABASE customers IF EXISTS

Use DUMP DATA or DESTROY DATA when deleting databases

You can request that a dump of the store files is produced first, and stored in the path configured using the dbms.directories.dumps.root setting (by default <neo4j-home>/data/dumps). This can be achieved by appending DUMP DATA to the command (or DESTROY DATA to explicitly request the default behavior). These dumps are equivalent to those produced by neo4j-admin dump and can be similarly restored using neo4j-admin load.

Query
DROP DATABASE movies DUMP DATA

The options IF EXISTS and DUMP DATA/ DESTROY DATA can also be combined. An example could look like this:

Query
DROP DATABASE customers IF EXISTS DUMP DATA

Wait options

The WAIT sub-clause was added as an option to the ALTER DATABASE command in Neo4j 5.7.

Aside from SHOW DATABASES, all database management commands accept an optional WAIT/NOWAIT sub-clause. The WAIT/NOWAIT sub-clause allows you to specify a time limit in which the command must complete and return.

The options are:

  • WAIT n SECONDS - Returns once completed or when the specified time limit of n seconds is up.

  • WAIT - Returns once completed or when the default time limit of 300 seconds is up.

  • NOWAIT - Returns immediately.

A command using a WAIT sub-clause automatically commits the current transaction when it executes successfully, as the command needs to run immediately for it to be possible to WAIT for it to complete. Any subsequent commands executed are therefore performed in a new transaction. This is different from the usual transactional behavior, and for this reason, it is recommended that these commands be run in their own transaction. The default behavior is NOWAIT, so if no clause is specified the transaction behaves normally and the action is performed in the background post-commit.

A command with a WAIT clause may be interrupted whilst it is waiting to complete. In this event, the command will continue to execute in the background and will not be aborted.
Example 1. Create a database with WAIT
Query
CREATE DATABASE slow WAIT 5 SECONDS
Result
+-------------------------------------------------------+
| address          | state      | message     | success |
+-------------------------------------------------------+
| "localhost:7687" | "CaughtUp" | "caught up" | TRUE    |
+-------------------------------------------------------+

The success column provides an aggregate status of whether or not the command is considered successful and thus every row will have the same value. This column is to determine, for example in a script, whether or not the command has been completed successfully without timing out.