Connecting with Javascript

Teachers

Why connect from JavaScript?

So far you have talked to SQLite through the terminal or DBeaver. In a real application your Node.js code needs to talk to the database directly — reading data to send to a user, or writing data when someone submits a form.

To do this we use a npm package that acts as a bridge between Node.js and the SQLite file on disk.

The `better-sqlite3` package

There are a few SQLite packages for Node. We will use better-sqlite3 because:

Its API is synchronous — no callbacks or Promises needed, which makes the code easier to read while you are still learning
It is fast and well-maintained
Error messages are clear and helpful

Setup

1. Create a project folder and initialise it

mkdir books-app
cd books-app
npm init -y

2. Install `better-sqlite3`

npm install better-sqlite3

3. Add ES modules to `package.json`

Open package.json and add "type": "module". It should look like this:

{
  "name": "books-app",
  "version": "1.0.0",
  "type": "module",
  "dependencies": {
    "better-sqlite3": "^9.x.x"
  }
}

This tells Node to treat all .js files in this project as ES modules, so you can use import and export syntax throughout.

<aside> ⚠️

Note — __dirname and __filename: ES modules don't have these CommonJS globals. They are not needed for the examples in this course, but if you ever need a file path relative to the current script rather than your working directory, you can recreate them:

import { fileURLToPath } from 'url';
import { dirname, join } from 'path';

const __dirname = dirname(fileURLToPath(import.meta.url));
const db = new Database(join(__dirname, 'books_library.db'));df

</aside>

4. Copy in your database file

Copy books_library.db into the books-app folder so it sits next to your JavaScript files.

Your project should look like this:

books-app/
├── books_library.db
├── index.js
├── package.json
└── node_modules/

Connecting to the database

Create index.js and add the following:

import Database from 'better-sqlite3';

// Open the database file.
// If the file doesn't exist, better-sqlite3 will create it.
const db = new Database('books_library.db');

console.log('Connected to the database.', db);

// Always close the connection when you are done.
db.close();

Run it:

node index.js

You should see Connected to the database. and some details about the database and its connection — that's it, you are in.

Reading data

better-sqlite3 gives you two main methods for SELECT queries:

.all() — returns every matching row as an array of objects
.get() — returns just the first matching row (or undefined if nothing matches)

Get all authors

import Database from 'better-sqlite3';
const db = new Database('books_library.db');

const authors = db.prepare('SELECT * FROM authors').all();
console.log(authors);
// [
//   { id: 1, first_name: 'J.K.', last_name: 'Rowling', nationality: 'British', birth_year: 1965 },
//   { id: 2, first_name: 'George R.R.', last_name: 'Martin', ... },
//   ...
// ]

db.close();

Each row comes back as a plain JavaScript object, so you can use it just like any other object:

authors.forEach(author => {
  console.log(`${author.first_name} ${author.last_name}`);
});

Get a single author by id

const author = db.prepare('SELECT * FROM authors WHERE id = 1').get();
console.log(author.first_name); // 'J.K.'

Using parameters (the safe way to filter)

You will often need to filter by a value that comes from user input — a search term, an id from a URL, etc. Never paste that value directly into the SQL string, because it opens you up to SQL injection attacks.

Instead, use a placeholder (?) in your query and pass the value separately:

// BAD — never do this with user input
const userId = 'Rowling'; // <-- value given by user, which is not what you expect. This is an innocent example and will just fail,
		// but it can be used for sql injection attacks
const books = db.prepare(`SELECT * FROM books WHERE author_id = ${userId}`).all();

// GOOD — use a placeholder
const stmt = db.prepare('SELECT * FROM books WHERE author_id = ?');
const books = stmt.all(1); // pass the value as an argument to .all()

You can also use named placeholders, which is clearer when you have several parameters:

const stmt = db.prepare(`
  SELECT b.title, b.published_year, a.first_name || ' ' || a.last_name AS author
  FROM books b
  JOIN authors a ON a.id = b.author_id
  WHERE a.last_name = @lastName
  ORDER BY b.published_year
`);

const books = stmt.all({ lastName: 'Tolkien' });
console.log(books);
// [
//   { title: 'The Hobbit', published_year: 1937, author: 'J.R.R. Tolkien' },
//   { title: 'The Fellowship of the Ring', published_year: 1954, author: 'J.R.R. Tolkien' },
//   ...
// ]

The @lastName syntax matches the key name in the object you pass. This is much easier to read than juggling multiple ? placeholders.

