Database backend

[kamilsi]

As we are progressing in the development of our R-based randomization API, one crucial decision we need to make is selecting the right database engine that aligns with our project requirements and future scalability. I would like to open a discussion to gather insights and opinions on the best-suited database options.

Key Requirements:

Our choice of database should fulfill the following requirements:

1. Auto Timestamp & ID Generation: The database must automatically generate timestamps and unique identifiers for each record. This feature is essential for tracking data changes and maintaining data integrity.
2. Uniqueness Enforcement: The database should have robust mechanisms to enforce the uniqueness of certain fields, ensuring no duplicate records are created.
3. Backup and Cloud Compatibility: The ability to perform regular backups is crucial, and if possible, the database should be compatible with cloud storage solutions for backups, ensuring data safety and disaster recovery.
4. R Integration: The database must be compatible with R, preferably with an established R driver for seamless integration into our existing systems and workflows.
5. Data Type Support: We need the database to support a variety of data types, including text, numerical, date/time, and JSON. This versatility is necessary to accommodate the diverse data we handle in clinical studies.

Potential Options:

1. PostgreSQL: Known for its advanced features and support for a wide range of data types, including JSON. Default choice so far.
2. DuckDB: Recently showcased as a good fit for R at a conference. Known for its efficient analytical processing capabilities and ease of use with R. Notably, it doesn't require an additional container (single-file build) and is available under a permissive MIT License, making it highly accessible and easy to integrate.

[ErdaradunGaztea]

Seems like there's a JSON extension for DuckDB, I believe it even allows JSON value storage, which could be useful (not as much as in GeneSense, but still).