How to use cartodb.js without storing sql in javascript

Question

Using cartodb.js, how can I change the layer definition without building the sql dynamically in javascript and passing it as a parameter. Aside from revealing my table structure (not a huge deal at the moment, but it might be later on), it seems like a bit of a maintenance headache.

mylayer.set({
 sql: "select * from mytable WHERE somefield = 'x'"
});

I don't see (but could be missing) options within CartoDb itself. Another option is I could set up a separate web server where I would send a request (e.g. someotherserver.com/request?somefield=x) which could then generate sql and then talk to CartoDB and return the results. The problem with that approach is that calling layer.set(...) provides a good deal of additional functionality that I'm not sure my separate web server would be able to provide.

Does anyone have a good solution to this problem?

Answer 1

If you want to have different visualisations but only modifying your query params, for instance because a filter changed and you want to change your where from WHERE somefield = 'x' to WHERE somefield = 'y', probably you want to have a look at the named maps + templates functionality provided by the Maps API. That allows you to create visualisations without exposing your queries/table structure but also allows you to change the queries dynamically.

First thing to do is create a named map with your API key, if you have a template.json:

{
 "version": "0.0.1",
 "name": "world_borders",
 "auth": {
 "method": "open"
 },
 "placeholders": {
 "color": {
 "type": "css_color",
 "default": "#FF6600"
 }
 },
 "layergroup": {
 "version": "1.0.1",
 "layers": [
 {
 "type": "cartodb",
 "options": {
 "cartocss_version": "2.3.0",
 "cartocss": "#layer { polygon-fill: <%= color %>; polygon-opacity: 1; line-color: #FFF; line-width: 1; line-opacity: 1; }",
 "sql": "select * from world_borders"
 }
 }
 ]
 }
}

The important part in the template is the "placeholders" and the <%= color %> part of the "cartocss", that is what allows you to change colors later on. You can do the same with the query, check the documentation link I provided before.

You can do a POST request to the Maps API like in:

curl -X POST \
 -H "Content-Type: application/json" \
 -d @template.json \
 "https://${CDB_USERNAME}.cartodb.com/api/v1/map/named?api_key=${CDB_API_KEY}"

That will give you the name of your template, then you can use that name with cartodb.js to instantiate the layer and add it to your map:

cartodb.createLayer(map, {
 user_name: '${CDB_USERNAME}',
 type: 'namedmap',
 options: {
 named_map: {
 name: '${CDB_USERNAME}@world_borders',
 params: {
 "color": "#5CA2D1"
 }
 }
 }
})
.addTo(map)

As you can see using the type "namedmap" you just have to use the name of the template/named map and you don't expose the queries in the JavaScript, not even the table name neither the structure.

Later on you can modify the color param using the layer object like:

.done(function(layer) {
 // later on we change the color
 setTimeout(function() {
 layer.setParams({
 color: '#FF6600'
 });
 }, 5000);
});

You can check a working example in the following bl.ock.org: http://bl.ocks.org/rochoa/656f1eee8b3adddf3756

Answer 2

I use squel.js to create SQL queries in javascript. It's pretty straightforward + lightweight to boot:

http://hiddentao.github.io/squel/