Earlier quoted context omitted.
I worked for a company that built the same thing in the mid-2000s, and it did not solve all dependency issues, because there's multiple kinds of issues. They're actually kind of unsolveable. I list some of the reasons here https://gist.github.com/peterwwillis/e96854532f471c739983c0b...
Thanks for sharing. Agreed that runtime dependency conflicts are unavoidable for most programming languages. Your example of sqlite database format conflicts is also a good one. 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 image…
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.