Coding · ·

PostgreSQL with Supabase

SQLite reads and writes to a local file. But because changes to files on your server are wiped each time you git push, if you write to your SQLite file you'll lose those change.

PostgreSQL uses a client-server architecture. PostgreSQL clients send SQL queries to PostgreSQL servers and receive results in reply.

We'll be setting up a PostgreSQL database server with Supabase and writing clients in Bun to send it SQL queries, either from your live server or from programs running on your computer. Perhaps confusingly, PostgreSQL clients will be in your server-side not client-side code.

Quick start

By the end of this quick start you'll be sending SQL queries to a PostgreSQL server with Supabase and receiving results in reply.

This assumes you created a Supabase account when you followed the S3 cloud files guide and that you're in a folder with a populated .env file created from that guide.

• log in to Supabase ('Continue with GitHub')
• navigate to your project's page
• click 'Connect' in the middle of the top header
• select 'Session pooler' from the 'Method' drop-down
• copy the 'Session pooler' 'Connection string' to your clipboard
• in your .env file, add a new line with your connection string:
  • POSTGRES_URL="postgresql://...your_connection_string...
• click 'Project settings' from the bottom of the left menu
• click 'Database' under 'Configuration' on the left
• click 'Reset database password'
• 'Generate a password', 'Copy' it to your clipboard and 'Reset password'
• back in your .env file, in the POSTGRES_URL replace [YOUR_PASSWORD] (inc the [brackets]) with the password saved in your clipboard
• the line in your .env will now look something like this (but with your details):
  • postgresql://postgres.xqmebjwhdfzrtolcgnpv:Qm9aT0xwZlF2Ujhs@aws-1-eu-west-1.pooler.supabase.com:5432/postgres

Create a new file, eg postgresql-test.js which should have the following:

console.table(await Bun.sql`SELECT 'hello world'`)

Run the file with Bun and you should see:

┌───┬─────────────┐
│   │ ?column?    │
├───┼─────────────┤
│ 0 │ hello world │
└───┴─────────────┘

... which means it has successfully sent an SQL query to Supabase's PostgreSQL server and receives results in reply.

If it didn't work and you instead see this error:

error: The server did not return the correct signature
 code: "SASL_SIGNATURE_MISMATCH"

... it's probably because you put the password in your .env incorrectly. Change it to a new one instead (via instructions earlier above).

If you instead get this error:

PostgresError: Connection closed
 code: "ERR_POSTGRES_CONNECTION_CLOSED"

... you likely used the default 'Direct connection' as the method rather than 'Session pooler' when you copied your 'Connection string' earlier.

With these changes made, using PostgreSQL with Supabase will be largely the same as using SQLite locally.

Running SQL queries from your website

Your website's client-side code (ie the HTTP client, such as a web browser) must not send queries directly to your PostgreSQL database server, as doing so would mean your client-side code would need your database password, which it absolutely should not have for security reasons. Instead, client-side code will do an HTTP request to your Bun HTTP server, which in turn will send an SQL query to your PostgreSQL server. The database server will reply to your server with results, which you can then send back to the browser via an HTTP response:

                 HTTP REQUEST──▶                      SQL QUERY──▶
HTTP client                          HTTP server                          PostgreSQL server
                 ◀──HTTP RESPONSE                    ◀──SQL RESULTS

Let's make a simple example server to do this:

import { sql } from 'bun'

let server = Bun.serve({
  routes: {
    '/hello': async () => {
      let data = await sql`SELECT 'hello world'`
      console.log(data)
      return Response.json(data)
    }
  }
})

console.log(`Server listening at ${server.url}`)

Run your file with eg bun --hot server.js, go to http://localhost:3000/hello and you should see the result of the SQL query ([{"?column?":"hello world"}]).

Note that instead of using await Bun.sql`SELECT 'hello world'` I've done import { sql } from 'bun' at the top of the file so we can just await sql`SELECT 'hello world'` instead, which is how we used sql in the SQLite guide.

