This article will introduce how to use SQLite database for data storage in Vue3 and Vite projects. We will use the better-sqlite3 library to create and manage SQLite databases, and we will use Vue3 to develop the front-end interface.
- create project
First, we need to create a new Vue3 project. A new project called vue-sqlite can be created with the following command:
vue create vue-sqlite
Then, install the required dependencies, including better-sqlite3:
npm install better-sqlite3
- Create SQLite database
Next, we need to create the SQLite database. You can create a file called database.js at the root of your project and add the following code to the file:
const sqlite = require('better-sqlite3')
const path = require('path')
const dbPath = path. join(__dirname, 'database. sqlite')
const db = new sqlite(dbPath)
const initDb = () => {<!-- -->
db. prepare(`
CREATE TABLE IF NOT EXISTS todos (
id INTEGER PRIMARY KEY AUTOINCREMENT,
text TEXT NOT NULL,
completed INTEGER NOT NULL DEFAULT 0
);
`).run()
}
module.exports = {<!-- -->
db,
initDb,
}
In the above code, we use the better-sqlite3 library to create a SQLite database instance named db and export it. At the same time, we also define a function called initDb to initialize the database. In the initDb function, we use the SQL statement to create a table named todos, which includes id, text and completed three fields.
- Create a data model
Next, we need to create the data model. You can create a file called Todo.js at the root of your project and add the following code to the file:
const {<!-- --> db } = require('./database')
class Todo {<!-- -->
constructor(text, completed = false) {<!-- -->
this.text = text
this.completed = completed
}
save() {<!-- -->
const stmt = db. prepare(`
INSERT INTO todos (text, completed)
VALUES (?, ?)
`)
stmt.run(this.text, this.completed? 1 : 0)
}
static findAll() {<!-- -->
const stmt = db. prepare(`
SELECT *
FROM todos
`)
const rows = stmt. all()
return rows. map(row => new Todo(row. text, row. completed))
}
static findById(id) {<!-- -->
const stmt = db. prepare(`
SELECT *
FROM todos
WHERE id = ?
`)
const row = stmt. get(id)
return row ? new Todo(row. text, row. completed) : null
}
update() {<!-- -->
const stmt = db. prepare(`
UPDATE todos
SET text = ?, completed = ?
WHERE id = ?
`)
stmt.run(this.text, this.completed? 1 : 0, this.id)
}
delete() {<!-- -->
const stmt = db. prepare(`
DELETE FROM todos
WHERE id = ?
`)
stmt.run(this.id)
}
}
module.exports = Todo
In the above code, we defined a class called Todo to represent the data model of to-do items. In the constructor, we define two attributes, text and completed . At the same time, we also defined save, findAll, findById, update and delete and other methods are used to add, delete, modify and query data.
In the save method, we use a SQL statement to insert the current todo into the todos table. In the findAll method, we use the SQL statement to query all to-do items, and convert the query result into an instance of the Todo class. In the findById method, we use the SQL statement to query the to-do item with the specified ID, and convert the query result into an instance of the Todo class. In the update method, we use the SQL statement to update the todo with the specified ID. In the delete method, we use the SQL statement to delete the todo item with the specified ID.
- create interface
Next, we need to create the interface. You can create a file named TodoList.vue at the root of your project and add the following code to the file:
<template>
<div>
<h1>Todo List</h1>
<form @submit.prevent="addTodo">
<input v-model="newTodoText" type="text" placeholder="Add a new todo">
<button type="submit">Add</button>
</form>
<ul>
<li v-for="todo in todos" :key="todo.id">
<input type="checkbox" v-model="todo.completed" @change="updateTodo(todo)">
<span :class="{ completed: todo.completed }">{<!-- -->{ todo.text }}</span>
<button @click="deleteTodo(todo)">Delete</button>
</li>
</ul>
</div>
</template>
<script>
import Todo from './Todo'
export default {<!-- -->
data() {<!-- -->
return {<!-- -->
newTodoText: '',
todos: [],
}
},
async mounted() {<!-- -->
const rows = await Todo. findAll()
this.todos = rows
},
methods: {<!-- -->
async addTodo() {<!-- -->
const todo = new Todo(this.newTodoText)
todo. save()
this.todos.push(todo)
this.newTodoText = ''
},
async updateTodo(todo) {<!-- -->
todo. update()
},
async deleteTodo(todo) {<!-- -->
todo. delete()
this.todos = this.todos.filter(t => t.id !== todo.id)
},
},
}
</script>
<style>
.completed {<!-- -->
text-decoration: line-through;
}
</style>
In the above code, we define a Vue component called TodoList to display the to-do list. In data , we define two attributes, newTodoText and todos . In the mounted life cycle hook, we use the findAll method of the Todo class to query all to-do items and assign the query results to todos attribute. In the template, we use the v-for directive to render the todos list, and pass the v-model directive and @change The event implements the checking and updating of to-do items. At the same time, we also implement the deletion of to-do items through the @click event.
- Integrate with SQLite
Now, we can use the Todo class to add, delete, modify and query the SQLite database, and we can also use the TodoList component to display the to-do list. Next, we need to integrate them. The following code can be added to the file in the src/main.js file:
import {<!-- --> createApp } from 'vue'
import App from './App.vue'
import {<!-- --> initDb } from './database'
initDb()
createApp(App).mount('#app')
In the above code, we first call the initDb() function to initialize the SQLite database. Then, we use the createApp function to create a Vue application and attach it to the #app element in the HTML.
Now, we have completed the process of creating a SQLite database and storing data using Vue3 and Vite. The application can be started by running the following command:
npm run dev
Open your browser and visit http://localhost:3000 to see the to-do list page. Now we can add, update and delete todos and their data will be stored in the SQLite database.
epilogue
This article describes how to use SQLite database for data storage in Vue3 and Vite projects. We used the better-sqlite3 library to create and manage the SQLite database, and Vue3 to develop the front-end interface. With the guidance of this article, you can easily integrate SQLite database into your