Guide
API
Guide
API
Database - Creation
easy

The database ln-history-database is a Postgres database.

This is the first part where we will setup the database with authentication and all its tables.

Schema

The gossip messages will be stored in the four tables nodes, nodes_raw_gossip, channels, channel_updates. The diagram below shows how their relationship.

Setup

The ln-history-database is running in a docker container via docker compose. We persist the actual PostgreSQL data by mounting a local directory called postgres-data into the container.

Create directory

Create the new folder called database.

ln-history@host:~/ln-history₿ mkdir database && cd database

Create data directory

This is where PostgreSQL will store its data. You do not need to add anything manually — it will be populated by the container.

ln-history@host:~/ln-history/database₿ mkdir postgres-data

Environment variablesauthentication

Create an .env file and fill in your own randomly created credentials.

POSTGRES_USER=admin
POSTGRES_PASSWORD=YOUR_PASSWORD

🔐 Important: Never commit .env files containing credentials to version control.

🐳 Docker container

Create the docker-compose.yml and paste the following content into it.

services:
  db:
    image: postgres:17.5
    restart: always
    container_name: ln-history-database
    environment:
      POSTGRES_USER: $POSTGRES_USER
      POSTGRES_PASSWORD: $POSTGRES_PASSWORD
    env_file: .env
    ports:
      - '5432:5432'
    volumes:
      - ./postgres-data:/var/lib/postgresql/data

  adminer:
    image: adminer:latest
    restart: always
    container_name: adminer
    ports:
      - '8080:8080'

Folder structure

Ultimatly the folder structure should look like this:

database/
├── .env                    # Environment variables for the service
├── docker-compose.yml      # Docker setup for this service
└── postgres-data           # Directory where the data is stored

Run

We start the container by using docker compose up -d (The flag -d abbreviates deamon, meaning background process).

ln-history@host:~/ln-history/database₿ docker compose up -d

Create tables

On your server go into the docker container

ln-history@host:~/ln-history/database₿ docker exec -it ln-history-database psql -U admin

Inside the docker container execute the following commands.

admin=# -- Create the ln-history-database CREATE DATABASE "ln-history-database" OWNER admin;
admin=# -- Connect to database \c "ln-history-database"
admin=# -- Create nodes table CREATE TABLE nodes ( node_id VARCHAR(66) PRIMARY KEY, from_timestamp TIMESTAMPTZ, last_seen TIMESTAMPTZ);"
admin=# -- Create nodes_raw_gossip table CREATE TABLE nodes_raw_gossip ( gossip_id VARCHAR(64) PRIMARY KEY, node_id VARCHAR(66) REFERENCES nodes(node_id), timestamp TIMESTAMPTZ, raw_gossip BYTEA);

admin=# -- Create channels table CREATE TABLE channels ( gossip_id VARCHAR(64) UNIQUE, scid VARCHAR(23) PRIMARY KEY, source_node_id VARCHAR(66) REFERENCES nodes(node_id), target_node_id VARCHAR(66) REFERENCES nodes(node_id), from_timestamp TIMESTAMPTZ, to_timestamp TIMESTAMPTZ, amount_sat INT, raw_gossip BYTEA);
admin=# -- Create channel_updates table CREATE TABLE channel_updates ( gossip_id VARCHAR(64) PRIMARY KEY, scid VARCHAR(23) REFERENCES channels(scid), direction BIT, from_timestamp TIMESTAMPTZ, to_timestamp TIMESTAMPTZ, raw_gossip BYTEA);

We exit the docker container by pressing CTRL + D.

Verify connection without installing PostgreSQL

We verify if the database has been sucessfully created without having to installed psql on the machine by just running this Docker command:

ln-history@host:~₿ docker run --rm -it --network host postgres:15 psql 'postgresql://admin:YOUR_PASSWORD@localhost:5432'

🔁 Replace YOUR_PASSWORD with your actual password for the ln-history-database

ON THIS PAGE