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 de sqlite3 Node.js dr...

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 {#configuración e instalación}

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
$ 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 el paquete sqlite3 a través de npm de la siguiente manera:

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

1
$ npm install --save bluebird

Ahora crearé un archivo vacío justo al lado del archivo package.json llamado base de datos.sqlite3 en el que SQLite almacenará los datos.

Diseño de la base de datos {#diseñodelabasede datos}

Al igual que 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 creando 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é una tabla de proyectos así como una tabla de tareas. Para el resto, solo usaré un poco de intuición, algunos datos de prueba inventados y seguiré adelante (una característica de trabajo común para la mayoría de los desarrolladores).

tabla de proyectos

nombre de identificación


1 Escribir Node.js - Tutorial de SQLite

tabla de tareas

id nombre descripción isCompleted projectId


1 Esquema Descripción general de alto nivel de las secciones 1 1 2 Escribir Escribir el contenido del artículo y ejemplos de código 0 1

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

Creación de la base de datos {#creación de la base de datos}

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

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
// 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 cree una instancia del constructor de la clase Base de datos sqlite3 pasándole la ruta al archivo de la base de datos SQLite con el que desea conectarse y, opcionalmente, verifique los errores que puedan ocurrir. Como se indicó anteriormente, estoy almacenando este objeto de conexión en un campo llamado db en la clase AppDAO.

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

  • run: se usa 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
  • todos: seleccione varias filas de datos de una o más tablas

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

1
2
3
4
5
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 a ejecutar y es el único parámetro requerido. El segundo es una matriz opcional de parámetros que la biblioteca sqlite3 intercambiará por cualquier marcador 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é la función run(...) para crear y actualizar mis proyectos y tareas. Sin embargo, en realidad voy a envolverlo en mi propia versión de un método run en la clase AppDAO porque me gustaría encapsularlo en bluebird Promise para hacer las cosas explícitamente asincrónicas y basadas en promesas como asi que:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
// 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 método AppDAO.run(...) personalizado, ahora puedo utilizarlo 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 objeto AppDAO y un método createTable que ejecuta algo de SQL DDL (lenguaje de definición de datos) así:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
// 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 vuelvo a hacer esencialmente lo mismo, pero esta vez en el archivo task_repository.js.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
// 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.

Inserción de datos

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
// 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 la clase TaskRepository.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
// 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 la clase ProjectRepository agregaré un método update que toma un objeto project y actualiza todos los campos para el registro de la base de datos de ese proyecto nuevamente utilizando el método AppDAO.run(...), al igual que:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
// 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 la clase TaskRepository.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
// 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;

Eliminación de datos

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

Para ProjectRepository esto se ve así:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
// 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 ve así:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
// 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 método run. A continuación, presentaré los otros dos métodos relacionados con el paquete get y all sqlite3 Node.js.

Lectura de datos {#lectura de datos}

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

La sintaxis base para usar get se ve así:

1
2
3
4
5
6
7
db.get('SELECT ...', [param1, param2], (err, result) => {
  if (err) {
    console.log(err)
  } else {
    // do something with result
  }
})

Donde db es un objeto de conexión sqlite3. Notará que la sintaxis es esencialmente idéntica al método 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 produjo ningún error.

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

1
2
3
4
5
6
7
db.all('SELECT ...', [param1, param2], (err, results) => {
  if (err) {
    console.log(err)
  } else {
    // do something with results
  }
})

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
// 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 las clases ProjectRepository y TaskRepository para recuperar datos de la base de datos SQLite.

Para comenzar, agregaré métodos getById a cada clase para seleccionar sus registros por id.

En ProjectRepository agrego esto:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
// 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 de manera similar:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
// 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 método AppDAO.all(...) 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í:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
// 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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
// project_repository.js
class ProjectRepository {
  // omitting other methods

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

module.exports = ProjectRepository;

Puesta en uso del código de acceso a datos {#puesta en 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 la sección Diseño de la base de datos.

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
// 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 taskRepo.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 nodo como este:

1
$ node main.js

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
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, revisé los conceptos básicos de la API del paquete sqlite3 de Node.js y demostré 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.