How to Run VNDB Locally

Intro

Yorhel, the founder and soul of VNDB, has died last week🕯. This post serves as a legacy setup just in case that vndb.org goes offline someday.

⚠️ Warning

User: If you are worried about vndb.org going offline someday, NO WORRY, there is backup, despite missing certain data. You can, however, export your list and length votes via My Visual Novel List -> Export -> Download XML export. If you are paranoid, download source and dump as well.
Developer: If you are considering developing VNDB to make it further, please WAIT for a few weeks before things settle down. It does no harm if you really plan to do it for years.
Site Administrator: If you are hesitated about whether you should run your own VNDB instance publicly, please DON’T. It’s too early to fragment users and only cause confusion.
Archiver: If you are mirroring VNDB images, please HOST IT, otherwise it’s just pointless and heavily consumes server resources, thus making VNDB live shorter… unless you plan to provide public hosting, which I highly doubt as some images are very explicit and offensive, making hosting illegal in many countries.

Prerequisite

Before we start, let’s define VNDB.

Most users are familiar with the web interface, but VNDB is not limited to that:

Web UI, discussion board, changes history, private list
VNDB.org API v2 (Kana), fortunately it’s bundled with vndb source code so we don’t need to set it up seperately
Database Dump
VNDB Query, aka. SQLBin

To have a working instance of VNDB locally, we need to make sure everything is ready. Otherwise you’ll only find your Dockerfile useless when it really happens.

Source¹
- Available as forgejo instance or cgit
- List (per d8 and SQLBin readme): fu, mithril-vndb, sqlbin, vndb, yhdev
- If you want to setup legacy versions of VNDB, tuwf is also needed
Database Dump: per d14#5, download near-complete dump, aka. vndb-db-latest.tar.zst
Documentation
- As docs on VNDB are stored seperately, we also need to backup them seperately, which can be easily done via extensions like SingleFile and is not covered in this post
- List: d2-d20 with d11 excluded (d11 is a redirection to VNDB API v2)
Knowledge
- Minimum: Linux package manager, Git, PostgreSQL
- Bonus: command line, init service, Vim, Perl, GNU Make
PC/VM Spec: 1 CPU Core, 1G RAM, 10G Disk²

Download

Download source and dump:

# download source and dump
mkdir -p ~/projects && cd ~/projects
git clone https://code.blicky.net/yorhel/{fu,mithril-vndb,sqlbin,vndb,yhdev}
# if you plan to setup legacy vndb
# git clone https://code.blicky.net/yorhel/tuwf

# near-complete database: https://vndb.org/d14#5
# development database (NOT working due to schema change): https://vndb.org/d8#3
wget https://dl.vndb.org/dump/vndb-db-latest.tar.zst

# additional querying stuff for vndb query
# https://query.vndb.org/about
wget https://query.vndb.org/extras.sql

# companion scripts
wget https://gist.vinfall.com/Vinfall/setup-vndb-psql/download/HEAD/setup-vndb-psql.sh
wget https://gist.vinfall.com/Vinfall/sqlbin/download/HEAD/setup-sqlbin.sql

Now the file tree should look like this:

$ tree -L 1 ./
./
├── extras.sql
├── fu
├── mithril-vndb
├── setup-sqlbin.sql
├── setup-vndb-psql.sh
├── sqlbin
├── tuwf
├── vndb
├── vndb-db-latest.tar.zst
├── vndb-dev-2025-10-13.tar.gz
└── yhdev

8 directories, 5 file

💡 Tip

As the following commands would make VNDB inaccessible, it’s recommended to setup inside VM.

To make absolute sure the setup would work even if vndb.org and blicky.net expire, now add these lines to /etc/hosts to intentionally block them:

127.0.0.1 vndb.org api.vndb.org dl.vndb.org query.vndb.org
127.0.0.1 blicky.net code.blicky.net g.blicky.net

Setup

I’ll use Alpine Linux Edge as an example to (deliberately) make it more difficult to setup, but it surely works for almost every other up-to-date distro. You might need to change doas to sudo and adapt apk-tools/OpenRC related commands to your package manager/init service.

Also, I assume you’ll only run VNDB locally inside VM with a host-only network with only 22/3000/8000 port forwarding enabled, making data breach impossible. As a consequence, database password and salt are kept simple and stupid. You definitely need to change them and do extra hardening if for whatever reason you decide to ignore my warning and host it publicly.

VNDB

Install dependencies:

# install general deps
doas apk add build-base curl git graphviz icu-data-full pandoc uglify-js vips-dev sassc wget zlib-dev
# install perl modules
doas apk add perl-algorithm-diff-xs perl-anyevent perl-app-cpanminus perl-crypt-urandom perl-dbd-pg perl-dev perl-json-xs perl-module-build
doas cpanm -nq AnyEvent::IRC AnyEvent::Pg Crypt::ScryptKDF Text::MultiMarkdown
# install fu (another perl module)
cd ~/projects/fu
doas cpanm .
# copy mithril.js to vndb/gen
install -pm644 -D ~/projects/mithril-vndb/mithril.js ~/projects/vndb/gen/mithril.js

Build:

# build vndb
cd ~/projects/vndb
make
make prod
util/setup-var.sh

Setup PostgreSQL Server:

# setup psql server
# https://wiki.alpinelinux.org/wiki/Postgresql
doas apk add postgresql18 postgresql18-contrib postgresql18-dev
doas rc-update add postgresql
doas rc-service postgresql start

