Building a Full-Stack Application with Express, SQLite3, and Sequelize

Introduction and Project Setup

Think of building a web application like constructing a modern office building. Just as a building needs a strong foundation, proper utilities, and well-organized spaces, our application needs a solid structure, efficient data management, and well-organized code. Let's start by creating this foundation.

First, let's create our project structure. This is like creating the blueprint for our building:

# Create project and all necessary directories
mkdir my_project
cd my_project
mkdir -p config db/{models,migrations,seeders} src/{routes/api,middleware,controllers}

# Initialize project and install dependencies
npm init -y
npm install express sequelize sqlite3 dotenv cors helmet morgan express-validator
npm install --save-dev nodemon sequelize-cli

# Your project structure will look like this:
your_project/
  ├── config/                         # Configuration settings
  │   ├── config.js                   # Database configuration
  │   └── database.js                 # Database connection setup
  ├── db/                             # Database-related files
  │   ├── models/                     # Data models
  │   │   └── index.js                # Models initialization
  │   ├── migrations/                 # Schema changes
  │   └── seeders/                    # Initial data
  ├── src/                            # Application source code
  │   ├── routes/                     # URL endpoints
  │   │   ├── index.js                # Main router
  │   │   └── api/                    # API routes
  │   │       ├── users.js            # User routes
  │   │       └── auth.js             # Auth routes
  │   ├── middleware/                 # Express middleware
  │   │   ├── error.js                # Error handling
  │   │   └── auth.js                 # Auth middleware
  │   ├── controllers/                # Business logic
  │   │   └── userController.js       # User operations
  │   └── app.js                      # Application entry
  ├── .env                            # Environment variables
  └── .sequelizerc                    # Sequelize CLI config
            

Initial Configuration

Just as a building needs proper utility connections before it can function, our application needs proper configuration. Let's set up our core configuration files:

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

# .env
NODE_ENV=development
PORT=3000
DB_NAME=development_db
DB_USER=your_username
DB_PASSWORD=your_password
DB_HOST=localhost
JWT_SECRET=your_jwt_secret
            

Set up your database configuration:

// config/config.js
require('dotenv').config();

module.exports = {
    development: {
        dialect: 'sqlite',
        storage: './db/development.sqlite',
        logging: console.log,
        seederStorage: 'sequelize',
        seederStorageTableName: 'SequelizeSeeds'
    },
    test: {
        dialect: 'sqlite',
        storage: ':memory:',
        logging: false
    },
    production: {
        dialect: 'sqlite',
        storage: process.env.DB_STORAGE || './db/production.sqlite',
        logging: false
    }
};

// config/database.js
const { Sequelize } = require('sequelize');
const config = require('./config')[process.env.NODE_ENV || 'development'];

const sequelize = new Sequelize(config);

module.exports = sequelize;
            

Configure Sequelize CLI by creating .sequelizerc:

// .sequelizerc
const path = require('path');

module.exports = {
    'config': path.resolve('config', 'config.js'),
    'models-path': path.resolve('db', 'models'),
    'seeders-path': path.resolve('db', 'seeders'),
    'migrations-path': path.resolve('db', 'migrations')
};
            

Setting Up the Express Application

Now let's set up our main application file. Think of this as installing the building's central control system:

// src/app.js
require('dotenv').config();
const express = require('express');
const cors = require('cors');
const helmet = require('helmet');
const morgan = require('morgan');
const { sequelize } = require('../db/models');
const routes = require('./routes');
const errorMiddleware = require('./middleware/error');

const app = express();

// Middleware
app.use(helmet());              // Security headers
app.use(cors());               // Enable CORS
app.use(morgan('dev'));        // Request logging
app.use(express.json());       // Parse JSON bodies

// Routes
app.use('/api', routes);

// Error handling
app.use(errorMiddleware);

// Database connection and server startup
const PORT = process.env.PORT || 3000;

