I'm using CL-SQLITE, a Common Lisp wrapper for SQLite3 written using CFFI (the Common Foreign-Function Interface). My preferred C runtime is UCRT64 as distributed with MSYS. Unfortunately, MSYS uses its own naming conventions for DLLs, so the default filenames that CFFI searches for will not be found. I want to tell CL-SQLITE to load my special DLL instead of searching for the default option. I do not want to modify CL-SQLITE code since that would create a maintenance burden. I also do not want to do "the easy thing" and copy the DLL to my project directory, renaming it as needed; when I incorporate other shared libraries into my project, these copy operations will become a problem.
I'm also using ASDF and DEPLOY, so if those tools provide a solution, then I can work with that.
I tried to use DEPLOY's define-library to point to my local DLL, but define-library depends on successfully loading the CFFI system. CL-SQLITE won't successfully QL:QUICKLOAD because the DLL it's looking for has a different name than the one I have.
I also tried adding a directory to cffi:*foreign-library-directories*. This did not solve the problem because the file name is different; the directory name is not the issue.
I misread the question at first, thinking that OP was using CLSQL instead of CL-SQLITE. I have left the original answer below. Both answers are fundamentally the same, but CLSQL provides additional means to configure the library load path.
The system is looking for either sqlite3.dll or libsqlite3.dll, but this file is named libsqlite3-0.dll in my MSYS2 installation, where it can be found in C:\msys64\ucrt64\bin\. You can create a soft link to the installed library file named sqlite3.dll from the MSYS2 UCRT64 command prompt:
$ cd /c/msys64/ucrt64/bin/
$ ln -s libsqlite3-0.dll sqlite3.dll
The CL-SQLITE installation instructions say that the Windows PATH environment variable needs to contain a path to the directory containing sqlite3.dll, so add that in the usual way, e.g., from the Windows Control Panel.
After these two steps CL-SQLITE should load as expected.
Setting the Windows PATH variable will also allow CLSQL to find sqlite3.dll, but CLSQL provides an alternative way to set the load path which is described in my original answer below.
I had the same problem and had to do two things to solve it.
In my installation of MSYS2 the sqlite3 library was named libsqlite3-0.dll. From a UCRT64 terminal window I created a soft link named sqlite3.dll in that directory:
$ cd /c/msys64/ucrt64/bin/
$ ln -s libsqlite3-0.dll sqlite3.dll
Then I had to add a search path so that CLSQL could find the library code. You can do CL-USER> (clsql:push-library-path #P"C:/msys64/ucrt64/bin/") after loading CLSQL with Quicklisp, or you can create an initialization file. This isn't very well documented: there is a brief mention in the CLSQL docs, and some more information can be gleaned from searching the CLSQL change log for clsql-init. It appears that you can add a file named clsql-init.lisp under /etc/ on *nix systems, or under c:\etc\clsql-init.lisp on Windows systems, or under ~/.clsql-init.lisp on either type of system. Note that this last variation is a dot file.
I added .clsql-init.lisp to my home directory:
;;; Add library path for sqlite3.
(clsql:push-library-path #P"C:/msys64/ucrt64/bin/")
After making these two changes everything should work as expected.