Building a Shopping App with Express, SQLite3, and Sequelize

A step-by-step guide to creating a secure shopping application

Project Setup and Foundation

Think of setting up a web application like building a house. We need to start with a strong foundation (our project structure), install the necessary utilities (our dependencies), and create the blueprints (our database schema) before we can start decorating (adding features).

Initial Project Setup


mkdir shopping_app
cd shopping_app
npm init -y

This is like creating the blueprint for our house. The package.json file that's generated is similar to a building permit - it tells others what our project is about and what it needs to function.

Installing Essential Dependencies


npm install express express-session sqlite3 sequelize bcrypt cookie-parser dotenv
npm install --save-dev nodemon

Think of these packages as our construction tools:

- express: The foundation framework (like the concrete foundation of our house)

- sqlite3 & sequelize: Our database tools (like the plumbing system)

- bcrypt: Security system (like installing locks on doors)

- express-session & cookie-parser: User session management (like a guest registry system)

- dotenv: Environment configuration (like the building's control panel)

Project Structure

Let's organize our project like rooms in a house - each with its specific purpose:


shopping_app/
  ├── config/
  │   └── database.js
  ├── models/
  │   ├── index.js
  │   └── user.js
  ├── routes/
  │   ├── index.js
  │   └── auth.js
  ├── middleware/
  │   └── auth.js
  ├── .env
  └── app.js

Setting Up the Database Configuration

First, create a .env file in your root directory:


# .env
DB_NAME=shopping_db
SESSION_SECRET=your_secret_key_here

Now, let's create our database configuration (config/database.js):


// config/database.js
const { Sequelize } = require('sequelize');
require('dotenv').config();

const sequelize = new Sequelize({
    dialect: 'sqlite',
    storage: './database.sqlite',
    logging: false
});

module.exports = sequelize;

Creating the User Model

Think of a model like a blueprint for a type of object in our system. The User model is like a form that specifies what information we need about each user:


// models/user.js
const { Model, DataTypes } = require('sequelize');
const sequelize = require('../config/database');
const bcrypt = require('bcrypt');

class User extends Model {
    async validatePassword(password) {
        return bcrypt.compare(password, this.password);
    }
}

User.init({
    email: {
        type: DataTypes.STRING,
        unique: true,
        allowNull: false,
        validate: {
            isEmail: true
        }
    },
    password: {
        type: DataTypes.STRING,
        allowNull: false
    },
    name: {
        type: DataTypes.STRING,
        allowNull: false
    }
}, {
    sequelize,
    modelName: 'User',
    hooks: {
        beforeCreate: async (user) => {
            const salt = await bcrypt.genSalt(10);
            user.password = await bcrypt.hash(user.password, salt);
        }
    }
});

module.exports = User;

Setting Up Authentication Routes

Routes are like the hallways in our house - they direct traffic to the right rooms:


// routes/auth.js
const router = require('express').Router();
const User = require('../models/user');

router.post('/register', async (req, res) => {
    try {
        const { email, password, name } = req.body;
        const user = await User.create({ email, password, name });
        req.session.userId = user.id;
        res.json({ message: 'Registration successful' });
    } catch (error) {
        res.status(400).json({ error: error.message });
    }
});

router.post('/login', async (req, res) => {
    try {
        const { email, password } = req.body;
        const user = await User.findOne({ where: { email } });
        
        if (!user) {
            return res.status(401).json({ error: 'Invalid credentials' });
        }

        const validPassword = await user.validatePassword(password);
        if (!validPassword) {
            return res.status(401).json({ error: 'Invalid credentials' });
        }

        req.session.userId = user.id;
        res.json({ message: 'Login successful' });
    } catch (error) {
        res.status(400).json({ error: error.message });
    }
});

router.post('/logout', (req, res) => {
    req.session.destroy();
    res.json({ message: 'Logged out successfully' });
});

module.exports = router;

Creating the Main Application File

The app.js file is like the central control system of our house, connecting all the pieces together:


// app.js
const express = require('express');
const session = require('express-session');
const cookieParser = require('cookie-parser');
require('dotenv').config();

const sequelize = require('./config/database');
const authRoutes = require('./routes/auth');

const app = express();

app.use(express.json());
app.use(cookieParser());
app.use(session({
    secret: process.env.SESSION_SECRET,
    resave: false,
    saveUninitialized: false,
    cookie: { secure: false } // Set to true in production with HTTPS
}));

app.use('/auth', authRoutes);

// Test route
app.get('/', (req, res) => {
    res.json({ message: 'Shopping app API is running' });
});

const PORT = process.env.PORT || 3000;

async function startServer() {
    try {
        await sequelize.sync();
        console.log('Database synchronized successfully');
        
        app.listen(PORT, () => {
            console.log(`Server is running on port ${PORT}`);
        });
    } catch (error) {
        console.error('Unable to start server:', error);
    }
}

startServer();

Testing the Application

Add this script to your package.json:


{
    "scripts": {
        "dev": "nodemon app.js"
    }
}

Start the application:

npm run dev

Test the endpoints using curl or Postman:


# Register a new user
curl -X POST http://localhost:3000/auth/register \
-H "Content-Type: application/json" \
-d '{"email":"test@example.com","password":"password123","name":"Test User"}'

# Login
curl -X POST http://localhost:3000/auth/login \
-H "Content-Type: application/json" \
-d '{"email":"test@example.com","password":"password123"}'

Next Steps and Security Considerations

Now that we have our basic authentication system in place, consider implementing:

- Password reset functionality

- Email verification

- Rate limiting for login attempts

- CSRF protection

- Input validation middleware

- Error handling middleware

Security Best Practices

Remember to:

- Use HTTPS in production

- Set secure cookie options in production

- Implement proper logging

- Regular security audits

- Keep dependencies updated

Database Migrations: Your Schema Version Control

Think of migrations as your database's timeline - like a journal that records every structural change to your database. Just as a historian documents important events chronologically, migrations help us track and manage database changes over time.

Setting Up Sequelize CLI


npm install --save-dev sequelize-cli
npx sequelize-cli init

This creates several directories:


shopping_app/
  ├── config/
  │   └── config.json      # Database configuration
  ├── migrations/          # Migration files
  ├── seeders/            # Seed files
  └── models/             # Model files

Creating Your First Migration

Let's create a migration for our users table:


npx sequelize-cli migration:generate --name create-users-table

// migrations/XXXXXXXXXXXXXX-create-users-table.js
'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('Users', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      email: {
        type: Sequelize.STRING,
        allowNull: false,
        unique: true
      },
      password: {
        type: Sequelize.STRING,
        allowNull: false
      },
      name: {
        type: Sequelize.STRING,
        allowNull: false
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });

    // Adding indexes for better query performance
    await queryInterface.addIndex('Users', ['email']);
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('Users');
  }
};

