Query data out of budibase via REST API

[mattiason]

I need a way where i could make an API call to query for items

The example I have a list with issues I would need to make an API call to get all items with eg status open so I can use those for another app. The API needs to return all the columns for this item.

I would also need to able to search for a certain item, find an item based on the item

[shogunpurple]

Hi @mattiason - so right now we have a bit of an undocumented internal API, that you can use for this purpose. I'll detail the steps for doing exactly what you want to do, including search etc, here.

Step 1: Make your table permissions PUBLIC

Public API means public access - you will need to set the access role of your table to public.

Step 2: Identify Table ID

When viewing your table in the data section, your table ID can be found in the address bar of your browser at the very end. In this case, my table ID is ta_9a515c36132d41cdad006306d283664d

Step 3: Identify your INTERNAL_API_KEY

This can be found in your .env file generated where budibase was initialised. You need this to make external requests to budibase through HTTP.

Step 4: Crafting HTTP Call

So, the test table I've created is just a simple table of Tasks, with one column - a boolean value representing whether they are done or not. I am going to query out the ones that are done, so here's what my HTTP request looks like:

URL: http://localhost:10000/api/ta_9a515c36132d41cdad006306d283664d/search
Method: POST

Headers:
x-budibase-app-id: <your-app-id>
Content-Type: application/json
x-budibase-type: client
x-budibase-api-key: <your-internal-api-key-from-step-3>

Body:
{
    "query": {
        "string": {},
        "fuzzy": {},
        "range": {},
        "equal": {
            "done": "true"
        },
        "notEqual": {},
        "empty": {},
        "notEmpty": {}
    },
    "limit": 50,
    "sortOrder": "descending",
    "sortType": "string",
    "paginate": true
}

Here is a CURL request, that you should be able to tweak with your values to get it working:

curl --location --request POST 'http://localhost:10000/api/ta_9a515c36132d41cdad006306d283664d/search' \
--header 'User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:89.0) Gecko/20100101 Firefox/89.0' \
--header 'Accept: application/json' \
--header 'Accept-Language: en-GB,en;q=0.5' \
--header 'Referer: http://localhost:10000/app_dev_887f393b1c4b4084b888afa7d0d109cf' \
--header 'x-budibase-app-id: app_dev_887f393b1c4b4084b888afa7d0d109cf' \
--header 'Content-Type: application/json' \
--header 'x-budibase-type: client' \
--header 'Origin: http://localhost:10000' \
--header 'Connection: keep-alive' \
--header 'x-budibase-api-key: budibase' \
--data-raw '{
    "query": {
        "string": {},
        "fuzzy": {},
        "range": {},
        "equal": {
            "done": "true"
        },
        "notEqual": {},
        "empty": {},
        "notEmpty": {}
    },
    "limit": 50,
    "sortOrder": "descending",
    "sortType": "string",
    "paginate": true
}'

Hope this helps!

[bderuijsscher]

I have attempted to access Budibase data using this method. However, I keep getting a "Not found" result. However, as soon as I add a specific Cookie authorisation header (used in the GUI - which I hijacked using Chrome Developer Tools), the request succeeds properly. My BB version is 0.9.178

It seems as though the API key from the .env file is not sufficient to access the data. Is that correct?

My current failing curl call is like this:

curl --location --request POST 'http://localhost:10000/api/MY_TABLE_ID/search' \ --header 'User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:89.0) Gecko/20100101 Firefox/89.0' \ --header 'Accept: application/json' \ --header 'Accept-Language: en-GB,en;q=0.5' \ --header 'Referer: http://localhost:10000/MY_APP_ID' \ --header 'x-budibase-app-id: MY_APP_ID' \ --header 'Content-Type: application/json' \ --header 'x-budibase-type: client' \ --header 'Origin: http://localhost:10000' \ --header 'Connection: keep-alive' \ --header 'x-budibase-api-key: MY_API_KEY' \ --data-raw '{ "tableId":"MY_TABLE_ID", "query": { "string": {}, "fuzzy": {}, "range": {}, "equal": { "in_mapcycle" : "true" }, "notEqual": {}, "empty": {}, "notEmpty": {} }, "limit": 50, "sortOrder": "descending", "sortType": "string", "paginate": true }'

