Database management

This chapter explains how to use Cypher to manage Neo4j databases: creating, modifying, deleting, starting, and stopping individual databases within a single server.

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.

The syntax of the database management commands is as follows:

Table 1. Database management command syntax
Command Syntax

Show Database

SHOW { DATABASE name | DATABASES | DEFAULT DATABASE | HOME DATABASE }
[WHERE expression]
SHOW { DATABASE name | DATABASES | DEFAULT DATABASE | HOME DATABASE }
YIELD { * | field[, ...] } [ORDER BY field[, ...]] [SKIP n] [LIMIT n]
[WHERE expression]
[RETURN field[, ...] [ORDER BY field[, ...]] [SKIP n] [LIMIT n]]

Create Database

CREATE DATABASE name [IF NOT EXISTS]
[OPTIONS "{" option: value[, ...] "}"]
[WAIT [n [SEC[OND[S]]]]|NOWAIT]
CREATE OR REPLACE DATABASE name [WAIT [n [SEC[OND[S]]]]|NOWAIT]

Alter Database

ALTER DATABASE name [IF EXISTS] SET ACCESS {READ ONLY | READ WRITE}

Stop Database

STOP DATABASE name [WAIT [n [SEC[OND[S]]]]|NOWAIT]

Start Database

START DATABASE name [WAIT [n [SEC[OND[S]]]]|NOWAIT]

Drop Database

DROP DATABASE name [IF EXISTS] [{DUMP|DESTROY} [DATA]] [WAIT [n [SEC[OND[S]]]]|NOWAIT]

Create Alias

CREATE [OR REPLACE] ALIAS name [IF NOT EXISTS] FOR DATABASE targetName

Alter Alias

ALTER ALIAS name [IF EXISTS] SET DATABASE TARGET targetName

Drop Alias

DROP ALIAS name [IF EXISTS] FOR DATABASE

1. Listing databases

There are four different commands for listing databases: listing all databases, listing a particular database, listing the default database, and listing the home database. These commands return the following columns:

Column Description Default Output Full Output Notes
name

The name of the database.

aliases

The names of any aliases the database may have.

access

The database access mode, either read-write or read-only.

databaseID

The database unique ID.

serverID

The server instance ID.

address

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

role

The current role of the database (standalone, leader, follower, read_replica, unknown).

requestedStatus

The expected status of the database.

currentStatus

The actual status of the database.

error

An error message explaining why the database is not in the correct state.

default

Show if this is the default database for the DBMS.

Not returned by SHOW HOME DATABASE or SHOW DEFAULT DATABASE.

home

Shown if this is the home database for the current user.

Not returned by SHOW HOME DATABASE or SHOW DEFAULT DATABASE.

lastCommittedTxn

The ID of the last transaction received.

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.

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

Query
SHOW DATABASES
Table 2. Result
name aliases access address role requestedStatus currentStatus error default home

"movies"

["films","motion pictures"]

"read-write"

"localhost:7687"

"standalone"

"online"

"online"

""

false

false

"neo4j"

[]

"read-write"

"localhost:7687"

"standalone"

"online"

"online"

""

true

true

"northwind-graph-2020"

[]

"read-write"

"localhost:7687"

"standalone"

"online"

"online"

""

false

false

"northwind-graph-2021"

[]

"read-write"

"localhost:7687"

"standalone"

"online"

"online"

""

false

false

"system"

[]

"read-write"

"localhost:7687"

"standalone"

"online"

"online"

""

false

false

Rows: 5

Note that the results of this command are filtered according to the ACCESS privileges of the user. However, users with CREATE/DROP/ALTER DATABASE, SET DATABASE ACCESS, or DATABASE MANAGEMENT privileges can see all databases regardless of their ACCESS privileges. If a user has not been granted ACCESS privilege to any databases, the command can still be executed but will only return the system database, which is always visible.

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 *
Table 3. Result
name aliases access databaseID serverID address role requestedStatus currentStatus error default home lastCommittedTxn replicationLag

"movies"

["films","motion pictures"]

"read-write"

"6BB45801AF42D681FCA31374D7F03A3A1BCF228CB032B94828F4F27E377738BA"

"c6f853ca-cf32-42ba-9f3c-18bfbd9ceadb"

"localhost:7687"

"standalone"

"online"

"online"

""

false

false

3

0

Rows: 1

The number of databases can be seen using a count() aggregation with YIELD and RETURN.

Query
SHOW DATABASES YIELD * RETURN count(*) as count
Table 4. Result
count

5

Rows: 1

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

Query
SHOW DEFAULT DATABASE
Table 5. Result
name aliases access address role requestedStatus currentStatus error

"neo4j"

[]

"read-write"

"localhost:7687"

"standalone"

"online"

"online"

""

Rows: 1

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

