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:
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,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:
<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:
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 thepersonsdata field with the contents of the DB. This is done by running aselecton all the rows. -
loadCsv- the same as before, only now we're popping the snackbar when the operation ends. -
deletePerson- runs adeleteSQL query using the list person's id. -
deleteAll- running adeletewithout 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.
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.
This article is cross posted from my blog
Top comments (4)
Hi! I think about use something like that but... is possible use this with a container like docker and see this data for all the users who have access? Or im not understanding what means local storage? Thnks and sorry for my bad english.
Hi Fran! Actually I don't have much experience with docker, but notice that this SQL DB sits completely in the client. There is no server here. So if you run this in the browser, the DB lives in the vm of the browser (chrome or whatever the user uses), and if you build a cordova native app, it will use the native SQL adapters of android/ios, but still the data stays only on the client's device.
Is this what you meant?
Ahhh it´s ok! no, i dont refer to this, but your answer was very clear. Thnks! =)
sweet :)