The current (temporarily) properly functioning call looks like this (left out details using [OMITTED]):

curl --location --request POST 'http://localhost:10000/api/MY_TABLE_ID/search' \ --header 'User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:89.0) Gecko/20100101 Firefox/89.0' \ --header 'Accept: application/json' \ --header 'Accept-Language: en-GB,en;q=0.5' \ --header 'Referer: http://localhost:10000/MY_APP_ID' \ --header 'x-budibase-app-id: MY_APP_ID' \ --header 'Content-Type: application/json' \ --header 'x-budibase-type: client' \ --header 'Origin: http://localhost:10000' \ --header 'Connection: keep-alive' \ --header 'x-budibase-api-key: MY_API_KEY' \ --header 'Cookie: budibase:auth=[OMITTED]; budibase:auth.sig=[OMITTED]; budibase:currentapp=[OMITTED]; budibase:init=[OMITTED]; budibase:init.sig=[OMITTED]' \ --data-raw '{ "tableId":"MY_TABLE_ID", "query": { "string": {}, "fuzzy": {}, "range": {}, "equal": { "in_mapcycle" : "true" }, "notEqual": {}, "empty": {}, "notEmpty": {} }, "limit": 50, "sortOrder": "descending", "sortType": "string", "paginate": true }'

[cguerin]

Did you figure out how to use the query, fuzzy, etc... items?
Or how to say request a certain field or group of fields?

[bderuijsscher]

I found the error, in my curl request I used the "app_dev_" version of my app. This doesn't work apparently, you need to publish the app and use the published app ID (not containing app_dev obviously).

[mattiason]

Thanks Can is also use this post things back? Kind regards Mattias

…

On Mon, 5 Jul 2021 at 15:52, Martin McKeaveney ***@***.***> wrote: Hi @mattiason <https://github.com/mattiason> - so right now we have a bit of an undocumented internal API, that you can use for this purpose. I'll detail the steps for doing exactly what you want to do, including search etc, here. Step 1: Make your table permissions PUBLIC Public API means public access - you will need to set the access role of your table to public. [image: Screenshot 2021-07-05 at 14 40 57] <https://user-images.githubusercontent.com/11256663/124480293-04807c80-dd9f-11eb-98c2-59aebde21987.png> Step 2: Identify Table ID When viewing your table in the data section, your table ID can be found in the address bar of your browser at the very end. In this case, my table ID is ta_9a515c36132d41cdad006306d283664d [image: Screenshot 2021-07-05 at 14 42 08] <https://user-images.githubusercontent.com/11256663/124480458-2ed23a00-dd9f-11eb-8d76-23a3d32e7ae2.png> Step 3: Identify your INTERNAL_API_KEY This can be found in your .env file generated where budibase was initialised. You need this to make external requests to budibase through HTTP. Step 4: Crafting HTTP Call So, the test table I've created is just a simple table of Tasks, with one column - a boolean value representing whether they are done or not. *I am going to query out the ones that are done*, so here's what my HTTP request looks like: URL: http://localhost:10000/api/ta_9a515c36132d41cdad006306d283664d/search Method: POST Headers: x-budibase-app-id: <your-app-id> Content-Type: application/json x-budibase-type: client x-budibase-api-key: <your-internal-api-key-from-step-3> Body: { "query": { "string": {}, "fuzzy": {}, "range": {}, "equal": { "done": "true" }, "notEqual": {}, "empty": {}, "notEmpty": {} }, "limit": 50, "sortOrder": "descending", "sortType": "string", "paginate": true } Here is a CURL request, that you should be able to tweak with your values to get it working: curl --location --request POST 'http://localhost:10000/api/ta_9a515c36132d41cdad006306d283664d/search' \ --header 'User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:89.0) Gecko/20100101 Firefox/89.0' \ --header 'Accept: application/json' \ --header 'Accept-Language: en-GB,en;q=0.5' \ --header 'Referer: http://localhost:10000/app_dev_887f393b1c4b4084b888afa7d0d109cf' \ --header 'x-budibase-app-id: app_dev_887f393b1c4b4084b888afa7d0d109cf' \ --header 'Content-Type: application/json' \ --header 'x-budibase-type: client' \ --header 'Origin: http://localhost:10000' \ --header 'Connection: keep-alive' \ --header 'x-budibase-api-key: budibase' \ --data-raw '{ "query": { "string": {}, "fuzzy": {}, "range": {}, "equal": { "done": "true" }, "notEqual": {}, "empty": {}, "notEmpty": {} }, "limit": 50, "sortOrder": "descending", "sortType": "string", "paginate": true }' Hope this helps! — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#1915 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AK54OPARE6I6EPSYNAKGHQ3TWG2KRANCNFSM472UBFGA> .