Query
SHOW HOME DATABASE
Table 6. Result
name aliases access address role requestedStatus currentStatus error

"neo4j"

[]

"read-write"

"localhost:7687"

"standalone"

"online"

"online"

""

Rows: 1

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 'e'

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 have been filtered to only show database names containing 'e'.

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

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

Table 7. Result
name currentStatus requestedStatus

"movies"

"online"

"online"

"neo4j"

"online"

"online"

"system"

"online"

"online"

Rows: 3

Note that for failed databases, the currentStatus and requestedStatus are different. This often implies an error, but does not always. For example, a database may take a while to transition from offline to online due to 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. The possible statuses are initial, online, offline, store copying and unknown.

2. Creating databases

Databases can be created using CREATE DATABASE.

Query
CREATE DATABASE customers

0 rows, System updates: 1

Database names are subject to the standard Cypher restrictions on valid identifiers. The following naming rules apply:

  • Database name length must be between 3 and 63 characters.

  • The first character must be an ASCII alphabetic character.

  • Subsequent characters can be ASCII alphabetic (mydatabase), numeric characters (mydatabase2), dots (main.db), and dashes (enclosed within backticks, e.g., CREATE DATABASE `main-db`).

  • Names cannot end with dots or dashes.

  • Names that begin with an underscore or with the prefix system are reserved for internal use.

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

Query
SHOW DATABASES
Table 8. Result
name aliases access address role requestedStatus currentStatus error default home

"customers"

[]

"read-write"

"localhost:7687"

"standalone"

"online"

"online"

""

false

false

"movies"

["films","motion pictures"]

"read-write"

"localhost:7687"

"standalone"

"online"

"online"

""

false

false

"neo4j"

[]

"read-write"

"localhost:7687"

"standalone"

"online"

"online"

""

true

true

"northwind-graph-2020"

[]

"read-write"

"localhost:7687"

"standalone"

"online"

"online"

""

false

false

"northwind-graph-2021"

[]

"read-write"

"localhost:7687"

"standalone"

"online"

"online"

""

false

false

"system"

[]

"read-write"

"localhost:7687"

"standalone"

"online"

"online"

""

false

false

Rows: 6

2.1. Handling Existing Databases

This command is optionally idempotent, with the default behavior to fail with an error if the database already exists. Appending IF NOT EXISTS to the command ensures that no error is returned and nothing happens should the database already exist. Adding OR REPLACE to the command will result in any existing database being deleted and a new one created.

Query
CREATE DATABASE customers IF NOT EXISTS
Query
CREATE OR REPLACE DATABASE customers

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

The IF NOT EXISTS and OR REPLACE parts of this command cannot be used together.

2.2. 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

instance ID of the cluster node

Defines which instance is used for seeding the data of the created database. The instance id can be taken from the id column of the dbms.cluster.overview() procedure. Can only be used in clusters.

The existingData and existingDataSeedInstance options cannot be combined with the OR REPLACE part of this command.

3. Altering databases

Databases can be modified using the command ALTER DATABASE. For example, a database always has read-write access mode on creation, unless the configuration parameter dbms.databases.default_to_read_only is set to true.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.

Database access modes can also be managed using the configuration parameters dbms.databases.default_to_read_only, dbms.databases.read_only, and dbms.database.writable. For details, see Configuration parameters. If the mode set by the ALTER DATABASE command is read-only while the database according to those settings is read-write (or vice versa), the database will be read-only and prevent write queries.

Query
ALTER DATABASE customers SET ACCESS READ ONLY

0 rows, System updates: 1

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

Query
SHOW DATABASES yield name, access
Table 9. Result
name access

"customers"

"read-only"

"movies"

"read-write"

"neo4j"

"read-write"

"northwind-graph-2020"

"read-write"

"northwind-graph-2021"

"read-write"

"system"

"read-write"

Rows: 6

This 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.

Query
ALTER DATABASE nonExisting IF EXISTS SET ACCESS READ WRITE

4. Stopping databases

Databases can be stopped using the command STOP DATABASE.

Query
STOP DATABASE customers

0 rows, System updates: 1

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

Query
SHOW DATABASE customers
Table 10. Result
name aliases access address role requestedStatus currentStatus error default home

"customers"

[]

"read-only"

"localhost:7687"

"standalone"

"offline"

"offline"

""

false

false

Rows: 1

5. Starting databases

Databases can be started using the command START DATABASE.

Query
START DATABASE customers

0 rows, System updates: 1

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

Query
SHOW DATABASE customers
Table 11. Result
name aliases access address role requestedStatus currentStatus error default home

"customers"

[]

"read-only"

"localhost:7687"

"standalone"

"online"

"online"

""

false

false

Rows: 1

6. Deleting databases

Databases can be deleted using the command DROP DATABASE.

Query
DROP DATABASE customers

