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]
[TOPOLOGY n PRIMAR{Y|IES} [m SECONDAR{Y|IES}]]
[OPTIONS "{" option: value[, ...] "}"]
[WAIT [n [SEC[OND[S]]]]|NOWAIT]
CREATE OR REPLACE DATABASE name
[TOPOLOGY n PRIMAR{Y|IES} [m SECONDAR{Y|IES}]]
[OPTIONS "{" option: value[, ...] "}"]
[WAIT [n [SEC[OND[S]]]]|NOWAIT]

Create Composite Database

CREATE COMPOSITE DATABASE name [IF NOT EXISTS]
[WAIT [n [SEC[OND[S]]]]|NOWAIT]
CREATE OR REPLACE COMPOSITE DATABASE name
[WAIT [n [SEC[OND[S]]]]|NOWAIT]

Alter Database

ALTER DATABASE name [IF EXISTS]
{
SET ACCESS {READ ONLY | READ WRITE} |
SET TOPOLOGY n PRIMAR{Y|IES} [m SECONDAR{Y|IES}]
}

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 [COMPOSITE] DATABASE name [IF EXISTS] [{DUMP|DESTROY} [DATA]] [WAIT [n [SEC[OND[S]]]]|NOWAIT]

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.

type

The type of the database, either system, standard, or composite.

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 (primary, secondary, unknown).

writer

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

requestedStatus

The expected status of the database.

currentStatus

The actual status of the database.

statusMessage

A message explaining the current state of the database.

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.

currentPrimariesCount

The number of primary nodes active for this database.

currentSecondariesCount

The number of secondary nodes active for this database.

requestedPrimariesCount

The number of primary nodes that have been configured for this database.

requestedSecondariesCount

The number of primary nodes that have been configured for this database.

creationTime

The date and time at which the database was created.

lastStartTime

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

lastStopTime

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

store

Information about the storage engine and format in use by this database. The value is a string formatted as follows: {storage engine}-{store format}-{major version}.{minor version}.

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.

constituents

The names of any constituents the database may have.

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

Query
SHOW DATABASES
Table 2. Result
name type aliases access address role writer requestedStatus currentStatus statusMessage default home constituents

"library"

"composite"

[]

"read-only"

"localhost:7687"

<null>

false

"online"

"online"

""

false

false

["library.sci-fi","library.romance"]

"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

[]

"sci-fi-books"

"standard"

["library.sci-fi"]

"read-write"

"localhost:7687"

"primary"

true

"online"

"online"

""

false

false

[]

"system"

"system"

[]

"read-write"

"localhost:7687"

"primary"

true

"online"

"online"

""

false

false

[]

Rows: 5

Note that the results of this 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 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 ...

"movies"

["films","motion pictures"]

"read-write"

"279934F99379C98ECFB7FCD2B177418A9643205610F4C2BE79D24C11CAD8B49A"

"9b44f371-fd18-4658-a58c-a9c9b1743e49"

"localhost:7687"

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 type aliases access address role writer requestedStatus currentStatus statusMessage constituents

"neo4j"

"standard"

[]

"read-write"

"localhost:7687"

"primary"

true

"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 type aliases access address role writer requestedStatus currentStatus statusMessage constituents

"neo4j"

"standard"

[]

"read-write"

"localhost:7687"

"primary"

true

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

"system"

"online"

"online"

"neo4j"

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

For composite databases the constituents column is particularly interesting as it lists the aliases that make up the composite database:

Query
SHOW DATABASE library YIELD name, constituents
Table 8. Result
name constituents

"library"

["library.sci-fi","library.romance"]

Rows: 1

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 9. Result
name type access role writer currentStatus ...

"customers"

"standard"

"read-write"

"primary"

true

"online"

"library"

"composite"

"read-only"

<null>

false

"online"

"movies"

"standard"

"read-write"

"primary"

true

"online"

"neo4j"

"standard"

"read-write"

"primary"

true

"online"

"sci-fi-books"

"standard"

"read-write"

"primary"

true

"online"

"system"

"system"

"read-write"

"primary"

true

"online"

Rows: 6

Cluster Topology

In a cluster environment, it may be desirable to control the number of servers used to host a database. The number of primary and secondary servers can be specified using the following command:

Query
CREATE DATABASE `topology-example` TOPOLOGY 1 PRIMARY 0 SECONDARIES

0 rows, System updates: 1

For more details on primary and secondary server roles, see Cluster overview.

TOPOLOGY is only available for standard databases and not composite databases. Composite databases are always available on all servers.

Creating composite databases