Products Table Migration


npx sequelize-cli migration:generate --name create-products-table

// migrations/XXXXXXXXXXXXXX-create-products-table.js
'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('Products', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      name: {
        type: Sequelize.STRING,
        allowNull: false
      },
      description: {
        type: Sequelize.TEXT
      },
      price: {
        type: Sequelize.DECIMAL(10, 2),
        allowNull: false
      },
      stock: {
        type: Sequelize.INTEGER,
        allowNull: false,
        defaultValue: 0
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('Products');
  }
};

To run migrations:


npx sequelize-cli db:migrate                 # Run pending migrations
npx sequelize-cli db:migrate:undo            # Undo last migration
npx sequelize-cli db:migrate:undo:all        # Undo all migrations

Database Seeders: Populating Your Database

Seeders are like gardeners planting initial seeds in your database garden. They help you populate your database with test data or initial required data, making development and testing much easier.

Creating Seeders


npx sequelize-cli seed:generate --name demo-users

// seeders/XXXXXXXXXXXXX-demo-users.js
'use strict';
const bcrypt = require('bcrypt');

module.exports = {
  up: async (queryInterface, Sequelize) => {
    // Create a few test users
    const hashedPassword = await bcrypt.hash('password123', 10);
    await queryInterface.bulkInsert('Users', [
      {
        name: 'John Doe',
        email: 'john@example.com',
        password: hashedPassword,
        createdAt: new Date(),
        updatedAt: new Date()
      },
      {
        name: 'Jane Smith',
        email: 'jane@example.com',
        password: hashedPassword,
        createdAt: new Date(),
        updatedAt: new Date()
      }
    ]);
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.bulkDelete('Users', null, {});
  }
};

Product Seeder


npx sequelize-cli seed:generate --name demo-products

// seeders/XXXXXXXXXXXXX-demo-products.js
'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.bulkInsert('Products', [
      {
        name: 'Laptop Pro',
        description: 'High-performance laptop for professionals',
        price: 1299.99,
        stock: 50,
        createdAt: new Date(),
        updatedAt: new Date()
      },
      {
        name: 'Smartphone X',
        description: 'Latest smartphone with advanced features',
        price: 799.99,
        stock: 100,
        createdAt: new Date(),
        updatedAt: new Date()
      },
      {
        name: 'Wireless Headphones',
        description: 'Premium noise-cancelling headphones',
        price: 199.99,
        stock: 75,
        createdAt: new Date(),
        updatedAt: new Date()
      }
    ]);
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.bulkDelete('Products', null, {});
  }
};

Running seeders:


npx sequelize-cli db:seed:all               # Run all seeders
npx sequelize-cli db:seed:undo              # Undo last seeder
npx sequelize-cli db:seed:undo:all          # Undo all seeders

Migration and Seeder Best Practices

Think of these practices like the rules of maintaining a well-organized library:

Migration Best Practices

- Always make migrations reversible (implement both up and down methods)

- Keep migrations small and focused, like chapters in a book

- Add appropriate indexes for fields you'll query frequently

- Use appropriate field types and constraints

- Version control your migrations alongside your code

Seeder Best Practices

- Create realistic test data that represents actual use cases

- Use factories or helpers for generating large amounts of test data

- Keep development/testing seeds separate from production seeds

- Consider dependencies between tables when ordering your seeds

Development Workflow

Create a npm script for easy database reset:


// package.json
{
  "scripts": {
    "db:reset": "npx sequelize-cli db:drop && npx sequelize-cli db:create && npx sequelize-cli db:migrate && npx sequelize-cli db:seed:all"
  }
}

Handling Production Deployments

When deploying to production, consider these important points:

- Always backup your database before running migrations

- Test migrations on a staging environment first

- Use transactions for complex migrations

- Have a rollback plan for each migration

- Consider using migration tools that support zero-downtime deployments

Production Migration Script Example


// scripts/migrate-production.js
const { execSync } = require('child_process');
const fs = require('fs');

async function migrateProd() {
    try {
        // Backup database first
        execSync('pg_dump -U username -d database > backup.sql');
        
        // Run migrations
        execSync('npx sequelize-cli db:migrate');
        
        // Run only production seeds if needed
        execSync('npx sequelize-cli db:seed --seed 20240212123456-production-essential-data.js');
        
        console.log('Migration completed successfully');
    } catch (error) {
        console.error('Migration failed:', error);
        // Restore from backup if needed
        execSync('psql -U username -d database < backup.sql');
    }
}

migrateProd();