Create an API

​

Introduction

The framework automatically creates an API for you based on the queries you have defined in your project. This means that you can easily expose your data to any other app or service.

In this tutorial, we will learn to:

1. Create a new data app
2. Add a data source
3. Write your queries
4. Accessing the API endpoints
5. Next steps

​

Prerequisites

Before you begin, make sure you followed the installation guide to install Latitude.

​

1. Create a new data app

To create a new data app, run the following command in your terminal:

latitude start

After the app is created, navigate to the app’s directory and start the development server by running the following commands:

cd my-app
latitude dev

This will start the development server and open your app in your default browser. Since we’re not interested in building a UI for this guide, you can close the browser tab and delete the views directory from your project.

​

2. Add a data source

To add a data source to your app, you’ll need to create a new file in the sources directory.

For this guide, we’ll use the CSV that comes with the sample project, but you can replace that with any other CSV or connect to a live database following these instructions.

Go ahead and download the sample CSV and save it in the queries directory as source.csv.

​

3. Write your queries

Now that you have a data source, you can write your first query. Create a new file named sample.sql in the queries directory and write the following query:

select *
from read_csv_auto('queries/source.csv')

This query will select all the rows from the source.csv file using our default DuckDB adapter (you don’t need to configure anything else to get this working).

Next, let’s create a secondary query to aggregate some data. Create a new file named sample_agg.sql in the queries directory and write the following query:

select
  release_year,
  count(*) AS total_titles,
from { ref('sample') }
group by 1
order by 1 asc

This query will count the number of titles released each year. The ref function is used to interpolate the sample.sql query, and use its results as the input for this query. Latitude allows you to chain queries together very easily, find out more about this in the queries reference.

To check that your queries are working as expected, you can run them locally using the latitude run command. For example:

latitude run sample_agg

This will run the /queries/sample_agg.sql query once and display the results in your terminal.

Learn more about latitude run in the CLI reference.

​

4. Accessing the API endpoints

Now that you have your queries set up, you can access the API endpoints. The Latitude automatically creates an API for you based on the file structure you have defined in your project.

To access the API, you can use any HTTP client, such as curl, Postman, or even your browser. For example, to access the /api/queries/sample_agg endpoint, you can run the following command in your terminal:

curl http://localhost:3000/api/queries/sample_agg

This will return the results of the sample_agg.sql query in JSON format.

​

4.1 Passing parameters to your queries

You can also pass parameters to your queries by adding them to the URL. Let’s edit our sample_agg.sql file to support a year parameter. Replace the contents of the file with the following and save it:

select
  release_year,
  count(*) as total_titles,
from { ref('sample') }
{#if param('year')}
    where release_year = {param('year')}
{/if}
group by 1
order by 1 asc

Now, you can pass the year parameter to the /api/queries/sample_agg endpoint. For example, to get the results for the year 2020, you can run the following command in your terminal:

curl http://localhost:3000/api/queries/sample_agg?year=2020

This will return the results of the sample_agg.sql query for the year 2020 in JSON format.

​

4.2 Downloading query results as CSV

You can pass a special __download boolean parameter to the URL to download the results as a CSV file. For example, to download the results of the sample_agg.sql query as a CSV file, you can run the following command in your terminal:

curl http://localhost:3000/api/queries/sample_agg?year=2020&__download=true --output sample_agg.csv

Calling the same url from a browser will automatically triggera a download.

​

5. Next steps

Congratulations! You have successfully created an API to your data with the Latitude. You can now use this API to access your data from any other app or service.