Code Editor
Learn Node SQLite

Using Placeholders

Now we know how to retrieve data from a database when we know exactly what we're looking for. But we may not always know what values we will need to search for when writing our program. When we write a JavaScript function, we give the function parameters that will have many different values when the function gets called. Placeholders solve a similar problem in the world of SQL queries. Sometimes we'll want to search our database based on a user's submission. Or we might find ourselves wanting to perform a series of queries looping over some external data.

In those cases, we will have to use a placeholder. A placeholder is a part of our SQL query that we want to be interpolated with a variable's contents. We want the value of the JavaScript variable to be placed within the SQL query. To do this properly, we'll need to pass a particular argument to our command that will tell it how to interpolate the query.

const furLength1 = "short"; const furLength2 = "long"; const furColor1 = "brown"; const furColor2 = "grey"; const findDogByFur = (length, color) => { db.all( "SELECT * FROM Dog WHERE fur_length = $furLength AND fur_color = $furColor", { $furLength: length, $furColor: color }, (error, rows) => { printQueryResults(rows); } }); findDogByFur(furLength1, furColor1); // prints all dogs with short brown fur. findDogByFur(furLength2, furColor1); // prints all dogs with long brown fur. findDogByFur(furLength1, furColor2); // prints all dogs with short grey fur. findDogByFur(furLength2, furColor2); // prints all dogs with long grey fur

As we can see in the example above, the power of placeholders is that we don't need to know precisely the data we're searching for at the time of writing our query. We can use these placeholders and then later, when we have values we want to find, we can plug them into the query. This is a highly effective tool that will allow us to harness our programming skills within our database queries.

Report a Bug
If you see a bug or any other issue with this page, please report it here.