Inserting data

Use .run() for queries that don't return rows — INSERT, UPDATE, and DELETE.

.run() returns an info object with two useful properties:

info.lastInsertRowid — the id of the row that was just inserted
info.changes — how many rows were affected

const insertAuthor = db.prepare(`
  INSERT INTO authors (first_name, last_name, nationality, birth_year)
  VALUES (@firstName, @lastName, @nationality, @birthYear)
`);

const info = insertAuthor.run({
  firstName: 'Chimamanda',
  lastName:  'Ngozi Adichie',
  nationality: 'Nigerian',
  birthYear: 1977
});

console.log(`New author added with id: ${info.lastInsertRowid}`);
// New author added with id: 25

Now insert a book for that author:

const insertBook = db.prepare(`
  INSERT INTO books (title, published_year, genre, author_id)
  VALUES (@title, @year, @genre, @authorId)
`);

insertBook.run({
  title:    'Half of a Yellow Sun',
  year:     2006,
  genre:    'Historical Fiction',
  authorId: info.lastInsertRowid
});

<aside> ❗

Remember that if you execute these statements twice they will run. So if you run the insertAuthor twice you will get two entries for the same author. Same goes for the book insertion.
Normally, you would have a check to make sure that the item to be inserted is not already in the system, but that is an exercise for the user. 🙂

</aside>

Updating and deleting data

UPDATE

const updateGenre = db.prepare(`
  UPDATE books SET genre = @genre WHERE id = @id
`);

const result = updateGenre.run({ genre: 'Literary Fiction', id: 1 });
console.log(`${result.changes} row(s) updated`);

DELETE

const deleteBook = db.prepare('DELETE FROM books WHERE id = ?');
const result = deleteBook.run(99);
console.log(`${result.changes} row(s) deleted`);

<aside> ❗

Just like in raw SQL, always make sure your WHERE clause targets the right rows before you run an UPDATE or DELETE. (or all your data in that table will be affected.

</aside>

Putting it all together

Here is a small self-contained script that demonstrates all of the above:

import Database from 'better-sqlite3';
const db = new Database('books_library.db');

// 1. Count everything
const { authorCount } = db.prepare('SELECT COUNT(*) AS authorCount FROM authors').get();
const { bookCount }   = db.prepare('SELECT COUNT(*) AS bookCount FROM books').get();
console.log(`Database has ${authorCount} authors and ${bookCount} books.`);

// 2. Get all books by a specific author
const booksByAuthor = db.prepare(`
  SELECT b.title, b.published_year
  FROM books b
  JOIN authors a ON a.id = b.author_id
  WHERE a.last_name = @lastName
  ORDER BY b.published_year
`).all({ lastName: 'Pratchett' });

console.log('\\nTerry Pratchett books:');
booksByAuthor.forEach(b => console.log(`  ${b.published_year}  ${b.title}`));

// 3. Insert a new author and book
const newAuthorInfo = db.prepare(`
  INSERT INTO authors (first_name, last_name, nationality, birth_year)
  VALUES (@firstName, @lastName, @nationality, @birthYear)
`).run({ firstName: 'Chimamanda', lastName: 'Ngozi Adichie', nationality: 'Nigerian', birthYear: 1977 });

db.prepare(`
  INSERT INTO books (title, published_year, genre, author_id)
  VALUES (@title, @year, @genre, @authorId)
`).run({ title: 'Half of a Yellow Sun', year: 2006, genre: 'Historical Fiction', authorId: newAuthorInfo.lastInsertRowid });

console.log(`\\nInserted new author with id ${newAuthorInfo.lastInsertRowid}`);

db.close();

Common gotchas

db.prepare().all() vs .get() — .all() always returns an array (even if empty). .get() returns one object or undefined. Use .get() when you expect exactly one result (e.g. find by id), .all() for lists.
Never interpolate user input — always use ? or @name placeholders. This protects against SQL injection.

Why connect from JavaScript?

The better-sqlite3 package

Setup

1. Create a project folder and initialise it

2. Install better-sqlite3

3. Add ES modules to package.json

4. Copy in your database file

Connecting to the database

Reading data

Get all authors

Get a single author by id

Using parameters (the safe way to filter)

Inserting data

Updating and deleting data

UPDATE

DELETE

Putting it all together

Common gotchas

The `better-sqlite3` package

2. Install `better-sqlite3`

3. Add ES modules to `package.json`