Integrating SQLite with a Restaurant Management System: Setup and Basic Operations

Learn how to integrate SQLite into a restaurant management system, covering setup, basic CRUD operations, and handling local data for orders and inventory.

In Part 1 of this series, we’ll walk you through integrating SQLite into a restaurant management system, setting the foundation for building a system that works seamlessly with the database backend. SQLite is a great choice for small-scale, local applications, especially when you need a lightweight, serverless solution to manage data like orders, inventory, and customer interactions. If you’ve jumped ahead, start from our Beginner’s Guide to Mastering SQLite.

This blog will help you set up SQLite and get started with basic database operations for your restaurant.

By the end of this blog, you’ll know how to:

• Set up SQLite for your restaurant system.

• Create and connect the database to your application.

• Perform basic CRUD operations (Create, Read, Update, Delete) for managing orders and inventory.

• Handle local database access and manage schema changes.

Step 1: Setting Up SQLite for Your Restaurant System

The first step is to set up SQLite in your application. Since SQLite is serverless, the database file is stored locally, making it ideal for a restaurant environment where your system might be running on local devices like tablets, laptops, or POS terminals.

Install SQLite for the Restaurant System

You’ll need SQLite installed and available for your web application (we’ll use Flask as an example, but you can use any framework).

1. Install Flask and SQLite:

pip install Flask SQLAlchemy

2. Set up Flask app and configure SQLite:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///restaurant.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

if __name__ == '__main__':
    app.run(debug=True)

Explanation:

• SQLAlchemy is an ORM (Object Relational Mapper) that simplifies the process of interacting with SQLite in Python.

• restaurant.db will be the SQLite database file stored locally on the system.

Step 2: Defining the Database Models

Next, you’ll define the database models for the restaurant system. We’ll need tables for orders, menu items, and inventory.

Defining the Orders Table

This table will track customer orders in the restaurant.

class Order(db.Model):
    order_id = db.Column(db.Integer, primary_key=True)
    customer_name = db.Column(db.String(100), nullable=False)
    item_id = db.Column(db.Integer, db.ForeignKey('menu_item.item_id'), nullable=False)
    quantity = db.Column(db.Integer, nullable=False)
    status = db.Column(db.String(50), default='Pending')

    def __repr__(self):
        return f"<Order {self.customer_name} - {self.status}>"

Defining the Menu Items Table

This table stores the restaurant's menu, including item names and prices.

class MenuItem(db.Model):
    item_id = db.Column(db.Integer, primary_key=True)
    item_name = db.Column(db.String(100), nullable=False)
    price = db.Column(db.Float, nullable=False)

    def __repr__(self):
        return f"<MenuItem {self.item_name}>"

Defining the Inventory Table

This table tracks the stock of each item.

class Inventory(db.Model):
    inventory_id = db.Column(db.Integer, primary_key=True)
    item_id = db.Column(db.Integer, db.ForeignKey('menu_item.item_id'), nullable=False)
    quantity_in_stock = db.Column(db.Integer, nullable=False)

    def __repr__(self):
        return f"<Inventory {self.item_id} - {self.quantity_in_stock}>"

Step 3: Performing Basic CRUD Operations

With the models defined, you can now perform CRUD operations to manage the restaurant's orders and inventory. For a deeper dive into CRUD operations and how to implement them in SQLite, check out our previous blog on CRUD operations in SQLite.

Creating an Order

@app.route('/create_order', methods=['POST'])
def create_order():
    new_order = Order(customer_name="John Doe", item_id=1, quantity=2)
    db.session.add(new_order)
    db.session.commit()
    return "Order Created!"

Reading All Orders

@app.route('/orders')
def get_orders():
    orders = Order.query.all()
    return str(orders)

Updating Order Status

@app.route('/update_order/<int:id>', methods=['POST'])
def update_order(id):
    order = Order.query.get(id)
    order.status = "Completed"
    db.session.commit()
    return "Order Updated!"

Deleting an Order

@app.route('/delete_order/<int:id>', methods=['POST'])
def delete_order(id):
    order = Order.query.get(id)
    db.session.delete(order)
    db.session.commit()
    return "Order Deleted!"

Step 4: Handling Database Schema Changes (Migrations)

As the restaurant’s menu and system evolve, you’ll need to handle database schema changes. Flask-Migrate is a great tool for this.

Install Flask-Migrate

pip install Flask-Migrate

Set Up Flask-Migrate

from flask_migrate import Migrate

migrate = Migrate(app, db)

1. Initialize Migrations:

flask db init

2. Create a Migration Script:

flask db migrate -m "Initial migration"

3. Apply the Migration:

flask db upgrade

Conclusion (Part 1)

In Part 1, we set up SQLite for the restaurant management system, created the database models for orders, menu items, and inventory, and demonstrated how to perform basic CRUD operations. We also covered Flask-Migrate for handling database schema changes.

In Part 2, we’ll dive into advanced database queries for managing inventory levels, integrating payment processing, and generating reports for restaurant admins.

Subscribe Now

Stay updated with the latest tips and tutorials for building efficient SQLite systems! Subscribe now to receive expert advice and updates directly in your inbox. Don’t miss out on the next parts of the Restaurant Management System series and more. Join our community at the SQLite Forum to ask questions, share experiences, and connect with fellow developers!