# PostgreSQL

When setting the `dbms` parameter in the `biocypher_config.yaml` to `postgres`, the BioCypher Knowledge Graph is written to a PostgreSQL database.
[PostgreSQL](https://www.postgresql.org/) is a relational database management system.

## Install PostgreSQL

To get a PostgreSQL instance running quickly, you can use Docker. The following command will start a PostgreSQL instance with the password `postgres` and the port `5432` exposed to the host system.

```
docker run --restart always --publish=5432:5432 --env POSTGRES_PASSWORD=postgres -d postgres
```

The ``postgresql-client`` (also known as ``psql`` command line tool) can be used to connect and interact with the running PostgreSQL database instance. Installation instructions can be found [here](https://www.postgresql.org/download/).

## PostgreSQL settings

To overwrite the standard settings of PostgreSQL, add a `postgresql` section to the `biocypher_config.yaml` file.
The following settings are possible:

```{code-block} yaml
:caption: biocypher_config.yaml

postgresql:  ### PostgreSQL configuration ###

  # PostgreSQL connection credentials
  database_name: postgres
  user: postgres
  password: postgres
  port: 5432

  # PostgreSQL import batch writer settings
  quote_character: '"'
  delimiter: '\t'

  # Import call prefixes to adjust the autogenerated shell script
  import_call_bin_prefix: ''  # path to "psql"
  import_call_file_prefix: /path/to/files/
```

## Offline mode

### Running BioCypher

After running BioCypher with the ``offline`` parameter set to ``true`` and the ``dbms`` set to ``postgres``,
the output folder contains:

- ``entity-create_table.sql``: The SQL scripts to create the tables for the nodes/edges. Entity is replaced by your nodes and edges and for each node and edge type an own SQL script is generated.
- ``entity-part000.csv``: The CSV file containing the data for the entity.
- ``postgres-import-call.sh``: The import script to create a database with the SQL scripts and insert the data from the CSV files.

```{note}
If the ``postgres-import-call.sh`` is missing, you can create it by running ``bc.write_import_call()``.
```

### Create the PostgreSQL database

A running PostgreSQL instance (e.g. in a Docker container created with the command from above) is required to create the database and import the data.
By running the import script ``postgres-import-call.sh``, the content should be written to the PostgreSQL database.

### Access the PostgreSQL database

To check the content of the database, you can use the ``psql`` command line tool.

First connect to the running PostgreSQL database instance:
```bash
psql -h localhost -p 5432 -U postgres
```

Then you can execute SQL queries.
For example, you can list all tables in the database by running the following command in the terminal:
```sql
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public';
```