Mobile app prototyping with Quasar and SQLite

This is the first post of a two part series that demonstrates how we can create a mobile app by developing locally with a SQLite file, and then easily transfer it to Turso for production.

While working on prototypes, we normally want to deal with technology that's easy to set up and just works. And for folks (like me) who have terrible internet connectivity, or those who travel a lot, the ability to keep on building things while offline could go a long way.

Build tools (Vite, WebPack, etc) have covered the first part involved in development on the offline aspect pretty well when it comes to managing and running your code. But, the same is mostly not true for the second part, data and databases.

It can be quite cumbersome when trying to figure out the tools to use for offline data management, and sometimes we can waste a significant amount of time setting that up. That is, unless we just use a local SQLite file, give it a schema and call it a day.

In this tutorial we'll be creating a prototype for a “recipe book” mobile application using Quasar, the open source Vue.js based framework for developing responsive hybrid applications in many flavors, and SQLite. And since we'll not be able to access the SQLite file using web APIs in Quasar in this case, we'll set up an express server through which that can be done.

We'll be using the libSQL JavaScript SDK to manage the SQLite database within the express server. By doing that, the migration to Turso should we opt to deploy the express server somewhere, wouldn't require any changes to be made to the source code.

#Why SQLite?

Beyond the well documented reasons as to why SQLite is the most deployed and used database in the world, including the device you are using to read this, its portability is the sweet spot we've aimed for in this scenario. SQLite database files are backward compatible, multi platform and architecture hence easy to move around without much fuss, require no initial set up, and we can easily take them anywhere without much complications compared to other databases.

Due to their small size, we could easily push a SQLite database file to GitHub to easily synchronize app state with others, use it in e2e tests to save on usage quotas for our deployed databases, and more.

Turso, being distributed SQLite for modern applications, has also extended the portability aspect of SQLite by making it easy for you to copy your SQLite database data and upload it to Turso for global distribution. That's the part we're going to cover and extend upon in the next part in this short series.

Prerequisites:

• Node >=14 (or any newer LTS Node.js version) and NPM >=6.14.12 or Yarn >= 1.21.1 installed on your machine.
• The SQLite shell.

#Creating a new Quasar application

To create a new Quasar application, run the following command on your terminal:

npm create quasar my-recipe-book

Proceed to pick the best options for your development setup when prompted by the installation tool, make sure you add axios, and the Pinia store while on this step.

At the end, you should have a new Quasar application with the following directory structure.

├── README.md
├── index.html
├── package.json
├── postcss.config.cjs
├── public
├── quasar.config.js
├── src
│   ├── App.vue
│   ├── assets
│   ├── boot
│   ├── components
│   ├── css
│   ├── layouts
│   ├── pages
│   ├── router
│   └── stores
└── tsconfig.json

Most of our application code will be going to the src directory.

Next, go ahead and install the plugins that will be needed to make working with data a breeze.

npm install @libsql/client uuid express cors body-parser
npm install --save-dev tsx

We've referenced the use of the libSQL SDK earlier, the express, cors, and body-parser will help us set up the Express server, and the uuid package will be used to create unique IDs. We'll be using the tsx module to compile and run our simple Express server.

#Setting up the SQLite database

Open the SQLite shell and create a new database by running.

sqlite3 recipe-book.db

Proceed to issuing the queries found here to add a schema to our database.

Then close the shell session by issuing:

.quit

You should now have a SQLite file named recipe-book.db at the root of our project.

If you fancy your SQL, you could add some data using the shell, or you could opt for GUI applications such as TablePlus, SQLiteStudio, DataFlare, etc. This is an advantage we get while using SQLite as we are spoilt for choice on the number of tools (like the ones mentioned) we could use to manage our data.

#Setting up the Express server

We will perform transactions with our SQLite file database from within a simple Express server.

Create a new file at the root of the project, giving it the name server.ts.

Start off by importing all the needed packages and server port declaration.

import express from 'express';
import bodyParser from 'body-parser';
import cors from 'cors';
import { createClient } from '@libsql/client';
import { v4 as uuidV4 } from 'uuid';
import { Ingredient, Recipe } from 'src/components/models';

const PORT = 3000;

Afterwards, we need to instantiate a new database client using the libSQL driver.

const db = createClient({
  url: 'file:recipe-book.db',
});

Then, we can use the created client within the following Express routes.

First, we have a GET route that fetches all the recipes and related ingredients from our database.

