Using SQLite3 with Express

2022-11-12

Just some quick notes on getting sqlite3 to work with Node. This probably isn't a problem for most people but as I'm using centos 7 it requires building sqlite from source.

The first step is to install sqlite3 and build it from source.

npm install sqlite3 --build-from-source

The next step is to create a models/index.js file.

const { Sequelize, Model, DataTypes } = require('sequelize');

const sequelize = new Sequelize({
      dialect: 'sqlite',
      storage: 'db/mydb.db'
});

const db = { };

db.Sequelize = Sequelize;
db.sequelize = sequelize;

db.Post = sequelize.define('Post', {
    title: DataTypes.STRING,
    tags: DataTypes.STRING,
    summary: DataTypes.STRING,
    link: DataTypes.STRING,
    image: DataTypes.DATE,
});

db.User = sequelize.define('User', {
    username: DataTypes.STRING,
    password: DataTypes.STRING,
    admin: DataTypes.BOOLEAN,
});

module.exports = db;

Now we are just about ready to go.

We need to update app.js so that it syncs our models to the sqlite database. This way we can define our model and this will make sure the sqlite schemas are set up.

We add the following to the end of app.js.

var db = require('./models');
db.sequelize.sync({ force: true });
//db.sequelize.sync({ force: true });

The force true will cause tables to be dropped and recreated. Without the force, we will create the tables if they don't exist.

The alter true will make changes to the sqlite schema to match the model.

Now we can do:

const db = require('../models');
...
let posts = await db.Post.findAll(),
...

With that we are now ready to use sqlite in our express application.