Composite databases do not contain data, but they reference to other databases that can be queried together through their constituent aliases. For more information about composite databases, see Operations Manual → Composite database introduction.

Composite databases can be created using CREATE COMPOSITE DATABASE.

Query
CREATE COMPOSITE DATABASE inventory

0 rows, System updates: 1

Composite database names are subject to the same rules as standard databases. One difference is however that the deprecated syntax using dots without enclosing the name in backticks is not available. Both dots and dashes needs to be enclosed within backticks when using composite databases.

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

Query
SHOW DATABASES YIELD name, type, access, role, writer, constituents
Table 10. Result
name type access role writer constituents

"customers"

"standard"

"read-write"

"primary"

true

[]

"inventory"

"composite"

"read-only"

<null>

false

[]

"library"

"composite"

"read-only"

<null>

false

["library.sci-fi","library.romance"]

"movies"

"standard"

"read-write"

"primary"

true

[]

"neo4j"

"standard"

"read-write"

"primary"

true

[]

"sci-fi-books"

"standard"

"read-write"

"primary"

true

[]

"system"

"system"

"read-write"

"primary"

true

[]

"topology-example"

"standard"

"read-write"

"primary"

true

[]

Rows: 8

In order to create database aliases in the composite database, give the composite database as namespace for the alias. For information about creating aliases in composite databases, see here.

Handling Existing Databases

These commands are 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. These behavior flags apply to both standard and composite databases (e.g. a composite database may replace a standard one or another composite.)

Query
CREATE COMPOSITE 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 these commands cannot be used together.

Options

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

The OPTIONS are only available to standard databases and not composite databases.

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.

Altering databases

Standard databases can be modified using the command ALTER DATABASE.

Access

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 Configuration parameters.

A database which 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.

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 11. Result
name access

"customers"

"read-only"

"library"

"read-only"

"movies"

"read-write"

"neo4j"

"read-write"

"sci-fi-books"

"read-write"

"system"

"read-write"

Rows: 6

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

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

Topology

In a cluster environment, it may be desirable to change the number of servers used to host a database. The number of primary and secondary servers can be specified using the following command:

Query
ALTER DATABASE `topology-example` SET TOPOLOGY 3 PRIMARY 0 SECONDARIES
Query
SHOW DATABASES yield name, currentPrimariesCount, currentSecondariesCount, requestedPrimariesCount, requestedSecondariesCount
Table 12. Result
name currentPrimariesCount currentSecondariesCount requestedPrimariesCount requestedSecondariesCount

customers

1

0

1

0

movies

1

0

1

0

neo4j

1

0

1

0

northwind-graph-2020

1

0

1

0

northwind-graph-2021

1

0

1

0

topology-example

3

0

3

0

system

1

0

<null>

<null>

Rows: 7

For more details on primary and secondary server roles, see Cluster overview.

Modifying database topology is only available to standard databases and not composite databases.

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 TOPOLOGY 1 PRIMARY 0 SECONDARY

0 rows

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 13. Result
name type aliases access address role writer requestedStatus currentStatus statusMessage default home constituents

"customers"

"standard"

[]

"read-only"

"localhost:7687"

"unknown"

false

"offline"

"offline"

""

false

false

[]

Rows: 1

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

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 14. Result
name type aliases access address role writer requestedStatus currentStatus statusMessage default home constituents

"customers"

"standard"

[]

"read-only"

"localhost:7687"

"primary"

true

"online"

"online"

""

false

false

[]

Rows: 1

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

Deleting databases

Databases (both standard and composite) 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 15. Result
name type aliases access address role writer requestedStatus currentStatus statusMessage default home constituents

"inventory"

"composite"

[]

"read-only"

"localhost:7687"

<null>

false

"online"

"online"

""

false

false

[]

"library"

"composite"

[]

"read-only"

"localhost:7687"

<null>

false

"online"

"online"

""

false

false

["library.sci-fi","library.romance"]

"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

[]

"sci-fi-books"

"standard"

["library.sci-fi"]

"read-write"

"localhost:7687"

"primary"

true

"online"

"online"

""

false

false

[]

"system"

"system"

[]

"read-write"

"localhost:7687"

"primary"

true

"online"

"online"

""

false

false

[]

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

It is also possible to ensure that only composite databases are dropped. A DROP COMPOSITE request would then fail if the targeted database is a standard database.

Query
DROP COMPOSITE DATABASE inventory

0 rows, System updates: 1

To ensure the database to be dropped is standard and not composite, the user first need to check the type column of SHOW DATABASES manually.

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