Now run the server (use the command node index.js in your terminal, or click Run if using replit.com). If all goes well, you should see the following messages displayed in the terminal confirming that the server has started, the app is connected to the database, and data has been successfully added to the database:

Server started
Connected to the database.
Table created successfully
A row has been inserted with rowid 1

If you receive an error message instead, check your code to ensure the code has been written correctly and that the data being added to the table is formatted properly. Also, check that the number of question marks in the VALUES() section of the code matches the number of values provided for each column.

Tip: Install the SQLite extension in VS Code

The SQLite extension for Visual Studio Code provides a helpful tool, called the SQLite Explorer, that allows you to view all the data in your database tables within Visual Studio Code. In VS Code, click on the Extensions icon and search for SQLite. Install the extension.

Once you have installed the SQLite extension, go back to your code. At the top of the VS Code window, click on the command palette and then click ‘Show and Run Commands’. Then select SQLite: Open Database. Then select mydb.db (or the name of your database). In the bottom left corner of the screen, you should notice the ‘SQLITE EXPLORER’ panel appear. Click this, then select your database (eg. mydb.db), select the table you wish to view, and then click the > icon next to the table name to view the existing records in the table.

You can use the SQLite extension’s tool (SQLITE EXPLORER) to check what data is currently in the database, as shown below.

Step 3 - Retrieve all movies from the database

Now that we know that we can successfully add data to the database, we can remove the test block of code that was used to manually add a movie to the database. We will use this code again later and modify it so that we can collect information about movies from users through a form and then add the movie information to the database. Now we can test out retrieving records from the database using code. The following code can be added to retrieve all movies from the database and display some basic details about them (eg. their ID, title, director, and genre).

Add the following block of code just before the app.get() block. When we run the server, this block of code will select all movies from the database, and in this example, display the results in the terminal. Note: SELECT * means “select all”. In this query, we specify the table we want to fetch records from. We can add more criteria to queries, which we will look at later.

// Query data
db.all('SELECT * FROM movies', [], (err, rows) => {
if (err) {
console.error(err.message);
}
else{
rows.forEach(row => {
console.log(row.movie_id, row.title, row.director, row.genre);
});
}
});

Start the server again, and you should see the data retrieved from the database displayed in the terminal.

Step 4 - Retrieve specific data from the database

You can modify the SELECT query by specifying search criteria. For example, you may wish to retrieve only movies released in a particular year, or from a particular director, or a particular genre. In the example below, we are modifying the SELECT query to search for all movies that match the genre “Sci-Fi” (note: you might want to add some more movies to your database first to see the effect of this)

// Query data
db.all('SELECT * FROM movies WHERE genre = ?', ['Sci-Fi'], (err, rows) => {
if (err) {
console.error(err.message);
}
else{
rows.forEach(row => {
console.log(row.movie_id, row.title, row.director, row.genre);
});
}
});

Start the server again, and you should retrieve only Sci-Fi movies from the database.

You can also add more criteria like this (eg. now we are searching for Animation movies released in 1995:

// Query data
db.all('SELECT * FROM movies WHERE genre = ? AND release_year = ?', ['Animation', '1995'], (err, rows) => {
if (err) {
console.error(err.message);
}
else{
rows.forEach(row => {
console.log(row.movie_id, row.title, row.director, row.genre);
});
}
});

Here is the full sample code:

We are now ready to start building the app so that users can view and add movies. In the next lesson, we will create the home screen for the app that will display a list of movies from the database.

Next lesson: Create a Movie Reviews app (Part 4 - Create the home page)