I definitely won't claim that nix has solved runtime dependency conflicts, but it does decisively solve build-time conflicts.
Similar to containers like you describe, but reproducible and don't require storing and shipping opaque binary images around. Reproducibility is really the killer feature, IMO. I inherited a bunch of containers that don't build anymore because the build instructions weren't reproducible. Having an old container image that still runs is just tech debt if yiu can't edit the build instructions and genrrate a new one.
Now, if one application uses a more recent version of SQLite and also makes use of some new feature (say, for example, generated columns which were added in release 3.31.0) then that might result in a database file that is unreadable by older versions since the older version won't be able to interpret the generated column syntax. But if you assume that all different versions of SQLite that you use have support for all of the features used in the database file (a reasonable assumption in most cases) then the database files are completely portable across versions. Different versions of SQLite can read/write the same database file concurrently. And newer versions of SQLite are always able to read/write databases created by older versions of SQLite, without exception or precondition.
So perhaps the "sqlite format conflict" example was not the best, in as much as it will always work as long as the package manager installs the most recent version of SQLite.
All of the above is also true for the API.