sqlitegithubgithub-pages

Is it possible to use github to host a web application that supports sqlite database?


Github supports static webpages. Sqlite database is a binary file. Can a static webpage with javascript access make queries on the sqlite database in the same folder?

Many web development projects require a database. This is for demonstration purposes only and does not need to support many users.


Solution

  • Yes, but read-only. sql.js is a Javascript SQLite library which compiles the SQLite C library into Webassembly.

    Here's an example of loading an existing SQLite database from a URL and querying it.

    Using Fetch API:

    // Example DB: https://github.com/lerocha/chinook-database/raw/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite
    const response = await fetch("/path/to/database.sqlite")
    const arrayBuffer = await response.arrayBuffer();
    const SQL = await initSqlJs();
    const db = new SQL.Database(new Uint8Array(arrayBuffer));
    const contents = db.exec("SELECT * FROM my_table");
    // contents is now [{columns:['col1','col2',...], values:[[first row], [second row], ...]}]
    

    Using XMLHttpRequest:

    const xhr = new XMLHttpRequest();
    // For example: https://github.com/lerocha/chinook-database/raw/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite
    xhr.open('GET', '/path/to/database.sqlite', true);
    xhr.responseType = 'arraybuffer';
    
    xhr.onload = e => {
      const uInt8Array = new Uint8Array(this.response);
      const db = new SQL.Database(uInt8Array);
      const contents = db.exec("SELECT * FROM my_table");
      // contents is now [{columns:['col1','col2',...], values:[[first row], [second row], ...]}]
    };
    xhr.send();