How to connect to a sqlite db from a React app?

Question

I'm trying to build a page using Create React App, plus the sqlite3 module. In their default configurations, the two things don't seem to be compatible out of the box. I'm new to React and JS in general, so I'm hoping there's something obvious I'm missing here.

Using npx v6.9.0 and Node v12.4.0, I can reproduce this by typing npx create-react-app test, cd test, npm start.

So far so good. I type: npm install sqlite3, and receive an npm warning that I should install typescript. OK, I type npm install typescript. All good. I start up the app, and it compiles so far. Great!

I open up App.js, and per sqlite3's readme doc, under the import lines, I type var sqlite3 = require('sqlite3').verbose();

This is probably where I'm doing something wrong. My app now fails to compile, telling me:

./node_modules/sqlite3/node_modules/node-pre-gyp/lib/info.js
Module not found: Can't resolve 'aws-sdk' in '/Users/brendanlandis/Desktop/test/node_modules/sqlite3/node_modules/node-pre-gyp/lib'

I try npm install aws-sdk, which gets me a little farther. My app still won't compile, but now the error is:

TypeError: stream is undefined
./node_modules/set-blocking/index.js/</module.exports/<
node_modules/set-blocking/index.js:3

  1 | module.exports = function (blocking) {
  2 |   [process.stdout, process.stderr].forEach(function (stream) {
> 3 |     if (stream._handle && stream.isTTY && typeof stream._handle.setBlocking === 'function') {
  4 |       stream._handle.setBlocking(blocking);
  5 |     }
  6 |   });

In googling around, I haven't yet figured out what I'm doing wrong. Any help would be appreciated. Thanks!

Answer 1

As the other answer mentioned, this is probably not recommended. You should really stick your database on the backend somewhere.

However it is possible to do this using sql.js. I had no luck with the sqlite3 module.

Here is the example I used: https://github.com/sql-js/react-sqljs-demo

Start by installing Craco. This is needed for bundling the wasm file.

npm install @craco/craco --save

replace your react scripts with Craco scripts in package.json:

"scripts": {
-   "start": "react-scripts start",
-   "build": "react-scripts build",
-   "test": "react-scripts test",
"start": "craco start",
"build": "craco build",
"test": "craco test"
}

Make a craco.config.js file in the root of your project and place this code inside:

module.exports = {
    webpack: {
        configure:{
            // See https://github.com/webpack/webpack/issues/6725
            module:{
                rules: [{
                    test: /\.wasm$/,
                    type: 'javascript/auto',
                }]
            }
        }
    }
};

Then install sql.js

  npm install sql.js --save

You probably need to do some more backend setup after this, but for now import the package:

import initSqlJs from 'sql.js';
// Required to let webpack 4 know it needs to copy the wasm file to our assets
import sqlWasm from "!!file-loader?name=sql-wasm-[contenthash].wasm!sql.js/dist/sql-wasm.wasm";

I had to disable eslint to get the second import to work. Really not ideal, but eslint throws errors if you try using file-loader. These are the lines to remove in package.json:

  "eslintConfig": {
      "extends": "react-app"
  },

Now call the library!

const response = fetch("example.com/sqlitedatabase");
const buf = await response.arrayBuffer();
const SQL = await initSqlJs({ locateFile: () => sqlWasm });
const db = new SQL.Database(new Uint8Array(buf));

You can then query the database; The following code runs a query and then puts it into a more useable form than the default response.

const query = db.exec(`
   SELECT
     *
   FROM
     table;
`);
const columns = query[0].columns;
const values = query[0].values;
let rows = [];
values.forEach(element => {
    let row = {};
    for (let i = 0; i < columns.length; i++) {
       row[columns[i]] = element[i];
    }
    rows.push(row);
});

That should work, but as I mentioned above, you might need to mess with the backend a little. Often times single page applications have trouble with CORS when dealing with bundled files. To fix this on AWS Amplify, I added the following code in a file called customHttp.yml to the root of my project:

customHeaders:
- pattern: '**/*'
  headers:
    - key: Access-Control-Allow-Origin
      value: '*'
- pattern: '*.wasm'
  headers:
    - key: Access-Control-Allow-Origin
      value: '*'

(Edit: Added a more generic pattern to the file, didn't work without it.) Note that it may take some time for the custom headers to update, even though it looks like they update immediately. Also related to single page applications, wasm files need to be added to the list of files exempted from redirects:

Source address: </^((?!.(css|gif|ico|jpg|js|png|txt|svg|woff|ttf|json|map|wasm)$).)*$/>
Target address: /index.html
Type: 200 (Rewrite)