SQLite reads and writes to a normal file on disk. Files on your server are wiped every time you git push except for files in the data/ folder. If you want to put an SQLite database into production (on your compsci.me website) where your server needs to edit the database, you'll need to make sure your SQLite .db file in in the data/ folder.
Alternatively, you may want to consider using a client-server architecture for your database, where your server-side JavaScript is the database client that connects to a database server. That's a more advanced approach, covered in an optional guide to using PostgreSQL.
Your website's client-side code (ie the HTTP client, such as a web browser) won't run SQL queries directly, largely for security reasons. Instead, client-side code will make an HTTP request to your Bun HTTP server, which in turn will run an SQL query against your SQLite database which is stored on the server. The result of that query can then be sent back to the browser via an HTTP response.
HTTP REQUEST──▶ SQL QUERY──▶
HTTP client HTTP server SQLite
◀──HTTP RESPONSE ◀──SQL RESULTS
We're going to create a simple one-table database to store blog items:
db.blog
-------
id
createdAt
title
body
At the top of your main server.js (or elsewhere if you don't want this on your main website), add the following which connects to your SQLite database and creaates this table if it doesn't exist yet:
import { sql } from 'bun'
process.env.DATABASE_URL = `sqlite://data/database.db` // must be in data/ to persist git pushes
await sql`
CREATE TABLE IF NOT EXISTS blog (
id INTEGER PRIMARY KEY AUTOINCREMENT,
createdAt TEXT DEFAULT CURRENT_TIMESTAMP,
title TEXT NOT NULL,
body TEXT NOT NULL
)
`
Then add a route within the routes object passed to Bun.serve() for getting all posts:
// get all blog items
'/api/blog': {
GET: async () => {
let data = await sql`SELECT * FROM blog`
return Response.json(data)
},
},
With your server running with --hot (or restarted if not) go to /api/blog and you should see a JSON representation of the items in your blog... which is currently just an empty [] array! I chose to prefix the URL with /api to make it more clear that the response is JSON rather than HTML.
We're going to create a web page with a form that POSTs to our server, which will have a route handler which then creates a blog item in our database table.
In an HTML file eg public/blog/add.html:
<!doctype html>
<title>New blog item</title>
<style>form * { display: block; margin: 5px 0; }</style>
<form method="post" action="/api/blog">
<label>Title: <input name="title"></label>
<label>Body: <textarea id="body" name="body" rows="10"></textarea></label>
<button>Add</button>
</form>
Then we need to handle POST requests that come from that form to /api/blog. We'll do that by adding a POST handler alongside the GET handler we added in the last step:
'/api/blog': {
GET: async () => {
let data = await sql`SELECT * FROM blog`
return Response.json(data)
},
POST: async (req) => {
let data = await req.formData()
await sql`INSERT INTO blog (title, body) VALUES (${data.get('title')}, ${data.get('body')})`
console.log(`Added blog item "${data.get('title')}"`)
return Response.redirect('/api/blog')
}
},
You may be worried about SQL injection attacks here, but Bun's SQL driver actually handles those for you (just be sure to use the weird syntax with backticks instead of brackets).
Try creating a blog item by going to /blog/add.html and filling in and submitting the form. It should redirect you to the list of JSON blog items, with your new item there.
So far you're just showing your blog items as JSON. Now we're going to create a proper page to show them instead. For this, we've got 2 main options, both with their advantages and disadvantages:
Server-side rendering:
Client-side rendering:
el.innerText instead of el.innerHTML to protect against XSS attacksWe're going to opt for the client-side rendering option.
We'll use a fetch() request from the browser to get all the posts as JSON, then use client-side JavaScript to add them to DOM elements.
We already have an API endpoint that gives us the JSON we need - /api/blog.
With the server running, open http://localhost:3000 in your browser, then in the developer tools console execute this line:
await (await fetch('/api/blog')).json()
... and you'll see an array of blog objects, which you can expand to see the blog posts.
We're going to create a simple HTML page now that uses that same code, then adds the blog posts to the DOM. I suggest creating this file as ./public/blog/index.html:
<!doctype html>
<title>Blog</title>
<h1>Blog</h1>
<div id="blogItems"></div>
<template id="itemTemplate">
<div>
<h2>{{ title }}</h2>
<p>{{ body }}</p>
</div>
</template>
<script>
populateBlog()
async function populateBlog () {
let items = await (await fetch('/api/blog')).json()
items.forEach(item => blogItems.append(renderTemplate(item, '#itemTemplate')))
}
function renderTemplate (props, selector) {
let template = document.createElement('div')
template.innerHTML = document.querySelector(selector).innerHTML.replace(/{{ *([\w]*) *}}/g, '<i j-prop="$1"></i>')
template.querySelectorAll('[j-prop]').forEach(el => {
let key = el.getAttribute('j-prop')
el.replaceWith(document.createTextNode(key in props ? props[key] : ''))
})
return template
}
</script>
I would love to give you shorter code than that, but unfortunately we need that renderTemplate() function to prevent against XSS attacks.
Open http://localhost:3000/blog and you should see all your blog items rendered as HTML.
Now that you have an actual page to show the blog items, you should probably redirect to here after adding new blog posts rather than to /api/blog: just change the redirect in the POST handler to be return Response.redirect('/blog').
An optional alternative to the code in the previous step would be to use a library/framework such as petite-vue (also see my guides here and here) which will deal with XSS for us, as well as adding lots of other useful features too. This cleans up our client-side code considerably:
<!doctype html>
<title>Blog</title>
<script src="https://unpkg.com/petite-vue"></script>
<h1>Blog</h1>
<div v-scope id="blogItems">
<div v-for="item in state.items">
<h2>{{ item.title }}</h2>
<p>{{ item.body }}</p>
</div>
</div>
<script>
let state = PetiteVue.reactive({ items: [] })
PetiteVue.createApp().mount()
populateBlog()
async function populateBlog () {
state.items = await (await fetch('/api/blog')).json()
}
</script>
Currently anyone who knows that blog posts can be posted at /blog/add.html could add a blog post to your page. The simplest way to fix this is with a secret code that only you know that is checked server-side when someone tries to add a blog post.
<label>Secret code: <input name="code" type="password"></label> inside the form on the HTML page where you create blog postsif (data.get('code') !== 'my_secret_code') return new Response('wrong secret code') just before your INSERT query in the POST handler for /api/blog, ensuring to replace 'my_secret_code' with your actual secret codeNow when you fill in the form to add a new blog post you'll need to put the correct code in for the blog post to be saved.
With this simple security in place, it's time to try putting your blog live on your website! Note that any blog posts made locally won't be replicated on the live website - they each use their own SQLite database file.
Every time you git push, the old container running your site gets killed and a new one started. Usually that's instant and you don't even notice. But if your site happened to be writing to SQLite at exactly that moment, you'd want to be sure the database doesn't end up half-written. This
Fortunately, SQLite has a mode called WAL (write-ahead logging) that handles this gracefully - any partial write is rolled back when the database is next opened. Turn it on once, near the top of server.js (under the process.env.DATABASE_URL line):
await sql`PRAGMA journal_mode = WAL`
You'll notice it creates two extra files alongside site.db - site.db-wal and site.db-shm. Don't worry about them, they live in data/ like everything else and persist too.
This write-ahead logging mode helps your SQLite database comply with 3 of the ACID principles:
Now that you have a persistent database and the ability to use it on your website, it's up to you to figure out what you want to do with it! Here are some more ideas:
If you want to keep track of things for a particular user, you'll want to think about adding a user signup and login system, and you'll want to use session cookies for that - ask me and I can tell you more.