app.get('/recipes', async (req, res) => {
  const results = await db.execute(
    `select recipes.id, recipes.name, recipes.nutrition_information as nutritionInformation, 
recipes.instructions, recipes.created_at as createdAt, recipes.updated_at as updatedAt, 
json_group_array(json_object('id', ingredients.id, 'name', ingredients.name, 'measurements', ingredients.measurements)) 
as ingredients from recipes join ingredients on ingredients.recipe_id = recipes.id group by recipes.id`,
  );

  res.json({ recipes: results.rows });
});

Second, we have a POST route that lets us add new recipes to our database.

app.post('/recipe', async (req, res) => {
  const { recipe, ingredients } = (await req.body) as unknown as {
    recipe: Recipe;
    ingredients: Ingredient[];
  };
  const recipeId = uuidV4();
  await db.execute({
    sql: 'insert into recipes(id, name, nutrition_information, instructions) values (?, ?, ?, ?)',
    args: [ recipeId, recipe.name recipe.nutritionInformation as string, recipe.instructions as string],
  });

  const statements = ingredients?.map((ingredient) => ({
    sql: 'insert into ingredients(id, name, measurements, recipe_id) values (?, ?, ?, ?)',
    args: [uuidV4(), ingredient.name, ingredient.measurements, recipeId],
  }));
  await db.batch(statements, 'write');

  res.json({ ok: true });
});

And lastly, a DELETE route.

app.delete('/recipe/:id', async (req, res) => {
  const { id } = req.params;
  await db.execute({
    sql: 'delete from ingredients where recipe_id = ?',
    args: [id],
  });

  await db.execute({
    sql: 'delete from recipes where id = ?',
    args: [id],
  });

  res.json({ ok: true });
});

We wind up our Express server by listening to HTTP requests on our selected port.

app.listen(PORT, () => console.log(`App running at port ${PORT}`));

Streamline the running of the Express server by adding the following script to the package.json file.

"dev:db": "tsx --watch server.ts"

Then, open a new terminal and run it.

npm run dev:db

#The application's pages

Our recipes app has recipe listing, addition, and details pages. Since the default IndexPage.vue component will be our listing page, you should add the recipe addition and details pages as AddRecipePage.vue and RecipeDetailsPage.vue respectively.

You should also add the two pages to the app's routes.

I've utilized Pinia to manage state within the Quasar application, hence, most of the data fetching logic is abstracted by the store.

So, create a new Pinia store with the Quasar CLI by running:

npx quasar new store <store_name> [--format ts]

Then, copy the code found inside the store as seen on GitHub and paste it into the file created above.

Looking at the code, you'll see that the store conveniently provides us some functions and variables that we can use within our pages, which are:

recipes: A reactive variable containing the array of all stored recipes.

isLoading: A reactive variable that gives us the fetching state of data from our database.

fetchAllRecipes(): A function that lets us populate the recipes variable on demand.

getRecipeById(): A function that lets us get individual recipes.

addRecipe(): A function that lets us store new recipes to the database.

deleteRecipe(): A function that lets us delete existing recipes.

Now, to get the recipe data from the database, all we need is to import the store and get the recipes variable.

import { storeToRefs } from 'pinia';
import { useDatabaseStore } from 'src/stores/database';

const store = useDatabaseStore();
const { recipes } = storeToRefs(store);

We should also trigger fetchAllRecipes() from the home page to make sure that the recipes variable is always populated with some data even on the first visit.

const { fetchAllRecipes } = store;

fetchAllRecipes();

Then we can list the recipes on the page.

In the recipe details page, we need to call the getRecipeById() function and pass it the id parameter we get from the page's route to get the details of the recipe we'd like to see.

const route = useRoute();
const recipe = getRecipeById(route.params.id as string);

Since we're providing a delete button on this page, we should also import the deleteRecipe() function and add the necessary logic to facilitate that.

const remove = () => {
  deleteRecipe(recipe as Recipe);
  // navigate to home page afterwards
};

We can then proceed to displaying the obtained data.

Inside the recipe addition page, we first set up a form that lets the user add all the required recipe information. Quasar makes form validation a breeze in this step.

Lastly, we import the addRecipe() function from the store, call, and pass it the form data to submit it to the database.

import { useDatabaseStore } from 'src/stores/database';
const { addRecipe } = store;

const onSubmit = () => {
  addRecipe({
    recipe: recipe.value as unknown as Recipe,
    ingredients: ingredients.value as unknown as Ingredient[],
  });
  // navigate to home page afterwards
};

Here is a preview of the screens of the Quasar application we'll have created up to this point.

That's all for the first part of this short series.

In the next part, we will learn how to distribute the data inside the SQLite file used in this project to Turso, then proceed to using the Turso REST API to manage that data within a Flutter application.