async function startServer() {
    try {
        await sequelize.authenticate();
        console.log('Database connection established');
        
        app.listen(PORT, () => {
            console.log(`Server running on port ${PORT}`);
        });
    } catch (error) {
        console.error('Unable to start server:', error);
        process.exit(1);
    }
}

startServer();
            

Creating Database Models

Models are like the blueprints for different types of data in our application. Let's create a User model as an example:

// db/models/index.js
const fs = require('fs');
const path = require('path');
const Sequelize = require('sequelize');
const process = require('process');
const basename = path.basename(__filename);
const env = process.env.NODE_ENV || 'development';
const config = require('../../config/config')[env];
const db = {};

let sequelize;
if (config.use_env_variable) {
    sequelize = new Sequelize(process.env[config.use_env_variable], config);
} else {
    sequelize = new Sequelize(
        config.database, 
        config.username, 
        config.password, 
        config
    );
}

fs.readdirSync(__dirname)
    .filter(file => {
        return (
            file.indexOf('.') !== 0 &&
            file !== basename &&
            file.slice(-3) === '.js' &&
            file.indexOf('.test.js') === -1
        );
    })
    .forEach(file => {
        const model = require(path.join(__dirname, file))(
            sequelize, 
            Sequelize.DataTypes
        );
        db[model.name] = model;
    });

Object.keys(db).forEach(modelName => {
    if (db[modelName].associate) {
        db[modelName].associate(db);
    }
});

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

module.exports = db;

// db/models/user.js
module.exports = (sequelize, DataTypes) => {
    const User = sequelize.define('User', {
        username: {
            type: DataTypes.STRING,
            allowNull: false,
            unique: true,
            validate: {
                len: [3, 30]
            }
        },
        email: {
            type: DataTypes.STRING,
            allowNull: false,
            unique: true,
            validate: {
                isEmail: true
            }
        },
        status: {
            type: DataTypes.ENUM('active', 'inactive'),
            defaultValue: 'active'
        }
    }, {
        timestamps: true,
        paranoid: true
    });

    User.associate = (models) => {
        // Define associations here
    };

    return User;
};
            

Setting Up Routes and Controllers

Routes direct traffic in our application, while controllers handle the specific logic for each route. Let's set these up:

// src/routes/index.js
const express = require('express');
const userRoutes = require('./api/users');
const authRoutes = require('./api/auth');

const router = express.Router();

router.use('/users', userRoutes);
router.use('/auth', authRoutes);

module.exports = router;

// src/routes/api/users.js
const express = require('express');
const { body } = require('express-validator');
const userController = require('../../controllers/userController');
const authMiddleware = require('../../middleware/auth');

const router = express.Router();

router.get('/', authMiddleware, userController.getAllUsers);
router.post('/',
    [
        body('username').isLength({ min: 3 }).trim().escape(),
        body('email').isEmail().normalizeEmail(),
        body('status').isIn(['active', 'inactive'])
    ],
    userController.createUser
);
router.get('/:id', authMiddleware, userController.getUser);
router.put('/:id', authMiddleware, userController.updateUser);
router.delete('/:id', authMiddleware, userController.deleteUser);

module.exports = router;

// src/controllers/userController.js
const { User } = require('../../db/models');
const { validationResult } = require('express-validator');

