Un tutorial de SQLite con Node.js

En este tutorial, demostraré cómo usar SQLite en combinación con JavaScript dentro del entorno Node.js con la ayuda del controlador sqlite3 Node.js. Para aquellos que no están familiarizados con SQLite, es una base de datos relacional simple de un solo archivo que es muy popular entre los dispositivos inteligentes, los sistemas integrados e incluso las aplicaciones web pequeñas.

Configuración e instalación

Comenzaré creando un nuevo paquete npm usando npm init dentro de un directorio vacío llamado node-sqlite-tutorial.

$ npm init
This utility will walk you through creating a package.json file.
It only covers the most common items, and tries to guess sane defaults.

See `npm help json` for definitive documentation on these fields
and exactly what they do.

Use `npm install <pkg> --save` afterwards to install a package and
save it as a dependency in the package.json file.

Press ^C at any time to quit.
name: (app) node-sqlite
version: (0.0.0) 0.1.0
description: Code for tutorial blog on node and sqlite
entry point: (index.js) main.js
test command: 
git repository: 
keywords: 
author: Adam McQuistan
license: (BSD) MIT
About to write to /node-sqlite/app/package.json:

{
  "name": "node-sqlite",
  "version": "0.1.0",
  "description": "Code for tutorial blog on node and sqlite",
  "main": "main.js",
  "scripts": {
    "test": "echo "Error: no test specified" && exit 1"
  },
  "repository": "",
  "author": "Adam McQuistan",
  "license": "MIT"
}


Is this ok? (yes) 

A continuación, tendré que instalar sqlite3 paquete a través de npm así:

$ npm install --save sqlite3

Además de sqlite3 voy a instalar Azulejo para poder usar la funcionalidad de promesa familiar en la programación de mi base de datos.

$ npm install --save bluebird

Ahora crearé un archivo vacío junto al archivo package.json llamado database.sqlite3 en el que SQLite almacenará los datos.

Diseño de la base de datos

Como con casi todos mis otros artículos, usaré una aplicación inventada para ayudar a describir algunos de los aspectos importantes de la programación de bases de datos con Node.js y SQLite. Para este artículo, asumo que estoy construyendo la capa de acceso a datos para una aplicación de seguimiento de proyectos y tareas. Las reglas comerciales básicas para la capa de acceso a datos de esta aplicación son las siguientes:

• La aplicación tiene proyectos
• Cada proyecto puede tener una o más tareas para completar

Con las reglas comerciales establecidas, puedo tomar esa información y comenzar a diseñar las tablas necesarias y sus campos. Está claro que necesitaré un proyectos mesa, así como una Tareas mesa. Por lo demás, usaré un poco de intuición, algunos datos de prueba inventados y seguiré con ellos (una característica de trabajo común para la mayoría de los desarrolladores).

tabla de proyectos

nombre de identificación

tabla de tareas

id nombre descripción isCompleted projectId

Bien, ahora que sé lo que necesito crear, ahora puedo traducirlo en código.

Creando la base de datos

Para comenzar, necesitaré hacer un archivo main.js junto con un dao.js (o Objeto de acceso a datos) en el mismo directorio que el archivo package.json.

Dentro de dao.js agregaré importaciones para sqlite3 y Bluebird’s Promise objetos. Después de eso, distribuiré una clase de acceso a datos llamada AppDAO que establecerá una conexión a la base de datos dentro de un constructor y la asignará a un campo miembro llamado db.

// dao.js

const sqlite3 = require('sqlite3')
const Promise = require('bluebird')

class AppDAO {
  constructor(dbFilePath) {
    this.db = new sqlite3.Database(dbFilePath, (err) => {
      if (err) {
        console.log('Could not connect to database', err)
      } else {
        console.log('Connected to database')
      }
    })
  }
}

module.exports = AppDAO

La conexión es bastante sencilla. Simplemente crea una instancia de sqlite3 Database constructor de clases pasándole la ruta al archivo de base de datos SQLite con el que desea conectarse y, opcionalmente, verifique los errores que puedan ocurrir. Como se señaló anteriormente, estoy almacenando este objeto de conexión en un campo llamado db sobre el AppDAO clase.

Avanzaré explicando cómo usar el objeto de conexión para enviar consultas a la base de datos. El paquete sqlite3 Node.js ofrece varios métodos diferentes para ejecutar consultas, pero en los que me centraré en este tutorial son:

