Building a cross platform VueJS app with a local SQL database that can load data from a static csv file
Tags: vue, cordova, vuetify, nanosql
Published: 2019-02-17 17:29:29

---
title: Building a cross platform VueJS app with a local SQL database that can load data from a static csv file
published: true
description: How to rapidly prototype a Vue.js cross-platform app that uses a local SQL database, imported from a bundled csv file.
cover_image: https://thepracticaldev.s3.amazonaws.com/i/a1l8g4uvmfspagqhoyzk.jpg
tags: VueJS, Cordova, Vuetify, NanoSQL
canonical_url: https://www.learningsomethingnew.com/vue-js-vue-cli-3-vuetify-cordova-nano-sql-building-a-cross-platform-app-with-a-local-sql-database-that-can-load-data-from-a-static-csv-file
---

![finished](https://www.learningsomethingnew.com/vue-csv-sample/finished.gif)

## What we're doing
In this tutorial we'll rapidly prototype a Vue.js cross-platform app, that uses a local SQL database which is initially imported from a static csv file. 
We'll use the VVVCNS Stack (I just made that up):
- [Vue.js](https://vuejs.org/) as the JS Framework
- [Vue CLI 3](https://cli.vuejs.org/guide/) for app scaffolding and plugins
- [Vuetify](https://vuetifyjs.com/en/) for the material design components
- [Cordova](https://cordova.apache.org/) for the cross-platform app generating powers
- [NanoSQL](https://nanosql.io/) for the local database

## Why would anyone need a local database?
It comes in handy when you want to ship your app with data, and also track your users' changing of that data in your app, but don't want the hassle of building a server side. 
This is what we did when building the language learning app [Flamingo](https://play.google.com/store/apps/details?id=com.cognitusapps.flamingo.elvish).
If you can make an app with no server side, you've just saved yourself about 2/3 of your dev time.

## Scaffolding
We're going to scaffold our app using `vue create`, which is a Vue CLI 3 command.

```
vue create vue-csv-sample
```

Choose default plugins (babel + eslint).

```
cd vue-csv-sample
vue add vuetify
```

Here we're adding the vuetify plugin (a Vue CLI 3 plugin)
Again choose default options.

```
vue add cordova
```

And this is the cordova plugin for Vue CLI. 
You can select where the cordova source will sit, and the app and package name.
The creates another src folder for the cordova project in src-cordova, where the configuration of the native app platforms resides.

Let's sanity test all this scaffolding craziness by serving our app in the browser platform:
```
npm run cordova-serve-browser
```

You should see your browser open at http://localhost:8080/ with something like this:
![sanity](https://www.learningsomethingnew.com/sanity.png)

> This is a good time to commit to git
> Notice that the Vue CLI plugins also change your .gitignore file accordingly

Now we'll add the NanoSQL cordova plugin. NanoSQL gives us an SQL database, and chooses the best implementation for each environment our app runs on, in Android it will choose SQLite, in web browser it will go for indexedDB etc.

```
npm i nano-sql --save
npm i axios --save
npm i cordova-plugin-nano-sqlite -- save
cd src-cordova
cordova plugin add cordova-plugin-nano-sqlite
```

> Note that cordova plugins are added from the src-cordova folder.
> We're also adding axios, which is the current recommended way of performing HTTP requests in Vue.

## Loading static CSV resource
First let's get some mock data csv from [mockaroo](https://mockaroo.com/)
The default data structure is: id,first_name,last_name,email,gender,ip_address. 
We'll add to that an avatar img field from dummyimage.com, and generate 30 lines of mock data.

> Mockaroo adds a new line at the end of the CSV file. Delete it or it will create an empty record in your DB.
> You can also import the data in JSON format, but CSV (and SQL) is more suited to tabular data

Take the `MOCK_DATA.csv` file you generated and put it in the `public` folder.
Replace the default HelloWorld component with PersonList component: just search-replace HelloWorld -> PersonList, and change the HelloWorld.vue file name to PersonList.vue.
We'll add a simple `axios.get` call to load the contents of the CSV file.

This is the content of the updated PersonList.vue:

```js
<template>
  <div>
    {{ response }}
  </div>
</template>

<script>
const axios = require('axios');

export default {
  name: 'PersonList',
  data() {
    return {
      response: ''
    }
  },
  mounted() {
    document.addEventListener(
      typeof cordova !== 'undefined' ? 'deviceready' : 'DOMContentLoaded',
      () => {
        axios.get('MOCK_DATA.csv')
          .then(response => {
            this.response = response;
          })
          .catch(err => {
            this.response = `ERROR! ${err}`;
          });
      }
    );
  }
};
</script>

```

Right now we're just testing if the static CSV file loads correctly. notice that the CSV file sits in the public folder, so the path doesn't have a prefix of "." or "/" or "~". It doesn't get resolved through webpack loaders. Read more about static asset loading [here](https://cli.vuejs.org/guide/html-and-static-assets.html).

Now running `npm run cordova-serve-browser` should show us the result of the get request:
![csv-loaded](https://www.learningsomethingnew.com/csv-loaded.png)

Hopefully you can see the content of the CSV file. Now let's import it into the DB.
> Notice the line `typeof cordova !== 'undefined' ? 'deviceready' : 'DOMContentLoaded'`. It attaches a different page loading event depending on whether it's a cordova environment or a normal web environment.

We will use NanoSQL's built in csv importing function to load the file. 
Make the following changes to PersonList.vue:

```js
<template>
  <div>
    {{ persondb.length }}
  </div>
</template>

<script>
const axios = require('axios');
import { nSQL } from 'nano-sql';
import { getMode } from 'cordova-plugin-nano-sqlite/lib/sqlite-adapter';

export default {
  name: 'PersonList',
  data() {
    return {
      persondb: []
    }
  },
  mounted() {
    nSQL().onConnected(() => {
      axios.get('MOCK_DATA.csv', {}).then(response => {
        nSQL()
          .loadCSV('persondb', response.data)
          .then(() => {
            nSQL('persondb')
              .query('select')
              .exec()
              .then(rows => {
                this.persondb = rows;
              });
          });
      });
    });

    document.addEventListener(
      typeof cordova !== 'undefined' ? 'deviceready' : 'DOMContentLoaded',
      () => {
        let model = [
          { key: 'id', type: 'int', props: ['pk', 'ai'] },
          { key: 'first_name', type: 'string' },
          { key: 'last_name', type: 'string' },
          { key: 'email', type: 'string' },
          { key: 'gender', type: 'string' },
          { key: 'ip_address', type: 'string' },
          { key: 'avatar', type: 'string' }
        ];

        nSQL('persondb')
          .config({
            mode: getMode()
          })
          .model(model)
          .connect();
      }
    );
  },
};
</script>

```

Here we've imported nSQL, loaded the CSV file using `loadCSV` method into the `persondb` table, and updated our data array `this.persondb` with the results of running the `select` query.
Finally we've displayed the length of the array, to see that we have 100 rows.
Notice we're defining the data model according to the fields in our CSV file.
Also notice we're configuring the database to persist data with `mode: getMode()`, which in the browser context will resolve to the IndexedDB adapter, and in a cordova environment (Android or IOS) will resolve to the SQLite adapter.

## Adding the visuals
Now that we have the DB loading down, let's make some sweet material UI to show our data.

```html
<template>
  <v-layout row>
    <v-snackbar v-model="showOperationStatus" left>{{operationStatus}}</v-snackbar>
    <v-flex xs12 sm6 offset-sm3>
      <v-card>
        <v-toolbar color="cyan" dark>
          <v-toolbar-side-icon></v-toolbar-side-icon>

          <v-toolbar-title>Person List</v-toolbar-title>

          <v-spacer></v-spacer>

          <v-btn icon ripple @click="deleteAll()">
            <v-icon color="lighten-1">delete</v-icon>
          </v-btn>
        </v-toolbar>

        <v-list three-line>
          <v-subheader>
            {{persons.length}} records
            <v-spacer></v-spacer>
            <v-btn @click="loadCsv">load csv</v-btn>
          </v-subheader>
          <template v-for="(person, index) in persons">
            <v-divider :inset="true" :key="index"></v-divider>

            <v-list-tile :key="'person'+index" avatar>
              <v-list-tile-avatar>
                <img :src="person.avatar">
              </v-list-tile-avatar>

              <v-list-tile-content>
                <v-list-tile-title v-html="`${person.first_name} ${person.last_name}`"></v-list-tile-title>
                <v-list-tile-sub-title v-html="`Email: ${person.email}`"></v-list-tile-sub-title>
                <v-list-tile-sub-title v-html="`IP: ${person.ip_address}`"></v-list-tile-sub-title>
              </v-list-tile-content>
              <v-list-tile-action>
                <v-btn icon ripple @click="deletePerson(person)">
                  <v-icon color="grey lighten-1">delete</v-icon>
                </v-btn>
              </v-list-tile-action>
            </v-list-tile>
          </template>
        </v-list>
      </v-card>
    </v-flex>
  </v-layout>
</template>
```
Here we're using a `<v-layout>` containing a single `<v-flex>` (like a cell), containing a single `<v-card>`, with a toolbar and a list inside. In the toolbar we have our delete-all button, which will drop the table. In the list we have a single subheader showing the number of rows, and a delete button for each person.
We've also added a snackbar to show the status of the operations we're doing.

## Some DB operations
Now let's take a look at the script section:

```js
const axios = require('axios');
import { nSQL } from 'nano-sql';
import { getMode } from 'cordova-plugin-nano-sqlite/lib/sqlite-adapter';

export default {
  name: 'PersonList',
  data() {
    return {
      showOperationStatus: false,
      operationStatus: '',
      persons: []
    };
  },
  mounted() {
    document.addEventListener(
      typeof cordova !== 'undefined' ? 'deviceready' : 'DOMContentLoaded',
      () => {
        let model = [
          { key: 'id', type: 'int', props: ['pk', 'ai'] },
          { key: 'first_name', type: 'string' },
          { key: 'last_name', type: 'string' },
          { key: 'email', type: 'string' },
          { key: 'gender', type: 'string' },
          { key: 'ip_address', type: 'string' },
          { key: 'avatar', type: 'string' }
        ];

        nSQL('persondb')
          .config({
            mode: getMode()
          })
          .model(model)
          .connect();

        nSQL().onConnected(() => {
          this.refreshData();
        });
      }
    );
  },
  methods: {
    async refreshData() {
      let rows = await nSQL('persondb')
        .query('select')
        .exec();
      if (rows.length == 0) this.persons = [];
      else this.persons = rows;
    },
    loadCsv() {
      axios.get('MOCK_DATA.csv', {}).then(response => {
        nSQL()
          .loadCSV('persondb', response.data, false)
          .then(() => {
            this.refreshData().then(() => {
              this.showOperationStatus = true;
              this.operationStatus = `CSV loaded`;
            });
          });
      });
    },
    deletePerson(person) {
      nSQL('persondb')
        .query('delete')
        .where(['id', '=', person.id])
        .exec()
        .then(rows => {
          this.showOperationStatus = true;
          this.operationStatus = `${rows.length} rows deleted`;
          this.refreshData();
        });
    },
    deleteAll() {
      nSQL('persondb')
        .query('delete')
        .exec()
        .then(result => {
          if (result[0].msg) {
            this.showOperationStatus = true;
            this.operationStatus = result[0].msg;
          }
          this.refreshData();
        });
    }
  }
};
```
Let's go over the methods:
- `refreshData` - updates the `persons` data field with the contents of the DB. This is done by running a `select` on all the rows.
- `loadCsv` - the same as before, only now we're popping the snackbar when the operation ends.
- `deletePerson` - runs a `delete` SQL query using the list person's id.
- `deleteAll` - running a `delete` without parameters will drop the table.

And that's it!
If you run it you can see that loading the CSV again will not add the rows that are already loaded. This is because id field is defined as "pk" in our data model.
Running with `yarn serve` will give you the web version, using IndexedDB as the SQL adapter.
Refresh the page and watch the data persist.

![finished](https://www.learningsomethingnew.com/vue-csv-sample/finished.gif)

Running with `npm run cordova-serve-android/ios` will give you the native version, using SQLite as the adapter.
Close the app, reload it and see that the data is still there.

Thanks for reading, and the code can be found [here](https://github.com/syonip/vue-csv-sample).


> This article is cross posted from my [blog](https://www.learningsomethingnew.com/vue-js-vue-cli-3-vuetify-cordova-nano-sql-building-a-cross-platform-app-with-a-local-sql-database-that-can-load-data-from-a-static-csv-file)