When we used SQLite we had eg process.env.DATABASE_URL = ':memory:' or process.env.DATABASE_URL = `sqlite://${file}` . Instead, we're now using our .env file to specify the DATABASE_URL, and the reason Bun knows its PostgreSQL rather than SQLite is because the value of DATABASE_URL starts with postgresql:// instead of sqlite:// or :memory:.

Creating a simple blog

We're going to create a database table to store blog items:

• when inside your project on the Supabase dashboard
• go to 'Table editor'
• 'Create a new table' and call it blog:
  • under columns, leave the id column as-is but rename created_at to createdAt
  • make a new column title with type 'text'
  • make a new column body with type 'text'
  • press 'Save' to create the table
• with the table created, 'Insert' to 'Insert row'
  • leave the id and createdAt is-is (they'll be populated automatically)
  • put a title like 'First post' with something in the body
  • 'Save' to create the first post
  • optionally, insert some more rows

Back in your server's .js file, add a route within the routes object passed to Bun.serve() for getting all posts:

// get all blog items
'/api/blog': async () => {
  let data = await sql`SELECT * FROM blog`
  return Response.json(data)
}

Then (with your server running with --hot, or restarted if not) go to /api/blog and you should see a JSON representation of the blog item you just created. I chose to prefix the URL with /api to make it more clear that the response is JSON rather than HTML.

Creating new blog posts

We're going to crete a web page with a form which 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 add.html:

<!doctype html>
<title>New blog item</title>
<style>form * { display: block; margin: 5px 0; }</style>
<form method="post" action="/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 /blog:

'/blog': {
  POST: async (req) => {
    let data = await req.formData()
    await sql`INSERT INTO blog (title, body) VALUES (${data.get('title')}, ${data.get('body')})`
    return Response.redirect('/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 /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.

Showing the blog items on a page

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:

• the data from the blog items is combined with HTML on the server and sent to the client together
• the user doesn't have to use JavaScript to see the page
• because the blog items are in the HTML, search engines can see and index them
• there's a greater risk that you're vulnerable to XSS attacks if you're not careful
• this is how all web pages used to be created until ~15 years ago

Client-side rendering:

• your server acts as a static file server and a JSON API
• HTML responses from the server are static and don't include blog items
• client-side JavaScript in the HTML makes requests to your API
• your API responds with JSON, which the browser then processes client-side by modifying DOM elements
• you could have a more dynamic page, for example with new blog items appearing without refreshing
• you can use el.innerText instead of el.innerHTML to protect against XSS attacks
• ... or you can use a library like Vue or React which can simplify things

We're going to opt for the client-side rendering option.

We'll user 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 items.

We're going to create a simple HTML page now that uses that same code, then adds the blog items to the DOM. You'll probably put this file in your ./public folder. Call it blog.html or similar:

<!doctype html>
<title>Blog</title>

<h1>Blog</h1>
<button onclick="populateBlog()">View blog</button>
<div id="blogItems"></div>

<template id="itemTemplate">
  <div>
    <h2>{{ title }}</h2>
    <p>{{ body }}</p>
  </div>
</template>

<script>
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. If we were to try this unsafe code instead:

<!doctype html>
<title>Blog</title>
<h1>Unsafe blog</h1>
<div id="blogItems"></div>
<script>
populateBlog()

async function populateBlog () {
  let items = await (await fetch('/api/blog')).json()
  blogItems.innerHTML = items.map(item => (`
    <div>
      <h2>${item.title}</h2>
      <p>${item.body}</p>
    </div>
  `)).join('')
}
</script>

... then we're vulnerable to XSS attacks.

A better idea is 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>

Next steps

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:

• leaderboard for a game
• discussion forum or guestbook
• todo app
• track how many visits you get
• personal notes manager
• poll/voting app
• bookmark manager
• book review manager
• some kind of quiz app
• product catalog
• a (mock) e-commerce shop with basket and checkout

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.

Edit this page · © Mr Jake Gordon, Cambridge Maths School, UK