This has been working perfectly for me:
Don't advise FireBird.
Take some mainstream, like MySql, MariaDB, Postgress (or Sqlite for lightweight). FPC supports all f them.
Don't consider SQLite if you might need multiple users or even a single user working from a different system. I believe it now has that capability, but it wasn't "designed in".
I believe that MySQL/MariaDB have a somewhat fluid API. They're popular, but from the POV of Lazarus that doesn't confer much of an advantage.
Firebird and PostgreSQL have fairly compatible SQL dialects, and they both have a notification mechanism (supported by an LCL component) which can be extremely useful.
When experimenting with it around five years ago, I found the Firebird documentation to be poor: multiple manuals with version addenda.
I've been using PostgreSQL for 20+ years, so am biased.
So from my biased POV: SQLite for single-user operation, where it has supplanted e.g. Berkeley DB for configuration files and working storage. Postgres for everything else.
MarkMLl