Use a database as a backend for JabRef library management

[InAnYan]

Is your suggestion for improvement related to a problem? Please describe.

Currently, JabRef struggles with libraries that have over 1000 entries (#10209).

Short reason and solution: JabRef stores all information in RAM. JabRef needs a mechanism to manage lots of data. This is a perfect use case for databases!

Longer issue description: look at how JabRef manages libraries and entries:

1. Load .bib file.
2. Convert .bib file into BibDatabase (with BibDatabaseContext) and BibEntry. Those are Java objects that are stored in RAM.
3. Manipulate library with those objects.
4. Save those objects into a .bib file.

So, JabRef's original philosophy is to be a file editor. However, when you have a giant library, you just don't have enough JVM heap. It is limited.

Describe the solution you'd like

JabRef should have a mechanism for managing a lot of data and use it for storing and manipulating libraries.

This is the purpose of databases! A DBMS will also cache data: a typical DBMS stores data in pages. Some pages are stored in RAM, some are offloaded to disk. This is a perfect solution for giant libraries, as now you are not limited to RAM space, but to space on your HDD/SDD!

Moreover, DBMS allows you to query data fast and powerful. Here is one place where SQL can be used: #10209 (comment). Search functionality is also a perfect case for databases.

Additional context

This is planned as a GSoC project. Beware, while this project is quite important for JabRef, it might turn out to be very complex.

We aim for a Relational DBMS like SQLite, DuckDB, Postgres. Especially, we want a database to be embedded.

In fact, we want Postgres to be our backend, as Postgres has powerful capabilities for search. It can be used as an embedded database, actually; checkout this library: https://github.com/zonkyio/embedded-postgres.

Here are some materials for this project:

• Postgres: https://www.postgresql.org/.
• Other databases you might consider (though, Postgres is preferable):
  • DuckDB: https://duckdb.org/ -- seems promising too. Can do JNI and thus could save a process: https://github.com/duckdb/duckdb-java/blob/main/src/jni/duckdb_java.cpp#L25
  • SQLite: https://www.sqlite.org/.
  • H2: https://www.h2database.com/html/main.html.
  • HSQLDB: https://hsqldb.org/.
• BibTeX and BibLaTeX (you can use this information to design the schema of the DB):
  • Internals of BibTeX: https://polish-mirror.evolution-host.com/ctan/biblio/bibtex/base/btxdoc.pdf.
  • Internals of BibLaTeX: https://mirrors.ibiblio.org/CTAN/macros/latex/contrib/biblatex/doc/biblatex.pdf.
• How Zotero internally stores data: https://github.com/zotero/zotero/blob/main/resource/schema/userdata.sql.
• Use Postgres as an embedded database: https://github.com/zonkyio/embedded-postgres.
• Take a look at JabRef's code:
  • Search functionality: https://github.com/JabRef/jabref/blob/main/src/main/java/org/jabref/model/search/PostgreConstants.java#L6. (It already uses embedded Postgres).
  • Shared database: https://github.com/JabRef/jabref/blob/main/src/main/java/org/jabref/logic/shared/PostgreSQLProcessor.java (schemas, etc.)

[koppor]

Regarding the de-coupling of the UI with the data, starting points are

• https://github.com/privatejava/javafx-dynamic-tableview
• https://genotechies.medium.com/javafx-tableview-with-dynamically-filled-data-from-a-list-89ff6f8778e1

[koppor]

In last year's GSoC we opted for Postgres, because it has many plugins, especially for fast regular expression search so that the DBMS does the regex indexing and resolving - and not the client.

[ryan-carpenter]

XTDB? There is so much more to do with literature than a bibtex/biblatex schema covers, and immutable data would enable some great opportunities. Examples include a traceable history of screening or detecting duplicates from the past (recognising previously imported entries even after they have changed).

• https://docs.xtdb.com/intro/what-is-xtdb.html
• https://docs.xtdb.com/concepts/key-concepts.html
• https://docs.xtdb.com/ops/config/storage.html#_top
• https://docs.xtdb.com/reference/main/stdlib/string.html (uses Java regex patterns)
• https://docs.xtdb.com/drivers/java.html

[koppor]

Since this is about memory saving, more things need to be considered:

Loading of a .bib file

• If the bib file is read with "plain" JabRef, strings might be in memory, too
• latex-to-unicode and unicode-to-latex (for search) is done in the DB, not in Java (hopefully --> stored procedure)

Presenting data to JabRef

• Tableviews might help here to gain speed and reduce memory consumption on JabRef's side.

Saving of a .bib file

• If the bib file is saved with "plain" JabRef, strings might be in memory, too
• Postgres should offer some pre-formatting for saving (e.g., views, stored procedure, ...)
• Maybe, a "simple" table view also helps

[koppor]

One decision driver: do not spawn another process (because this might be forbidden in certain environments)

[ungerts]

In my opinion, the scope of a technology decision should be broader and not limited solely to storing BibTeX entries. I'm concerned that JabRef may end up using too many technologies in parallel. For example, abbreviations are currently implemented using MVStore. Ideally, JabRef should rely on a single storage technology.

I'm also not in favor of packaging a full PostgreSQL database with JabRef for several reasons:

• Security: PostgreSQL may open network ports, and if poorly configured, could introduce potential security vulnerabilities.
• Resource Usage: PostgreSQL is a server-grade database meant to run on dedicated systems. Running it on end-user machines can consume significant CPU, memory, and disk resources, negatively impacting performance and user experience. Additionally, managing Postgres-specific tasks like cache configuration or vacuuming to reclaim disk space may be beyond the intended scope of JabRef.
• Initialization and Portability: SQLite database files, for example, can be easily copied and delivered as part of the application, simplifying distribution - particularly for features like abbreviations.

That said, even file-based databases should be carefully evaluated. For instance, DuckDB currently does not support automatic updates to full-text search indexes when the input table changes

Conclusion:
A detailed architectural decision should be made regarding JabRef's persistence technology, taking into account scalability, performance, maintainability, and user environment constraints.

[ungerts]

Just for fun, I created a comparison with a little help from ChatGPT. It might not be perfectly accurate, but in my opinion, using comparisons like this - along with prototypes - is the right approach for making sustainable decisions.

Criteria	SQLite 🥇 (Recommended)	DuckDB 🥈 (Alternative)	PostgreSQL ❌ (Not Ideal)	HSQLDB ⚠️ (Legacy Option)	H2 🟡 (Viable Alternative)
Installation Required	❌ None – fully embedded	❌ None – fully embedded	✅ Yes – needs external server	❌ None – fully embedded	❌ None – fully embedded
Resource Usage	🟢 Very low	🟢 Low to moderate	🔴 High	🟢 Low	🟢 Low
Crash Safety	🟢 Excellent – no external processes	🟢 Excellent – no external processes	🔴 Poor – background processes may linger	🟡 Historically had issues	🟢 Generally good
Performance (Local Usage)	🟢 Good for most library sizes (<10k entries)	🟢 Optimized for analytics, fast on big data	🟢 Great – but overkill for local use	🟡 Adequate but aging	🟢 Fast and efficient
Scalability	🟡 Medium – may slow with very large DBs	🟢 High – columnar storage efficient	🟢 High – built for large concurrent loads	🟡 Medium	🟡 Medium to good
Multi-user Support	🔴 No – single-writer only	🔴 No – not built for concurrent access	🟢 Yes – designed for multi-user environments	🔴 Poor – file-based, single user	🔴 Basic – embedded usage only
Java Integration	🟢 Mature and stable JDBC drivers	🟢 Good – newer but actively improving	🟢 Mature JDBC ecosystem	🟢 Native Java	🟢 Native Java
Access Method	🔹 JNI (Java Native Interface)	🔹 JNI (Java Native Interface)	🔸 Network via TCP/IP	🟢 Direct Java API	🟢 Direct Java API
Cross-platform Compatibility	🟢 Excellent	🟢 Excellent	🟢 Excellent	🟢 Excellent	🟢 Excellent
Query Features	🟡 Basic SQL	🟢 Advanced analytics, SQL + functions	🟢 Full SQL, indexing, full-text search	🟡 Basic SQL	🟢 Full SQL, stored procedures
Full-Text Search (FTS)	🟢 Yes – via FTS5 extension	🟢 Yes – via FTS extension (DuckDB Docs)	🟢 Yes – built-in full-text search	🔴 Very limited or none	🟢 Yes – via native or Lucene (H2 Docs)
Portability (Database File)	🟢 Excellent – single .db file	🟢 Excellent – file-based	🔴 Poor – tied to server instance	🟢 Good – single file	🟢 Good – file-based
Ease of Embedding in JabRef	🟢 Very easy	🟢 Easy	🔴 Complex – requires local server connection	🟢 Easy – Java-native	🟢 Easy – Java-native
End-user Complexity	🟢 Transparent – no setup or config	🟢 Transparent	🔴 Requires setup and config	🟢 Transparent	🟢 Transparent
Fits Intended Use?	✅ Yes – built for local embedded use	🟡 Partially – analytics-focused	❌ No – intended for server environments	🟡 Partial – legacy option	✅ Yes – built for embedded Java apps