Avoid exposing SQL queries with Named Sources

Maintain a lightweight, modern and efficient architecture while avoiding the exposure of SQL queries in your application

Introduction

Modern data and geospatial applications must use a lightweight and efficient architecture in order to be successful. The way CARTO achieves this is by acting as the geospatial backend between your application and your data warehouse, reducing the need for further backend services and simplifying the architecture.

However, this also comes with a challenge: how will your application and CARTO communicate in a flexible and deterministic way to request the right data?

The most straightforward way that you'll find in many examples is by using SQL queries directly in the code and in the API requests — And while using SQL is fine and desirable for many applications, it may represent a security risk for some organizations, that would rather avoid uncovering their business logic by exposing the SQL queries used by the app.

In this guide you will learn how to use Named Sources to communicate from your application to CARTO, completely avoiding the exposure of SQL queries.

Regardless if you use SQL queries or Named Sources, CARTO is fully protected against SQL injection. This is achieved by using sanitized, native methods from the data warehouse, plus additional layers of protection in the CARTO APIs.

Preparing the application

First, you will need a working CARTO application that uses SQL queries, such as the query-accidents example. Let's start with that one:

git clone https://github.com/CartoDB/deck.gl-examples.git
cd deck.gl-examples/query-accidents
npm install
npm run dev

Once you do this, the application should be running in your browser. Notice how in index.ts:51 you can find SQL queries, that would be exposed to any user in the app.

We are going to use Named Sources to replace those SQL queries with references that CARTO will understand and translate backend-side. Feel free to create a copy of the application if you prefer having both side by side.

Preparing the Named Sources

Check the Named Sources documentation to learn how to create Named Sources. You will need to create two Named Sources:

Name	SQL Statement
accidents	SELECT * FROM carto-demo-data.demo_tables.riskanalysis_railroad_accidents WHERE year = @selectedYear
accidents_by_state	SELECT polygons.code_hasc as polygonid, any_value(polygons.geom) as geom, any_value(name) as state_name, count(points.year) count FROM carto-demo-data.demo_tables.usa_states_boundaries as polygons LEFT JOIN carto-demo-data.demo_tables.riskanalysis_railroad_accidents as points ON ST_INTERSECTS(polygons.geom, points.geom) AND year=@selectedYear GROUP BY polygons.code_hasc

Once you're done, you'll have two Named Sources like in this screenshot:

Adding the Named Sources to your token

Create a new API Access Token following the documentation, and make sure to add two grants for your two Named Sources, just like this screenshot:

Replace your SQL queries by Named Sources

Great! All the ingredients are there, let's connect the dots and your application will be using Named Sources:

1. First, replace the API Access Token in the .env file: use the token that you created in the previous step, containing the grants for your Named Sources

2. Modify your data sources in index.ts around line 48: here's where we are replacing our SQL queries by our Named Sources. It should now look something like:

function render() {
  const accidentsByState = vectorQuerySource({
    ...cartoConfig,
    sqlQuery: 'public_example_accidents_by_state',
    queryParameters: {selectedYear: selectedYear}
  });

  const accidents = vectorQuerySource({
    ...cartoConfig,
    sqlQuery: 'public_example_accidents',
    queryParameters: {selectedYear: selectedYear}
  });

  // ... continues with const layers = [

Run your application

🎉 That's it, congrats! If you now run the app:

npm run dev

You will see how the functionalities haven't changed at all, the map displays beautifully, and there's not a single SQL query being used in the application or exposed in the network requests.

• The final result for this guide can be found in the CARTO for Developers examples.

• Remember that you can manage Named Sources programmatically via API for large-scale applications.