howto

Running SQLite in the browser using NextJS

Why not?

tags
sqlite
browser
nextjs

Contents

End API

Our goal is to be able to load a sqlite3 database using useBinaryFile, load up the database engine using useDB, and get the results using useDBQuery.

1
2
3
4
5
6

  const data = useBinaryFile( sqlliteURL )
  const db = useDB(data);
  const [query, setQuery] = useState( "SELECT name FROM  sqlite_schema WHERE type ='table' AND name NOT LIKE 'sqlite_%';" )
  const results = useDBQuery( db, data, query )

  return <p>You have {results.size} rows</p>

Lets walk though how to make that happen.

Install sql.js

First we install the wasm files, and we serve them out of /public.

1
2
3

  npm install sql.js
  cp node_modules/sql.js/dist/sql-wasm.js public
  cp node_modules/sql.js/dist/sql-wasm.wasm public

/public/sql-loader.js

Now, outside of the NextJS managed javascript universe create a file that will add the script tag to load up sql-wasm.js and sql-wasm.wasm into the browser.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

  console.log( "Adding sql-wasm.js script tag")
  const s = document.createElement( 'script' );
  s.setAttribute( 'src', '/sql-wasm.js' );
  document.body.appendChild( s );

  window.loadSQL = async () => {
      console.log( "loadSQL function called" )

      return await initSqlJs({
          locateFile: file => `/${file}`
        })
  }

lib/useDB.tsx

Now we create a custom react hook to use load up the database.

The first useEffect runs once, and looks to see if there's a window object. If there is, it's running on the browser, so it sets up an interval to see if the loadSQL function has been defined. If it has, this means that the sql-loader.js script has been evaluated. Once's that's true, will call setWindowWatcher which will cause the second useEffect to rerun.

The second useEffect called the function setup by sql-loader.js, which loads the SQLite database engine. Once this is done ie called setEngine.

Once the engine and data are set, the third useEffect is run, which actually instantiates a new SQL.Database (called engine.Database in the code.)

At this point, the db is set, so we can use useDBQuery to get the results. We pass in db (which will change if we switch to a new database file) and a query, and it returns the result.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

  import { useEffect, useState } from "react"

  export function useDB(data) {
      const [engine, setEngine] = useState(null)
      const [db, setDB] = useState(null)
      const [windowWatcher,setWindowWatcher] = useState(false);

      useEffect( () => {
          if( window ) {
              console.log("Running in a browser, checking for loadSQL" )
        
              const timer = setInterval( () => {
                  console.log( "Polling..." );

                  // @ts-ignore
                  if( window.loadSQL ) {
                      console.log("Clearing timer")
                      clearInterval( timer );
                      setWindowWatcher(true)
                  }
              }, 500)
          }
      }, [])

      useEffect( () => {
          console.log( "Looking for loadSQL")
          // @ts-ignore
          if( window.loadSQL ) {
              console.log( "Should try initSQLJS")
              // @ts-ignore
              window.loadSQL().then( (db) => {
                  console.log( "I have the database" )
                  setEngine( db )
              })
          }
          return () => {}
      }, [windowWatcher] )
    
      useEffect( () => {
          if( engine && data ) {
              console.log( "Starting up the engine")

              // @ts-ignore
              setDB( new engine.Database(new Uint8Array(data) ))
          }

          return () => {}
      }, [data,engine] )

      return db
  }

  export function useDBQuery( db, query ) {
      const [results, setResults] = useState(null)

      useEffect( () => {
          if( db ) {
              console.log( `Running query ${query}`)
              const r = db.exec(query)
              console.log(r)
              // @ts-ignore
              window.results = r;
              setResults( r )
          }
      }, [db, query])

      return results;
  }

Loading up a file

An easy way to load a binary file is:

useBinaryFile.tsx

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

  import { useEffect, useState } from "react";

  export default function useBinaryFile( url ) {
      const [dataFile, setDataFile] = useState(null)

      useEffect( () => {
          console.log( `Loading ${url}`)

          fetch(
              url
          ).then( (res) => {
              res.arrayBuffer().then( (data) => setDataFile( data ))
          })

          return () => { console.log( "Unmounted binary file") }
      }, [url]);

      return dataFile
  }

Tying it all together

Now to trigger the loading of sql-loader.js you just need to put a script tag in the pages that you use useDB on:

1

    <Script type="module" strategy='beforeInteractive' src="/sql-loader.js"/>

If you don't put this tag in, window.loadSQL will never be define, and setWindowWatcher will never be true.

Generic ResultsTable

Here's a little starting point to show the results:

  export function ResultTable( {results} ) {
      if( !results ) {
          return <div></div>
      }
      return (
          <table className="w-full">
              <thead>
                  <tr>
                      {results[0].columns.map( (c) => <th key={c}>{c}</th>)}
                  </tr>
              </thead>
              <tbody>
                  {results[0].values.map( (r) => <tr key={r}>
                      {r.map( (v) => <td key={v}>{v}</td> )}
                  </tr>)}
              </tbody>
          </table>
      )
  }

Previously

howto

Setting up digital ocean spaces to upload

Gotta work on my upload scrips

tags
aws
s3
ruby
node
golang
deno
digitalocean

Next

howto

Pulling avatars from slack

Basic bot integration

tags
slack
nextjs
bot