# Management SQLite store

In NetBird, the Management system traditionally relies on JSON file storage.
Starting from version [0.24.0](https://github.com/netbirdio/netbird/releases/tag/v0.24.0), we have introduced **experimental** support for SQLite.
This addition aims to provide users with enhanced performance and scalability options.

## SQLite entity relationship diagram

For a high-level overview of the SQLite schema, refer to the Entity Relationship Diagram (ERD) below:

<p>
    <img src="/docs-static/img/selfhosted/sqlite-erd.png" alt="high-level-dia" className="imagewrapper"/>
</p>

## Using SQLite for fresh installations

As of version 0.24.0, the default configuration for fresh installations is JSON file storage. 
However, users have the option to leverage the benefits of SQLite for new instances. 
To enable SQLite, add to your setup.env the following variable:
```bash
NETBIRD_STORE_CONFIG_ENGINE=sqlite
```
This will result in a configuration similar to the following in your management.json file:
```json
    "StoreConfig": {
        "Engine": "sqlite"
    }
```
You can switch back to JSON file storage by setting the `NETBIRD_STORE_CONFIG_ENGINE` variable to `jsonfile`.
<Note>
    Switching between storage options requires migration steps to prevent data loss.
</Note>

## Migrating from JSON store to SQLite store
This migration process allows users to seamlessly transition between storage options while maintaining data integrity.
Ensure that you are running the commands described below after upgrading to version 0.24.0 or later.

### Migration commands

Starting from version 0.24.0, NetBird Management provides a subcommand to facilitate migration to SQLite. This command is part of the binary shipped within the official docker image.
<Note>
    The following commands assume you use the latest docker version with the compose plugin. If you have docker-compose installed as a standalone, please use docker-compose as a command.
</Note>
```bash
$ docker compose exec management /go/bin/netbird-mgmt sqlite-migration
```
This will produce an output similar to:
```bash
Contains sub-commands to perform JSON file store to SQLite store migration and rollback

Usage:
  netbird-mgmt sqlite-migration [command]

Available Commands:
  downgrade   Rollback SQLite store to JSON file store. Please make a backup of the SQLite file before running this command.
  upgrade     Migrate JSON file store to SQLite store. Please make a backup of the JSON file before running this command.

Flags:
      --datadir string   server data directory location (default "/var/lib/netbird/")
  -h, --help             help for sqlite-migration

Global Flags:
      --log-file string    sets Netbird log path. If console is specified the the log will be output to stdout (default "/var/log/netbird/management.log")
      --log-level string   (default "info")

Use "netbird-mgmt sqlite-migration [command] --help" for more information about a command.
```

### Follow these steps for migration:
To migrate from JSON file store to SQLite, follow these steps:
<Note>
    The following commands assume you use the latest docker version with the compose plugin. If you have docker-compose installed as a standalone, please use docker-compose as a command.
</Note>
1. After accessing the path that contains your docker-compose.yml and management.json files, stop the NetBird Management service.
```bash
  docker compose stop management
```
2. Backup your data store (`store.json` in `datadir` - default "/var/lib/netbird/").
```bash
  mkdir backup
  docker compose cp -a management:/var/lib/netbird/ backup/
```
3. Start NetBird Management service:
```bash
  docker compose start management
```
4. Run the migration to SQLite subcommand:
```bash
  docker compose exec management /go/bin/netbird-mgmt sqlite-migration upgrade --log-file console
```
5. Enable SQLite by updating the management.json file and setting the `Engine` field to `sqlite` as the following example:
```json
    "StoreConfig": {
        "Engine": "sqlite"
    }
```
6. Restart the Management service.
```bash
  docker compose restart management
```
7. Check logs to confirm the store switch:
```bash
  docker compose logs management
```
You should see an entry similar to:
```
2023-10-19T18:55:29Z INFO management/server/store.go:75: using SQLite store engine
```

### Rollback to JSON file store
To rollback to the JSON file store, follow these steps:
<Note>
    The following commands assume you use the latest docker version with the compose plugin. If you have docker-compose installed as a standalone, please use docker-compose as a command.
</Note>
1. After accessing the path that contains your docker-compose.yml and management.json files, stop the NetBird Management service.
```bash
  docker compose stop management
```
2. Backup your data store (`store.db` in `datadir` - default "/var/lib/netbird/").
```bash
  mkdir backup
  docker compose cp -a management:/var/lib/netbird/ backup/
```
3. Start NetBird Management service:
```bash
  docker compose start management
```
4. Run the migration to SQLite subcommand:
```bash
  docker compose exec management /go/bin/netbird-mgmt sqlite-migration downgrade --log-file console
```
5. Enable JSON file by updating the management.json file and setting the `Engine` field to `jsonfile` as the following example:
```json
    "StoreConfig": {
        "Engine": "jsonfile
    }
```
6. Restart the Management service.
```bash
  docker compose restart management
```
7. Check logs to confirm the store switch:
```bash
  docker compose logs management
```
You should see an entry similar to:
```
2023-10-19T18:55:29Z INFO management/server/store.go:72: using JSON file store engine
```