# How can I query the database using variables?
**URL:** https://org-roam.discourse.group/t/how-can-i-query-the-database-using-variables/3562
**Category:** How To
**Created:** [August 21, 2024, 8:47am UTC](https://org-roam.discourse.group/t/how-can-i-query-the-database-using-variables/3562 "2024-08-21T08:47:25Z")
**Posts on this page:** 7
**Page:** 1
### Author:  [@lyndhurst](https://org-roam.discourse.group/u/lyndhurst)
#### Post date: [August 21, 2024, 8:47am UTC](https://org-roam.discourse.group/t/how-can-i-query-the-database-using-variables/3562/1 "2024-08-21T08:47:25Z")
I am trying to query the database from within a function using variables.
In my tests, I tried gathering all files which have a property named “TYPE” set to the value “how to”.
I could not find exhaustive documentation about `org-roam-db-query`, and I usually use the `org-roam-ql` to do queries. But I was getting errors too using variables, so I figured I should remove the middle man to try and solve that problem.
Thanks to a few examples here and there, I can retrieve a list of 40 files using the code below:
```lisp
(defun my/test ()
(let (
(files
(org-roam-db-query
[
:select [file]
:from nodes
:where (like properties '"%TYPE%how to%")
])))
(length files)))
(my/test "how to")
```
I will spare you all my other attempts, the best I could do was writing the follwing function which returns 0 instead of 40, but has at least the merit of not throwing a `stringp` type error:
```lisp
(defun my/test (type)
(let* (
(prop (concat "%TYPE%" (format "%s" type) "%"))
(files
(org-roam-db-query
[
:select [file]
:from nodes
:where (like properties (quote prop))
]
)))
(length files)))
(my/test "how to")
```
I would really appreciate some help because I do have a few functions depending on that kind of logic, and I am really stuck (and I have not even started trying to query using tags!).
---
### Author:  [@dmg](https://org-roam.discourse.group/u/dmg)
#### Post date: [August 22, 2024, 2:24am UTC](https://org-roam.discourse.group/t/how-can-i-query-the-database-using-variables/3562/2 "2024-08-22T02:24:21Z")
Do the following:
- create the entire query as a string, rather than the recommended way
- doing so requires to escape % and "
- to escape % double it
- to escape ", backslash it
for example: this query retrieves the properites of nodes with the category “faq\_git”
```
(org-roam-db-query "select properties
from nodes
where properties LIKE '%%\"CATEGORY\" . \"faq_git\"%%'
")
```
and once you know the syntax, you can use the typical manner, escaping the characters as necessary:
```
(org-roam-db-query [:select [properties]
:from nodes
:where (like properties $r1 )
] "%%\"CATEGORY\" . \"faq_git\"%%")
```
so the key seems to be to properly escape the % and " in the string.
It appers that you need a string st such that (format st) will return the string (without single quotes) that SQL expects.
```
#+begin_src emacs-lisp :exports both
(format "select properties
from nodes
where properties LIKE '%%\"CATEGORY\" . \"faq_git\"%%'
")
#+end_src
#+RESULTS:
#+begin_example
select properties
from nodes
where properties LIKE '%"CATEGORY" . "faq_git"%'
#+end_example
```
This also seems to work (using quote, only quoting ")
```
(org-roam-db-query [:select [properties]
:from nodes
:where (like properties $r1 )
] (quote "%\"CATEGORY\" . \"faq_git\"%"))
```
---
### Author:  [@lyndhurst](https://org-roam.discourse.group/u/lyndhurst)
#### Post date: [August 22, 2024, 8:18am UTC](https://org-roam.discourse.group/t/how-can-i-query-the-database-using-variables/3562/3 "2024-08-22T08:18:30Z")
Thank you for all the details, that was very helpful.
I still have a lot to understand about the syntax before being comfrotable querying the database, but that’s progress 👍
---
### Author:  [@ahmed-shariff](https://org-roam.discourse.group/u/ahmed-shariff)
#### Post date: [August 26, 2024, 6:25am UTC](https://org-roam.discourse.group/t/how-can-i-query-the-database-using-variables/3562/4 "2024-08-26T06:25:50Z")
`org-roam-ql` author here, if you need help with using the package, don’t hesitate to reach out.
---
### Author:  [@dmg](https://org-roam.discourse.group/u/dmg)
#### Post date: [August 27, 2024, 2:55am UTC](https://org-roam.discourse.group/t/how-can-i-query-the-database-using-variables/3562/5 "2024-08-27T02:55:25Z")
quick question. Is there a way to call elisp functions in sqlite (as user-defined functions, similar to the way it can be done in python using conn.create\_function (see create\_function in [sqlite3 — DB-API 2.0 interface for SQLite databases — Python 3.12.5 documentation](https://docs.python.org/3/library/sqlite3.html))
---
### Author:  [@lyndhurst](https://org-roam.discourse.group/u/lyndhurst)
#### Post date: [August 27, 2024, 6:58am UTC](https://org-roam.discourse.group/t/how-can-i-query-the-database-using-variables/3562/6 "2024-08-27T06:58:56Z")
Thanks, I did actually, and, backquotes easily solved the problem with `org-roam-ql`. When using `org-roam-db-query` I tend to go for the `(format "%s" sql-string)` solution from @dmg answer.
---
### Author:  [@ahmed-shariff](https://org-roam.discourse.group/u/ahmed-shariff)
#### Post date: [August 27, 2024, 5:31pm UTC](https://org-roam.discourse.group/t/how-can-i-query-the-database-using-variables/3562/7 "2024-08-27T17:31:31Z")
As far as I know, the `emacsql` package doesn’t have that functionality. My understanding is it requires a pointer to a routine sqlite can call, and I am not sure how to even go about that. You may want to ask the authors there to see if it possible.