0 rows, System updates: 1

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

Query
SHOW DATABASES
Table 12. Result
name aliases access address role requestedStatus currentStatus error default home

"movies"

["films","motion pictures"]

"read-write"

"localhost:7687"

"standalone"

"online"

"online"

""

false

false

"neo4j"

[]

"read-write"

"localhost:7687"

"standalone"

"online"

"online"

""

true

true

"northwind-graph-2020"

[]

"read-write"

"localhost:7687"

"standalone"

"online"

"online"

""

false

false

"northwind-graph-2021"

[]

"read-write"

"localhost:7687"

"standalone"

"online"

"online"

""

false

false

"system"

[]

"read-write"

"localhost:7687"

"standalone"

"online"

"online"

""

false

false

Rows: 5

This 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

The DROP DATABASE command will remove a database entirely. However, 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 customers 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

7. Wait options

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

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

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

  • NOWAIT - Return immediately.

A command using a WAIT clause will automatically commit 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 will therefore be performed in a new transaction. This is different to 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 will behave normally and the action is performed in the background post-commit.

Query
CREATE DATABASE slow WAIT 5 SECONDS
Table 13. Result
address state message success

"localhost:7687"

"CaughtUp"

"caught up"

true

Rows: 1

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. The intention of this column is to make it easy to determine, for example in a script, whether or not the command completed successfully without timing out.

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.

8. Creating database aliases

An alias can be used as an alternative database name, which can be used in all places where a database name can be used. A home database can be set to an alias, which will be resolved to the target database on use. In all all other Cypher commands and queries, the alias will be resolved while executing the command. The privileges are determined on the resolved database.

Aliases can be created using CREATE ALIAS.

Query
CREATE ALIAS `northwind` FOR DATABASE `northwind-graph-2020`

0 rows, System updates: 1

Alias names are subject to the standard Cypher restrictions on valid identifiers. The following naming rules apply:

  • A name is a valid identifier, additionally allowing dots e.g. main.alias.

  • Name length can be up to 65534 characters.

  • Names cannot end with dots.

  • Names that begin with an underscore or with the prefix system are reserved for internal use.

  • Non-alphabetic characters, including numbers, symbols and whitespace characters, can be used in names, but must be escaped using backticks.

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

Query
SHOW DATABASE `northwind`
Table 14. Result
name aliases access address role requestedStatus currentStatus error default home

"northwind-graph-2020"

["northwind"]

"read-write"

"localhost:7687"

"standalone"

"online"

"online"

""

false

false

Rows: 1

This command is optionally idempotent, with the default behavior to fail with an error if the database alias already exists. Inserting IF NOT EXISTS after the alias name ensures that no error is returned and nothing happens should a database alias with that name already exist. Adding OR REPLACE to the command will result in any existing database alias being deleted and a new one created. CREATE OR REPLACE ALIAS will fail if there is an existing database with the same name.

Query
CREATE ALIAS `northwind` IF NOT EXISTS FOR DATABASE `northwind-graph-2020`

0 rows

Query
CREATE OR REPLACE ALIAS `northwind` FOR DATABASE `northwind-graph-2020`

0 rows, System updates: 2

This is equivalent to running DROP ALIAS northwind IF EXISTS FOR DATABASE followed by CREATE ALIAS northwind FOR DATABASE northwind-graph-2020 .

The IF NOT EXISTS and OR REPLACE parts of this command cannot be used together.

9. Altering database aliases

Aliases can be altered using ALTER ALIAS to change its database target.

Query
ALTER ALIAS `northwind` SET DATABASE TARGET `northwind-graph-2021`

0 rows, System updates: 1

When a database alias has been altered, it will show up in the aliases column for the target database provided by the command SHOW DATABASES.

Query
SHOW DATABASE `northwind`
Table 15. Result
name aliases access address role requestedStatus currentStatus error default home

"northwind-graph-2021"

["northwind"]

"read-write"

"localhost:7687"

"standalone"

"online"

"online"

""

false

false

Rows: 1

10. Deleting database aliases

Aliases can be dropped using DROP ALIAS.

Query
DROP ALIAS `northwind` FOR DATABASE

0 rows, System updates: 1

When a database alias has been deleted, it will no longer show up in the aliases column provided by the command SHOW DATABASES.

Query
SHOW DATABASE `northwind-graph-2021`
Table 16. Result
name aliases access address role requestedStatus currentStatus error default home

"northwind-graph-2021"

[]

"read-write"

"localhost:7687"

"standalone"

"online"

"online"

""

false

false

Rows: 1

This command is optionally idempotent, with the default behavior to fail with an error if the alias does not exist. Inserting IF EXISTS after the alias name ensures that no error is returned and nothing happens should the alias not exist.

Query
DROP ALIAS `northwind` IF EXISTS FOR DATABASE

0 rows