Changing the dir errors and hunchentoot - Dir not found?

I use a macro called use-db and change-to-path in a lot of my projects.

(defmacro use-db (db project-name &body query)
  `(progn
     (change-to-path ,project-name)
     (clsql:connect ,db :database-type :sqlite3)
     (unwind-protect (progn ,@query)
       (clsql:disconnect :database ,db))))

(defmacro change-to-path (project-name)
  `(uiop:chdir (merge-pathnames (write-to-string ,project-name) "/Users/vince/quicklisp/local-projects/")))

As you can see, change to path changes the repl dir. Then use-db reads the sqlite db from the root of the project folder. This works fine.

However, when I try to read a db as part of a route (easy-routes + hunchentoot):

(defroute test-file ("/results" :method :post)
    ()
  (get-one-col-as-list-db #'first "firstname"))


(defun get-one-col-as-list-db (fn tablename)
  (ki:use-db "new.db" 'custom-sender
    (mapcar fn
        (clsql:query
         (concatenate 'string "Select * from " tablename)))))

I get the following error:

Error in SB-POSIX:CHDIR: No such file or directory (2)

without using the change-to-path macro, clsql function would note find the database.

Is there a better way to use sqlite (from the file system) and manage the pathing for specific db files?

Solution

To change the current working directory, temporarily, use uiop:with-current-directory:

(uiop:with-current-directory ("/tmp/")
   (uiop:getcwd))

(edit) chdir has a side effect: it effectively changes the current working directory (uiop:getcwd), while with-current-directory doesn't, or it does but only temporarily for the code under it. For your sanity you want to avoid chdir's side effets (or I do, I want to work at a project root). It could have an effect on bare-bones Hunchentoot, if it looks at a www/ directory under the project root, but in your case I'm not sure.

Your use-db mimics a well-known pattern: get a resource, and be sure to release it in case of errors. These macros are often named "with-something", just like "with-current-directory". Does CLSQL have such a macro? It does: with-database.

Why is your change-to-path a macro? It can be a function that concatenates strings and calls chdir.

Inside use-db, "project-name" is an unknown variable. Look at the compiler warnings:

; caught WARNING:
;   undefined variable: COMMON-LISP-USER::PROJECT-NAME

How do you use the two anyways?

Error in SB-POSIX:CHDIR: No such file or directory (2) […] without using the change-to-path macro

what is the directory it tries to go to? You'd have a tip on how things are not working.