const userController = {
    // Define all controller methods
    getAllUsers: async (req, res, next) => {
        try {
            const users = await User.findAll({
                attributes: ['id', 'username', 'email', 'status']
            });
            res.json(users);
        } catch (error) {
            next(error);
        }
    },

    createUser: async (req, res, next) => {
        try {
            const errors = validationResult(req);
            if (!errors.isEmpty()) {
                return res.status(400).json({ errors: errors.array() });
            }
            const user = await User.create(req.body);
            res.status(201).json(user);
        } catch (error) {
            next(error);
        }
    },

    getUser: async (req, res, next) => {
        try {
            const user = await User.findByPk(req.params.id);
            if (!user) {
                return res.status(404).json({ message: 'User not found' });
            }
            res.json(user);
        } catch (error) {
            next(error);
        }
    },

    updateUser: async (req, res, next) => {
        try {
            const [updated] = await User.update(req.body, {
                where: { id: req.params.id }
            });
            if (!updated) {
                return res.status(404).json({ message: 'User not found' });
            }
            const user = await User.findByPk(req.params.id);
            res.json(user);
        } catch (error) {
            next(error);
        }
    },

    deleteUser: async (req, res, next) => {
        try {
            const deleted = await User.destroy({
                where: { id: req.params.id }
            });
            if (!deleted) {
                return res.status(404).json({ message: 'User not found' });
            }
            res.status(204).send();
        } catch (error) {
            next(error);
        }
    }
};

module.exports = userController;
            

Middleware Setup

Middleware functions process requests before they reach their final handler. Let's set up some essential middleware:

// src/middleware/error.js
const errorMiddleware = (err, req, res, next) => {
    console.error(err.stack);
    
    if (err.name === 'SequelizeValidationError') {
        return res.status(400).json({
            error: 'Validation Error',
            details: err.errors.map(e => ({
                message: e.message,
                field: e.path
            }))
        });
    }

    res.status(500).json({
        error: 'Internal Server Error',
        message: process.env.NODE_ENV === 'development' 
            ? err.message 
            : 'Something went wrong'
    });
};

module.exports = errorMiddleware;

// src/middleware/auth.js
const authMiddleware = (req, res, next) => {
    const authHeader = req.headers.authorization;
    
    if (!authHeader) {
        return res.status(401).json({ 
            message: 'Authorization header missing' 
        });
    }

    // Add your authentication logic here
    next();
};

module.exports = authMiddleware;
            

Database Migrations and Seeds

Migrations keep track of database changes, while seeds provide initial data. Let's create these:

// Generate migration
npx sequelize-cli migration:generate --name create_users_table

// db/migrations/XXXXXXXXXXXXXX-create_users_table.js
module.exports = {
    up: async (queryInterface, Sequelize) => {
        await queryInterface.createTable('Users', {
            id: {
                allowNull: false,
                autoIncrement: true,
                primaryKey: true,
                type: Sequelize.INTEGER
            },
            username: {
                type: Sequelize.STRING,
                allowNull: false,
                unique: true
            },
            email: {
                type: Sequelize.STRING,
                allowNull: false,
                unique: true
            },
            status: {
                type: Sequelize.ENUM('active', 'inactive'),
                defaultValue: 'active'
            },
            createdAt: {
                allowNull: false,
                type: Sequelize.DATE
            },
            updatedAt: {
                allowNull: false,
                type: Sequelize.DATE
            },
            deletedAt: {
                type: Sequelize.DATE
            }
        });
    },

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

// Generate seeder
npx sequelize-cli seed:generate --name demo_users

// db/seeders/XXXXXXXXXXXXX-demo_users.js
module.exports = {
    up: async (queryInterface, Sequelize) => {
        await queryInterface.bulkInsert('Users', [
            {
                username: 'john_doe',
                email: 'john@example.com',
                status: 'active',
                createdAt: new Date(),
                updatedAt: new Date()
            }
        ], {});
    },

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

Running the Application

Update your package.json with these scripts:

{
  "scripts": {
    "start": "node src/app.js",
    "dev": "nodemon src/app.js",
    "migrate": "sequelize-cli db:migrate",
    "migrate:undo": "sequelize-cli db:migrate:undo",
    "migrate:undo:all": "sequelize-cli db:migrate:undo:all",
    "seed": "sequelize-cli db:seed:all",
    "seed:undo": "sequelize-cli db:seed:undo",
    "seed:undo:all": "sequelize-cli db:seed:undo:all"
  }
}
            

Now let's run the application and test our setup:

# Run migrations
npm run migrate

# Seed the database
npm run seed

# Start the development server
npm run dev
            

Testing Your API

Let's test our implementation with example requests. Think of this as doing a final inspection of our building before occupancy. We'll test each major function to ensure everything works as expected:

# Create a new user
curl -X POST http://localhost:3000/api/users \
  -H "Content-Type: application/json" \
  -d '{
    "username": "jane_smith",
    "email": "jane@example.com",
    "status": "active"
  }'

# Get all users
curl http://localhost:3000/api/users

# Get a specific user
curl http://localhost:3000/api/users/1

# Update a user
curl -X PUT http://localhost:3000/api/users/1 \
  -H "Content-Type: application/json" \
  -d '{
    "status": "inactive"
  }'

