Creating Your First SQL Query

To illustrate creating a Query, we'll use a public database of research data called “Rfam” as an example: https://docs.rfam.org/en/latest/database.html

Once you have set up your first Datasource, you will be able to create Queries in the PocketQuery admin interface. For this, head over to the Queries tab:

On the Queries tab, you will be able to create your first Query by clicking the Let’s create my first Query! button:

Our research database contains a rather simple author table that we can use. This is how you create a Query with it:

1. Name your Query, in our case “Rfam Authors”

2. Select the “Rfam” Datasource we created in “Creating Your First SQL Datasource”

3. Insert this SQL statement:

   SQL

   SELECT author_id, name, last_name
   FROM author

4. Click Create Query to save your first SQL Query

You can now execute it, using the PocketQuery macro in one of your Confluence pages. So, create a new Confluence page and insert a PocketQuery macro by typing /PocketQuery:

Open the PocketQuery macro editor by clicking the Edit button:

Here you can select your “Rfam Authors” Query that you have just created. Once selected, PocketQuery will execute your SQL against the database and present you with the results:

That’s it! Easy, right? You now know how to use PocketQuery to pull in data from a database into your Confluence page. But there’s actually a lot more you can do with PocketQuery. For example, you can define parameters in your SQL statement to make your Queries more dynamic and reusable. Want to see how?

Continue with “Adding Parameters to Your Query“