Direct Database Query
Write SQL statements to query your Xano databases.
The Direct Database Query function is available starting on the upgraded (non-Legacy) Launch or Scale plans. If you have any questions, please reach out to support.
To access the Direct Database Query function, add a new function to your function stack, choose Database Operations, and then Direct Database Query.
From the Direct Database Query panel, you can provide the following:
Code - This is the query you would like to perform
Statement Args - If you specify arguments using ? in your code, you can use this section to sequentially fill in those arguments with other data, such as variables or inputs previously defined in your function stack
Response Type - Return either a single item, or a list of items
Output Variable - The name of the variable that will contain the result of the query
Finding your database identifier
The database identifier can be found by combining the workspace ID and database table ID with 'mvpw'.
For example:
If workspace ID = 1 and ID = 3: mvpw1_3
If workspace ID = 500 and table ID = 3913: mvpw500_3913
Using the mvpw selector will return two columns: id and xdo, with xdo containing each record's content. For SELECT statements where you want to return specific columns, use 'x' instead.
You can also use an x identifier, such as x1_3, to return a more readable view of the data. Please note, however, that these views do not always have function parity with working with the mvpw_ version of your tables (such as when performing inserts).
Using x_ is typically best when just performing queries.
Direct Database Query allows you to query tables across your Instance. For example, if you are in workspace 2, you can query a table from workspace 1 using the above syntax. Please do so carefully to not misuse sensitive data in a query.
Using Custom Aliases
You can leverage custom view aliases to make direct database queries easier to write, and more readable, based on exactly the data that you need.
Head to your database table, and create a custom view. When creating your custom view, you can provide a Database View Alias, which you can use in your Direct Database Query statement.
In the screenshot below, we've created a database view to filter people named David in our people table. When saving the view, we're providing an alias called 'david'.
Once this view is saved, we can utilize it in a Direct Database Query function to retrieve the data from that view. The view is listed when using the Direct Query wizard, or you can type it manually if you are writing a query from scratch as SELECT * from "view_name";
Test Data
Assuming our data source is named 'test', mvpw599_2377 would become mvpw599_test_2377. You can replace 'test' in this example with the name of your data source.
Direct Database Query does not respond to the selection of your data source in Xano or the data source specified in any external requests. You need to specifically state the test data source in the function. It is not possible at this time to dynamically modify the table selector.
Statement Arguments
Statement arguments enable dynamic values in your queries. Statement arguments are designed to come from variables, inputs, or environment variables. A ? in the query will identify where a statement argument should be placed; they will be placed in sequential order.
Statement arguments are escaped with single quotes by default.
In situations where you want to escape the argument value with double quotes, use ?:alias.
To insert an argument value with no quotes, such as a table name, use ?:raw.
| Argument Type | Query Syntax | Result |
|---|---|---|
Default |
| 'example' |
Alias |
| "example" |
Raw |
| example |
Example:
In the following query, there are two statement arguments. The input search will be placed at the first ? and the variable var_1 will be placed at the second ?.
Arguments can not, at this time, be anything other than single values. Arguments can not also replace functions; they can only serve as query values at this time.
SQL Query Wizard
The SQL Query Wizard generates simple SQL queries. It is not designed to support complex statements or joins but basic statements to help get you started.
Step 1: Choose the Database Table to Query
Step 2: Choose the field
Step 3: Choose an operator and value.
Choose an operator for the query and add a value. Optionally include multiple conditions with AND or OR statements.
Step 4: Select the columns to include in the query response.
The Wizard will process the settings and generate a SQL query in the code editor.
The result returns a list from merchant where desc = Pizza.
What's the difference between Direct Database Query and Direct Database Access?
Direct Database Access is a premium add-on that allows you to connect directly to your Xano PostgreSQL database using an external tool. If you would like to leverage something outside of Xano to manage your database, direct database access is the feature you're looking for.
The Direct Database Query function in Xano is available if you want to simply run SQL queries from inside Xano.
Last updated