# Delete a user
curl -X DELETE http://localhost:3000/api/users/1
            

Best Practices and Tips

As you continue developing your application, keep these important practices in mind:

Error Handling:

// Always use try-catch blocks in async functions
async function someFunction() {
    try {
        await someAsyncOperation();
    } catch (error) {
        // Log the error for debugging
        console.error('Operation failed:', error);
        
        // Rethrow with meaningful message
        throw new Error('Failed to perform operation');
    }
}
            

Database Operations:

// Use transactions for multiple operations
async function createUserWithProfile(userData, profileData) {
    const t = await sequelize.transaction();
    try {
        const user = await User.create(userData, { transaction: t });
        await Profile.create({ ...profileData, userId: user.id }, { transaction: t });
        await t.commit();
        return user;
    } catch (error) {
        await t.rollback();
        throw error;
    }
}
            

Route Organization:

// Group related routes together
router.route('/users')
    .get(userController.getAllUsers)
    .post(validateUser, userController.createUser);

router.route('/users/:id')
    .get(userController.getUser)
    .put(validateUser, userController.updateUser)
    .delete(userController.deleteUser);
            

Common Pitfalls and Solutions

Here are some common challenges you might face and how to address them:

Handling Asynchronous Operations:

// Incorrect
app.get('/users', (req, res) => {
    const users = User.findAll();  // This returns a Promise!
    res.json(users);  // Will send unresolved Promise
});

// Correct
app.get('/users', async (req, res, next) => {
    try {
        const users = await User.findAll();
        res.json(users);
    } catch (error) {
        next(error);
    }
});
            

Managing Database Connections:

// Add to your app.js
process.on('SIGINT', async () => {
    try {
        await sequelize.close();
        console.log('Database connection closed.');
        process.exit(0);
    } catch (error) {
        console.error('Error closing database connection:', error);
        process.exit(1);
    }
});
            

Sample Non-API Routes

Here's an example of a simple health check route to verify your application's status:

const express = require('express');
        const router = express.Router();
        
        router.get('/', (req, res) => {
            res.json({
                 'Welcome to our application',
                
            });
        });
        
        // You can add other non-API routes here
        router.get('/health', (req, res) => {
            res.json({ status: 'healthy' });
        });
        
        module.exports = router;
        

Make sure to add app.use('/', mainRoutes); and const mainRoutes = require('./routes/main'); to the src/app.js

Next Steps and Further Learning

To enhance your application, consider exploring these advanced topics:

• Authentication and Authorization: Implement JWT or session-based authentication
• API Documentation: Add Swagger/OpenAPI documentation
• Testing: Implement unit and integration tests
• Caching: Add Redis for improved performance
• Monitoring: Implement logging and monitoring solutions
• Deployment: Set up CI/CD pipelines and deployment strategies

Maintenance and Scaling

As your application grows, consider these practices:

• Regular dependency updates and security audits
• Database indexing and query optimization
• Implementation of rate limiting and request throttling
• Load balancing and horizontal scaling strategies
• Regular database backups and recovery procedures
• Performance monitoring and optimization