In fairness, it's reasonable to expect a developer's machine to have the relevant -dev packages on it. Unfortunately, in the case of Debian/Raspbian the lack of the symlink set up by installation of the -dev package impacts usability of end-user systems which normally wouldn't (and possibly shouldn't) have developer packages installed.
My recollection is that the underlying SQL connection components are thin wrappers around classes in the FCL, but I can't remember whether I've used the FCL directly other than for minimal bug report code.
I tend to be oriented towards PostgreSQL here, when I was shopping around for a suitable database back in the early 00s it was the only accessible one that had comprehensive transaction support which was something I needed badly. Having all libraries in the right place was not an issue with Slackware and Windows ODBC/BDE which were what I was using at the time, I suspect that Debian initially set things up properly and when the missing symlink became an issue I simply started creating a symlink and (later) installing the -dev package as routine... something which I'm not entirely happy doing on a non-development machine.
My recollection is that the FCL component gained a search path as an alternative way of fixing the same problem. Now you /could/ look for the .so library in a sequence of "usual suspect" directories, but that implies that application-level code is making the decision that xxx-1.2.3.so is a suitable implementation of the API defined for xxx.so, which is something that should really be done by the package maintainer at the distro level. So in short, this is an distro-level problem which for some reason Debian et al. are refusing to fix, and FPC/Lazarus allow the path to be set at the application level as a not-entirely-satisfactory way around the problem.
I agree that the library path is probably something you want in your configuration file, with the obvious caveat that it will make the configuration version-specific. What I've got here is a .ini file defining how apps are to connect to the database, after which they get everything else from scripts and scheduler tables stored in the database itself: and that .ini file has not needed maintaining for a long time.
I'd suggest that the best solution would be a hybrid sequence:
* Look for an explicit path in the .ini file. If there isn't one then:
* Attempt to make the connection without an explicit path.
* Look for well-known (tested) library names that the distro /should/ have symlinked.
* If this fails, e.g. because your "well known" sequence goes up to xxx-1.2.3.so but the library is now at xxx-1.2.4.so then put it as an explicit parameter in the .ini file until the next software release, then remove it.
I've got a bit of example code here but I don't think it would tell you anything you don't already know, the only bit which might be useful is that the comments suggest that the FCL gained the path parameter > 2.6.0 and the LCL gained a wrapper component at some point after 1.0.
MarkMLl