• run: se utiliza para crear o modificar tablas y para insertar o actualizar datos de tablas
• get: seleccione una sola fila de datos de una o más tablas
• all: seleccione varias filas de datos de una o más tablas

Para empezar, me gustaría explorar el run método. Su sintaxis general se ve así:

db.run('SOME SQL QUERY', [param1, param2], (err) => {
  if (err) {
    console.log('ERROR!', err)
  }
})

El primer parámetro pasado a run(...) es una cadena de SQL que se ejecutará y es el único parámetro obligatorio. El segundo es una matriz opcional de parámetros que la biblioteca sqlite3 intercambiará por cualquier ‘?’ marcadores de posición dentro de la consulta (lo demostraré en un momento). La final es una función de devolución de llamada de error.

Como puede sospechar, usaré el run(...) función para crear y actualizar mis proyectos y tareas. Sin embargo, en realidad voy a envolverlo en mi propia versión de un run método en el AppDAO clase porque me gustaría encapsularlo en una bluebird Promise para hacer las cosas explícitamente asincrónicas y basadas en promesas así:

// dao.js

const sqlite3 = require('sqlite3')
const Promise = require('bluebird')

class AppDAO {
  // omitting constructor code
  
  run(sql, params = []) {
    return new Promise((resolve, reject) => {
      this.db.run(sql, params, function (err) {
        if (err) {
          console.log('Error running sql ' + sql)
          console.log(err)
          reject(err)
        } else {
          resolve({ id: this.lastID })
        }
      })
    })
  }
}

Con mi costumbre AppDAO.run(...) método que ahora puedo utilizar para crear las tablas de productos y tareas.

Para comenzar, agrego dos archivos más a mi proyecto llamados project_repository.js y task_repository.js. Dentro de project_repository.js defino una clase llamada ProjectRepository que tiene un constructor que acepta una instancia del AppDAO objeto y un createTable método que ejecuta algunos DDL (lenguaje de definición de datos) SQL así:

// project_repository.js

class ProjectRepository {
  constructor(dao) {
    this.dao = dao
  }

  createTable() {
    const sql = `
    CREATE TABLE IF NOT EXISTS projects (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT)`
    return this.dao.run(sql)
  }
}

module.exports = ProjectRepository;

Luego hago esencialmente lo mismo de nuevo, pero esta vez en el archivo task_repository.js.

// task_repository.js

class TaskRepository {
  constructor(dao) {
    this.dao = dao
  }

  createTable() {
    const sql = `
      CREATE TABLE IF NOT EXISTS tasks (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT,
        description TEXT,
        isComplete INTEGER DEFAULT 0,
        projectId INTEGER,
        CONSTRAINT tasks_fk_projectId FOREIGN KEY (projectId)
          REFERENCES projects(id) ON UPDATE CASCADE ON DELETE CASCADE)`
    return this.dao.run(sql)
  }
}

module.exports = TaskRepository;

El SQL DDL para crear las tablas está completo, por lo que pasaré a los métodos para insertar datos en las tablas.

Insertar datos

En el ProjectRepository clase necesito agregar un create método que recibe el nombre del proyecto para crear y ejecuta la instrucción INSERT apropiada usando el AppDAO.run(...) método. Observe cómo he usado ‘?’ para representar el valor del nombre del proyecto y luego poner el name parámetro en el argumento de matriz de params opcional al run(...) método. Esto se conoce como una declaración de consulta parametrizada que limpiará las entradas para minimizar los riesgos de inyección de SQL.

// project_repository.js

class ProjectRepository {
  // omitting other methods

  create(name) {
    return this.dao.run(
      'INSERT INTO projects (name) VALUES (?)',
      [name])
  }
}

module.exports = ProjectRepository;

Se necesita un método de creación similar para TaskRepository clase.

// task_repository.js

class TaskRepository {
  // omitting other methods

  create(name, description, isComplete, projectId) {
    return this.dao.run(
      `INSERT INTO tasks (name, description, isComplete, projectId)
        VALUES (?, ?, ?, ?)`,
      [name, description, isComplete, projectId])
  }
}

module.exports = TaskRepository;

Ahora que tengo la capacidad de INSERTAR datos en la base de datos, me gustaría agregar la funcionalidad para actualizarla.