# init psql admin
doas passwd postgres
# su postgres
$ pg_config --pkglibdir
/usr/lib/postgresql18

# build psql lib
make -C sql/c
sudo make -C sql/c install

Initialize database:

Search cache is disabled by default as the Perl maintenance script is slow by design.

# init database
psql -U postgres -f sql/superuser_init.sql
psql -U postgres vndb -f sql/vndbid.sql
echo "ALTER ROLE vndb       LOGIN PASSWORD 'pwd1'" | psql -U postgres
echo "ALTER ROLE vndb_site  LOGIN PASSWORD 'pwd2'" | psql -U postgres
echo "ALTER ROLE vndb_multi LOGIN PASSWORD 'pwd3'" | psql -U postgres

# create an empty database
# psql -U vndb -f sql/all.sql

# OR import near complete database
# load schema
doas -u postgres psql -c "ALTER ROLE vndb SUPERUSER;"
psql -U vndb -d vndb -f sql/all.sql
doas -u postgres psql -c "ALTER ROLE vndb NOSUPERUSER;"

tar -C var --zstd -xf ../vndb-db-latest.tar.zst

chmod +x setup-vndb-psql.sh
./setup-vndb-psql.sh

Configure:

vim var/conf.pl

form_salt   => '<some unique string>',
scrypt_salt => '<another unique string>',
db_site         => 'dbname=vndb user=vndb_site password=pwd2',
db_task         => 'dbname=vndb user=vndb password=pwd1',

-cookie_defaults => { domain => 'localhost', path => '/' },
+cookie_defaults => { path => '/' },

Run:

# start dev server
util/vndb-dev-server.pl
# start prod server
# util/vndb.pl --fcgi=unix:/tmp/vndb-fastcgi.sock --proc=4 --max-reqs=5000:10000

# housekeeping
# util/task.pl

Now head to http://127.0.0.1:3000.

API

We already install pandoc and uglify-js and run make prod in #VNDB, so just head to http://127.0.0.1:3000/api/kana. You can’t use API button in the homepage as it’s hardcoded.

SQLBin

Compile and Install:

# install deps
doas apk add autoconf automake crystal libcerf-dev libseccomp-dev shards sqlite-dev

# build sqlbin
cd ~/projects/sqlbin
shards
make release
# build customized gnuplot
cd gnuplot # this IS correct
./build.sh

# install
doas install gnuplot-sqlbin /usr/local/bin/gnuplot-sqlbin
cd ..
doas install bin/sqlbin /usr/local/bin/sqlbin

Setup read-only PostgreSQL user:

As we only run it locally, there is no need to import yet another database dump for SQLBin, just setup a seperate read-only user
Runtime Config

psql -U postgres -d vndb -f ~/projects/setup-sqlbin.sql
# psql -U postgres -d vndb -f extras.sql

-- ~/projects/setup-sqlbin.sql

CREATE ROLE sqlbin LOGIN PASSWORD 'secure_passwd';
GRANT CONNECT ON DATABASE vndb TO sqlbin;
GRANT USAGE ON SCHEMA public TO sqlbin;

-- grant read access to all tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO sqlbin;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO sqlbin;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO sqlbin;

-- OR grant on a per-table basis
-- GRANT SELECT ON vn, releases, producers, chars, tags TO sqlbin;

-- grant access to tables not yet created
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO sqlbin;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO sqlbin;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO sqlbin;

-- defense in depth
ALTER ROLE sqlbin_user SET timezone = 'Asia/Shanghai'; -- change to your timezone
ALTER ROLE sqlbin_user SET statement_timeout = '5s';
ALTER ROLE sqlbin_user SET default_transaction_read_only = true;

Test the newly created sqlbin user:

# success
$ psql -U sqlbin -d vndb -c "SELECT count(*) FROM vn;"
count
-------
 61144
(1 row)
# intended failure
$ psql -U sqlbin -d vndb -c "INSERT INTO users (id, username) VALUES ('u999', 'attacker');"
ERROR:  cannot execute INSERT in a read-only transaction

Configure:

mkdir -p ~/.config/sqlbin
vim ~/.config/sqlbin/config.ini
chmod 600 ~/.config/sqlbin/config.ini

[server]
db=postgresql://sqlbin:secure_passwd@127.0.0.1:5432/vndb
storage=~/.local/share/sqlbin
bind=tcp://0.0.0.0:8000/
gnuplot_path=/usr/local/bin/gnuplot-sqlbin

Run:

sqlbin -c ~/.config/sqlbin/config.ini

Now head to http://127.0.0.1:8000.

Misc

Prune PostgreSQL database to start over/import new dump:

# prune database
doas rc-service postgresql stop
doas apk del postgresql18 postgresql18-contrib postgresql18-dev
doas rm -rf /var/lib/postgresql

Test sqlbin config:

psql "postgresql://sqlbin:secure_passwd@127.0.0.1:5432/vndb" -c "SELECT 1;"

Revoke table access of sqlbin user:

REVOKE SELECT ON TABLE schema_version, changes FROM sqlbin;

Also, please do NOT upload source code to GitHub. yorhel.nl explicitly mentions that You will not find me on closed networks such as Discord, Facebook, Github, LinkedIn, Twitter, etc and I hope you respect that, for the time being at least. ↩︎
If you don’t cache searches, the required disk space would be significantly smaller (2G-ish), but many things also work slower. This only includes the system, source code, build files and cached database. Images are not counted and would take extra 45G space (which is strongly discouraged to sync, as mentioned in the begining). ↩︎