[shogunpurple]

Yep, you can.

curl --location --request POST 'http://localhost:10000/api/ta_9a515c36132d41cdad006306d283664d/rows' \
--header 'User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:89.0) Gecko/20100101 Firefox/89.0' \
--header 'Accept: application/json' \
--header 'Accept-Language: en-GB,en;q=0.5' \
--header 'Referer: http://localhost:10000/app_dev_887f393b1c4b4084b888afa7d0d109cf' \
--header 'x-budibase-app-id: app_dev_887f393b1c4b4084b888afa7d0d109cf' \
--header 'Content-Type: application/json' \
--header 'x-budibase-type: client' \
--header 'Origin: http://localhost:10000' \
--header 'Connection: keep-alive' \
--header 'x-budibase-api-key: budibase' \
--data-raw '{"done":true,"tableId":"ta_9a515c36132d41cdad006306d283664d"}'

[mattiason]

Thanks! Is there any way i can add multiple items via API aswell?

…

On Mon, 5 Jul 2021 at 16:34, Martin McKeaveney ***@***.***> wrote: Yep, you can. curl --location --request POST 'http://localhost:10000/api/ta_9a515c36132d41cdad006306d283664d/rows' \ --header 'User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:89.0) Gecko/20100101 Firefox/89.0' \ --header 'Accept: application/json' \ --header 'Accept-Language: en-GB,en;q=0.5' \ --header 'Referer: http://localhost:10000/app_dev_887f393b1c4b4084b888afa7d0d109cf' \ --header 'x-budibase-app-id: app_dev_887f393b1c4b4084b888afa7d0d109cf' \ --header 'Content-Type: application/json' \ --header 'x-budibase-type: client' \ --header 'Origin: http://localhost:10000' \ --header 'Connection: keep-alive' \ --header 'x-budibase-api-key: budibase' \ --data-raw '{"done":true,"tableId":"ta_9a515c36132d41cdad006306d283664d"}' — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#1915 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AK54OPGIEJXW2RPLD6KMF73TWG7HJANCNFSM472UBFGA> .

[tluyben]

Will this be made official? Maybe with a automatic Swagger file like nocodb for instance? Because of the automations set up with Budibase, we need to push everything via it and this is not as fluent as you already indicated? Is the plan to make this an official part? It seems not very hard to actually just push a Swagger file for every data source. And make a little API Key setup dialog for instance?

[mjashanks]

Absolutely, this needs to happen - it's a matter of prioritisation. We fully intend to make this official, but won't come in 2021.

[tluyben]

Thank you! Apologies for being lazy and not checking myself, but maybe others are helped with this as well; I assume there are internal APIs for meta tasks as well? Creating tables, adding columns etc?

[mjashanks]

For sure, everything you can do in budibase is available through its rest API. Its just not documented!

[cguerin]

Would love a few simple GET apis in there... such as...

GET http://budibase:port/api/table/{mytable}
GET http://budibase:port/api/table/{mytable}/1

and perhaps some POST insert and update.

Assuming the budibase tables have an SQL parser?

[Tharkis]

Add me to the list of people that want see this as an officially supported (and documented) feature!

[rantoniuk]

How about adding https://github.com/PostgREST/postgrest into the mix to by default expose the tables via REST API? (see also https://supabase.com/).