Actualización de datos

En el ProjectRepository clase voy a agregar un update método que toma un project objeto y actualiza todos los campos para el registro de la base de datos de ese proyecto nuevamente utilizando el AppDAO.run(...) método, así:

// project_repository.js

class ProjectRepository {
  // omitting other methods

  update(project) {
    const { id, name } = project
    return this.dao.run(
      `UPDATE projects SET name = ? WHERE id = ?`,
      [name, id]
    )
  }
}

module.exports = ProjectRepository;

El siguiente paso es agregar el método de actualización correspondiente a TaskRepository clase.

// task_repository.js

class TaskRepository {
  // omitting other methods

  update(task) {
    const { id, name, description, isComplete, projectId } = task
    return this.dao.run(
      `UPDATE tasks
      SET name = ?,
        description = ?,
        isComplete = ?,
        projectId = ?
      WHERE id = ?`,
      [name, description, isComplete, projectId, id]
    )
  }
}

module.exports = TaskRepository;

Eliminar datos

La última funcionalidad mutacional a implementar es proporcionar la capacidad de eliminar registros de la base de datos. Para esto volveré a usar el AppDAO.run(...) método junto con el nuevo delete métodos tanto para el ProjectRepository y TaskRepository clases.

Xa ProjectRepository esto se ve así:

// project_repository.js

class ProjectRepository {
  // omitting other methods

  delete(id) {
    return this.dao.run(
      `DELETE FROM projects WHERE id = ?`,
      [id]
    )
  }
}

module.exports = ProjectRepository;

Y para TaskRepository se parece a esto:

// task_repository.js

class TaskRepository {
  // omitting other methods

  delete(id) {
    return this.dao.run(
      `DELETE FROM tasks WHERE id = ?`,
      [id]
    )
  }
}

module.exports = TaskRepository;

Muy bien, eso resume todas las formas en que usaré el run método. A continuación presentaré los otros dos relacionados get y all Métodos del paquete sqlite3 Node.js.

Leer datos

En esta sección voy a repasar cómo usar el get y all métodos de la biblioteca sqlite3 Node.js. Como se mencionó previamente, get se utiliza para recuperar una sola fila de datos mientras all se utiliza para consultar muchas filas de datos.

La sintaxis básica para usar get Se ve como esto:

db.get('SELECT ...', [param1, param2], (err, result) => {
  if (err) {
    console.log(err)
  } else {
    // do something with result
  }
})

Dónde db es un objeto de conexión sqlite3. Notarás que la sintaxis es esencialmente idéntica a la run excepto que la devolución de llamada tiene un parámetro adicional que contiene el objeto de resultado de la consulta, asumiendo que no se arrojó ningún error.

La sintaxis base para all es esencialmente el mismo de nuevo, excepto que el segundo parámetro de la devolución de llamada es una matriz de resultados devueltos por la consulta, así:

db.all('SELECT ...', [param1, param2], (err, results) => {
  if (err) {
    console.log(err)
  } else {
    // do something with results
  }
})

Al igual que hice con sqlite3 run método voy a implementar el get y all métodos que utilizan el bluebird Promise dentro de AppDAO clase como se muestra a continuación:

// dao.js

const sqlite3 = require('sqlite3').verbose()
const Promise = require('bluebird')

class AppDAO {
  // omitting other methods
  
  get(sql, params = []) {
    return new Promise((resolve, reject) => {
      this.db.get(sql, params, (err, result) => {
        if (err) {
          console.log('Error running sql: ' + sql)
          console.log(err)
          reject(err)
        } else {
          resolve(result)
        }
      })
    })
  }

  all(sql, params = []) {
    return new Promise((resolve, reject) => {
      this.db.all(sql, params, (err, rows) => {
        if (err) {
          console.log('Error running sql: ' + sql)
          console.log(err)
          reject(err)
        } else {
          resolve(rows)
        }
      })
    })
  }
}

Ahora puedo usar estos métodos en el ProjectRepository y TaskRepository clases para recuperar datos de la base de datos SQLite.

Para empezar añadiré getById métodos a cada clase para seleccionar sus registros por id.

En ProjectRepository Agrego esto:

// project_repository.js

class ProjectRepository {
  // omitting other methods

  getById(id) {
    return this.dao.get(
      `SELECT * FROM projects WHERE id = ?`,
      [id])
  }
}

