Jonathan's Dev Blog Privacy About Contact

VueJs + 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

January 04, 2019

finished

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 as the JS Framework
Vue CLI 3 for app scaffolding and plugins
Vuetify for the material design components
Cordova for the cross-platform app generating powers
NanoSQL 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. 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

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 The default data structure is: id,firstname,lastname,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:

<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.

Now running npm run cordova-serve-browser should show us the result of the get request: csv-loaded

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:

<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.

<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:

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

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.

Follow me on twitter at @johnnymakestuff for more.

Written by@Jonathan Perry

Fullstack dev - I like making products fast

GitHub Medium Twitter