Coding · ·

SQL app design

In the SQLite guide you learnt how to create an SQLite database, connect to it from Bun JavaScript and query a sample database with SELECT queries.

In today's lesson you'll be creating your own database instead, and building a simple app which connects to it.

Get started by creating a new folder and navigating into it in your terminal.

Designing a simple database

Specifically, we're going to create a simple 2-table database which keeps track of quotes made by people. Our database will have 2 tables: one to store details of the person making the quote (db.person), and the other with the details of the quote (db.quote). Each person can have many quotes, but each quote is only by one person, so there's a one-to-many relationship between db.person and db.quote.

As it's a relational database, each table needs a primary key, which by convention I always just call id. So we have db.person.id and db.quote.id (some people prefer db.person.personId etc).

To model the one-to-many relationship between db.person and db.quote, the primary key for db.person needs to be stored as a foreign key in db.quote. So far we have this:

db.person
---------
  id

db.quote
--------
  id
  personId

In addition, we want to at least store the name of the person who made the quote, and the words of the quote. Later, you may choose to add other details such as the date/time of the quote and maybe give the quotes different ratings based on how popular/impressive they are.

db.person
---------
  id
  name

db.quote
--------
  id
  personId
  words

Creating the database from a schema

To create tables in a database we need to run a create CREATE... query. As well as the names of the fields, SQLite also needs to know which field is the primary key, and also what type of data (eg text vs integer) is being stored in each field. Collectively, this is all known as the database schema.

In your new folder, create a file called create.js and put the following inside:

import { sql } from 'bun'

let file = './database.db'
process.env.DATABASE_URL = `sqlite://${file}`
await Bun.write(file, '') // clear existing db

// the database schema
let tables = [
  `CREATE TABLE person (
    id INTEGER PRIMARY KEY,
    name TEXT
  )`,
  `CREATE TABLE quote (
    id INTEGER PRIMARY KEY,
    personId INTEGER,
    words TEXT
  )`
]

// create tables from the schema
for (let table of tables) await sql.unsafe(table)

Run the file with bun create.js and if you run ls you should see a new file called database.db.

Adding rows to your database

You can add rows to a database table with INSERT ... queries.

Let's try it by adding a person to the database, in a file insert.js:

import { sql } from 'bun'

process.env.DATABASE_URL = 'sqlite://database.db'

let teachers = ['Mr Gordon', 'Mr Banham', 'Ms Berry']
for (let teacher of teachers) {
  await sql`INSERT INTO person (name) VALUES (${teacher})`
}

console.table(await sql`SELECT * FROM person`)

Run bun insert.js and you should see the following:

┌───┬────┬───────────┐
│   │ id │ name      │
├───┼────┼───────────┤
│ 0 │ 1  │ Mr Gordon │
│ 1 │ 2  │ Mr Banham │
│ 2 │ 3  │ Ms Berry  │
└───┴────┴───────────┘

But run it again and you'll see:

┌───┬────┬───────────┐
│   │ id │ name      │
├───┼────┼───────────┤
│ 0 │ 1  │ Mr Gordon │
│ 1 │ 2  │ Mr Banham │
│ 2 │ 3  │ Ms Berry  │
│ 3 │ 4  │ Mr Gordon │
│ 4 │ 5  │ Mr Banham │
│ 5 │ 6  │ Ms Berry  │
└───┴────┴───────────┘

... if you want to run it again, you need to first run create.js to reset the database. Do that now, then run insert.js just once after so you just have 3 people in the person table.

Adding quotes interactively

Now, we want to write a simple CLI program that asks the user who made a quote, then allows them to enter the words for that quote.

Make a new file add-quotes.js with the following:

import { sql } from 'bun'

process.env.DATABASE_URL = 'sqlite://database.db'

while (confirm('Add a new quote?')) {
  console.table(await sql`SELECT id, name FROM person`)
  let id = parseInt(prompt('Provide the id of the person who made the quote'))
  let words = prompt('What did they say?')
  await sql`INSERT INTO quote (personId, words) VALUES (${id}, ${words})`
  console.table(await sql`
    SELECT person.name, quote.words
    FROM quote
    JOIN person ON person.id = quote.personId
  `)
}

Hopefully the above script should be fairly simple to follow, but do ask if you have any questions about it.

Deleting quotes interactively

We can delete rows from a database with DELETE ... queries.

Make a new file delete-quotes.js with the following:

import { sql } from 'bun'

process.env.DATABASE_URL = 'sqlite://database.db'

await showQuotes()
while (confirm('Delete a quote?')) {
  let id = parseInt(prompt('Provide the id of the quote to delete'))
  await sql`DELETE FROM quote WHERE id = ${id}`
  await showQuotes()
}

async function showQuotes () {
  console.table(await sql`
    SELECT quote.id, person.name, quote.words
    FROM quote
    JOIN person ON person.id = quote.personId
  `)
}

Updating rows

You can use UPDATE ... queries to update rows, but I won't be covering that here.

Deleting tables

You can use DROP ... queries to delete whole tables, but I won't be covering that here.

Your turn

Here are some ways you can extend your program:

put multiple functions together into a single program, which prompts the user for which action to take
a way to interactively add teachers
a way to interactively edit teachers or quotes
a way to reassign a quote from one user to another
add a field to keep track of when a quote was added
have a popularity rating field for each quote, and a way to vote for a quote
follow the steps in the next section to turn it into a server-side website

Optional: Making it into a server-side website

There are extra considerations if you want to put your database online, rather than just locally on your computer. These are covered in this guide.