module.exports = ProjectRepository;

Y en TaskRepository similar:

// task_repository.js

class TaskRepository {
  // omitting other methods

  getById(id) {
    return this.dao.get(
      `SELECT * FROM tasks WHERE id = ?`,
      [id])
  }
}

module.exports = TaskRepository;

Para demostrar el AppDAO.all(...) método Agregaré la capacidad de seleccionar todos los proyectos, así como todas las tareas para un proyecto determinado.

El código para SELECCIONAR todos los proyectos se ve así:

// project_repository.js

class ProjectRepository {
  // omitting other methods

  getAll() {
    return this.dao.all(`SELECT * FROM projects`)
  }
}

module.exports = ProjectRepository;

Luego, para seleccionar todas las tareas para un proyecto, usaré un método llamado getTasks(projectId) que espera la identificación del proyecto para el que desea las tareas.

// project_repository.js
class ProjectRepository {
  // omitting other methods

  getTasks(projectId) {
    return this.dao.all(
      `SELECT * FROM tasks WHERE projectId = ?`,
      [projectId])
  }
}

module.exports = ProjectRepository;

Uso del código de acceso a datos

Hasta ahora, básicamente he creado una biblioteca de acceso a datos para este proyecto ficticio y aplicación de seguimiento de tareas. Lo que me gustaría hacer ahora es usarlo para cargar mis datos de prueba que se muestran en las tablas en el Diseño de la base de datos sección.

En el archivo main.js querré introducir el AppDAO, ProjectRepositoryy TaskRepository clases a través de require. Luego los usaré para crear las tablas, completarlas con datos y luego recuperar datos de la base de datos y mostrarlos en la consola.

// main.js

const Promise = require('bluebird')
const AppDAO = require('./dao')
const ProjectRepository = require('./project_repository')
const TaskRepository = require('./task_repository')

function main() {
  const dao = new AppDAO('./database.sqlite3')
  const blogProjectData = { name: 'Write Node.js - SQLite Tutorial' }
  const projectRepo = new ProjectRepository(dao)
  const taskRepo = new TaskRepository(dao)
  let projectId

  projectRepo.createTable()
    .then(() => taskRepo.createTable())
    .then(() => projectRepo.create(blogProjectData.name))
    .then((data) => {
      projectId = data.id
      const tasks = [
        {
          name: 'Outline',
          description: 'High level overview of sections',
          isComplete: 1,
          projectId
        },
        {
          name: 'Write',
          description: 'Write article contents and code examples',
          isComplete: 0,
          projectId
        }
      ]
      return Promise.all(tasks.map((task) => {
        const { name, description, isComplete, projectId } = task
        return taskRepo.create(name, description, isComplete, projectId)
      }))
    })
    .then(() => projectRepo.getById(projectId))
    .then((project) => {
      console.log(`nRetreived project from database`)
      console.log(`project id = ${project.id}`)
      console.log(`project name = ${project.name}`)
      return projectRepo.getTasks(project.id)
    })
    .then((tasks) => {
      console.log('nRetrieved project tasks from database')
      return new Promise((resolve, reject) => {
        tasks.forEach((task) => {
          console.log(`task id = ${task.id}`)
          console.log(`task name = ${task.name}`)
          console.log(`task description = ${task.description}`)
          console.log(`task isComplete = ${task.isComplete}`)
          console.log(`task projectId = ${task.projectId}`)
        })
      })
      resolve('success')
    })
    .catch((err) => {
      console.log('Error: ')
      console.log(JSON.stringify(err))
    })
}

main()

Ejecutar usando node Me gusta esto:

$ node main.js

Y verá la salida como se muestra a continuación.

Connected to database
Retreived project from database
project id = 1
project name = 1
Retrieved project tasks from database
task id = 1
task name = Outline
task description = High level overview of sections
task isComplete = 1
task projectId = 1
task id = 2
task name = Write
task description = Write article contents and code examples
task isComplete = 0
task projectId = 1

Conclusión

En este tutorial, he revisado los conceptos básicos de la API del paquete sqlite3 de Node.js y he demostrado cómo puede envolver esa funcionalidad en JavaScript orientado a objetos con un enfoque en una implementación asincrónica basada en Promise.

Como siempre, les agradezco su lectura y agradezco los comentarios y críticas a continuación.