Skip to content
BranchesTags

Repository files navigation

Open in GitHub Codespaces Open in Dev Container

Library App

A full-stack library management application with AI-powered book recommendations, built to demonstrate SQL Server 2025's native VECTOR data type, Node.js, React, and GitHub Copilot integration with the MSSQL extension.

Architecture

graph LR
    A["React Frontend<br/>(port 3001)<br/>MUI + Vite"] -->|REST API| B["Express Backend<br/>(port 3000)<br/>Sequelize ORM"]
    B -->|tedious| C["SQL Server 2025<br/>VECTOR support<br/>books, authors,<br/>books_authors"]
    A -->|/chat| D["FastAPI AI/RAG<br/>(port 8000)<br/>Ollama LLM +<br/>Embeddings"]
    D -->|mssql_python| C
Loading

Tech Stack

Layer Technology Details
Frontend React 18, MUI v7, Vite 7 Single-page app with axios for API calls
Backend Node.js, Express 4.19, Sequelize 6.37 REST API with code-first schema
AI/RAG Service Python, FastAPI, LangChain Semantic search + natural language responses
Database SQL Server 2025 Native VECTOR(768) for embeddings
Embeddings Ollama nomic-embed-text 768-dimensional vectors
LLM Ollama llama3.2:3b Natural language generation for chat
Infrastructure Terraform Microsoft Fabric provisioning (optional)
Auth Microsoft Entra ID Fabric mode only, via @azure/identity

Prerequisites

  • VS Code with the following extensions:
  • Node.js >= 18 and npm (download)
  • Python >= 3.8 and pip (download)
  • Docker (recommended for running SQL Server locally) (download)
  • Git

For AI features:

Optional:

  • Terraform >= 1.5.7 (for Microsoft Fabric infrastructure)
  • Azure CLI (for Entra ID authentication with Fabric)

Tip

Use the Open in GitHub Codespaces or Open in Dev Container buttons above to get a pre-configured environment with all prerequisites installed.

Project Structure

library-app/
├── app/
│   ├── backend/                        # Node.js/Express API server
│   │   ├── ai/                         # Python AI services
│   │   │   ├── chat_service.py         # FastAPI RAG semantic search service
│   │   │   ├── backfill_embeddings.py  # Embedding generation script
│   │   │   ├── requirements.txt        # Python dependencies
│   │   │   ├── .env                    # Python DB connection config
│   │   │   └── .env.example            # Template
│   │   ├── config/                     # Database configuration
│   │   │   ├── db.js                   # Sequelize initializer (Fabric + local)
│   │   │   ├── config.js               # Sequelize CLI config
│   │   │   ├── .env                    # Backend DB credentials
│   │   │   └── .env.example            # Template
│   │   ├── models/                     # Sequelize ORM models
│   │   │   ├── book.model.js           # Book model
│   │   │   ├── author.model.js         # Author model
│   │   │   ├── books_authors.model.js  # Junction table
│   │   │   └── initModels.js           # Model registration + associations
│   │   ├── routes/                     # Express route handlers
│   │   │   ├── Book.js                 # /books endpoints
│   │   │   ├── Author.js               # /authors endpoints
│   │   │   └── BooksAuthors.js         # /books_authors endpoints
│   │   ├── seeders/                    # Sequelize seeders (test data)
│   │   ├── scripts/                    # Utility scripts (drop tables)
│   │   ├── index.jsx                   # Express entry point
│   │   └── package.json
│   └── frontend/
│       └── library-frontend/           # React SPA
│           ├── src/
│           │   ├── ModernApp.jsx        # Main app component
│           │   ├── ModernApp.css       # Styles
│           │   └── index.jsx           # Entry point
│           └── package.json
├── docs/                               # Documentation
│   ├── PRD.md                          # Product Requirements Document
│   └── demos/                          # Step-by-step demo walkthroughs
│       ├── github-copilot-demo.html    # GitHub Copilot + MSSQL extension demo
│       ├── library-ai-ready-demo.html  # AI-ready demo (VECTOR + RAG)
│       ├── library-demo-flow.html      # End-to-end demo flow
│       └── schema-designer-demo.html   # Schema Designer demo
├── infrastructure/                     # Terraform files for Microsoft Fabric
├── .devcontainer/
│   └── devcontainer.json               # Dev container / Codespaces configuration
├── .github/
│   └── copilot-instructions.md         # GitHub Copilot custom instructions
├── AGENTS.md                           # Cross-platform AI agent instructions
├── CLAUDE.md                           # Claude Code project context
├── .cursorrules                        # Cursor AI instructions
└── README.md                           # This file

Quick Start (Local SQL Server with Docker)

1. Clone the Repository

git clone <repository-url>
cd library-app

2. Install Dependencies

# Backend
cd app/backend
npm install

# Frontend
cd ../frontend/library-frontend
npm install

3. Create SQL Server 2025 Container

Use the MSSQL extension for VS Code to create and start the SQL Server container:

  1. Open the SQL Server view in the VS Code sidebar (database icon)
  2. Click Add Connection > Create Local SQL Server
  3. The extension will pull the SQL Server 2025 Docker image and create the container for you

Note

Make sure Docker Desktop is running before creating the container. The MSSQL extension handles all Docker configuration automatically.

4. Configure Environment Variables

Copy the .env.example template and fill in your credentials:

cp app/backend/config/.env.example app/backend/config/.env

Edit app/backend/config/.env with your local SQL Server settings:

DB_SERVER=<your-server,your-port>
DB_USER=<your-username>
DB_PASSWORD=<your-password>
DB_DATABASE=Library

Tip

You can find your connection details in the MSSQL extension's connection properties. The default port is 1433 and the default username is sa.

5. Create the Database

cd app/backend
npx sequelize-cli db:create

This creates the empty Library database using your .env configuration.

Note

This command works for local SQL Server only. For Microsoft Fabric, create the database via Terraform, Azure portal, or Fabric portal (see Microsoft Fabric Setup).

6. Start the Backend

cd app/backend
npm start

The backend starts on http://localhost:3000. On first run, Sequelize automatically creates all tables.

7. Seed the Database

In a new terminal:

cd app/backend
npx sequelize-cli db:seed:all

This populates the database with 29 authors (with profile photos), ~200 books, all book-author associations, and the GetBooksWithAuthors stored procedure.

8. Start the Frontend

In a new terminal:

cd app/frontend/library-frontend
npm start

The frontend opens at http://localhost:3001.

AI/RAG Setup (Semantic Search)

The AI service adds semantic book search using SQL Server 2025's VECTOR data type, Ollama embeddings, and a RAG (Retrieval-Augmented Generation) pipeline.

1. Install Ollama

# macOS/Linux
curl -fsSL https://ollama.com/install.sh | sh

# Windows: download from https://ollama.com/download

2. Start Ollama

Start the Ollama server in a dedicated terminal and keep it running:

ollama serve

Important

The Ollama server must be running before you can pull models or use the AI service. On systems without systemd (e.g., GitHub Codespaces), you must start it manually each time.

3. Pull the Required Models

In a new terminal (while ollama serve is still running):

ollama pull nomic-embed-text   # Embedding model (768-dimensional)
ollama pull llama3.2:3b         # LLM for natural language responses

4. Install Python Dependencies

cd app/backend/ai
pip install -r requirements.txt

5. Configure the Python Environment

Copy the .env.example template and fill in your credentials:

cp app/backend/ai/.env.example app/backend/ai/.env

Edit app/backend/ai/.env with your SQL Server connection string:

MSSQL_CONNECTION_STRING=Server=<your-server>;Database=Library;UID=<your-username>;PWD=<your-password>;TrustServerCertificate=yes;

Important

The Python service uses ADO.NET-style connection strings with UID and PWD (not User Id and Password). This is a different format from the Node.js backend's .env file.

6. Generate Embeddings

cd app/backend/ai
python backfill_embeddings.py

This adds the description_embedding VECTOR(768) column if it is missing, then generates 768-dimensional embeddings for every book with the nomic-embed-text model and stores them. Re-running it is safe.

Note

On vector indexes. A vector index is optional here, and on SQL Server 2025 it is a trade. CREATE INDEX does not work on a vector column at all:

Msg 1978: Column 'description_embedding' in table 'dbo.books' is of a type
that is invalid for use as a key column in an index or statistics.

The correct statement is CREATE VECTOR INDEX, which on SQL Server 2025 also needs PREVIEW_FEATURES switched on:

ALTER DATABASE SCOPED CONFIGURATION SET PREVIEW_FEATURES = ON;

CREATE VECTOR INDEX IX_books_description_embedding
ON dbo.books(description_embedding)
WITH (METRIC = 'cosine', TYPE = 'DiskANN');

But on SQL Server 2025 that makes the table read-only, so POST /books starts failing with Msg 42231: Data modification statement failed because table 'books' has a vector index on it. Full DML alongside a vector index is available on Azure SQL Database and SQL database in Microsoft Fabric with the latest index version, not on a local SQL Server 2025 container.

VECTOR_DISTANCE works without an index, as an exact scan, which is ample for a 194-row library. This is why the setup script creates the column and not the index.

7. Start the Chat Service

cd app/backend/ai
python chat_service.py

The service starts on http://localhost:8000. Interactive API docs are available at http://localhost:8000/docs.

9. Test Semantic Search

curl -X POST http://localhost:8000/chat \
  -H "Content-Type: application/json" \
  -d '{"question": "I want to read science fiction about space exploration"}'

The chat widget is also integrated into the React frontend (bottom-right corner).

RAG Intelligence

The chat service uses smart query detection and similarity thresholds to deliver relevant results:

  • Category detection: Recognizes genre names (e.g., "science fiction", "fantasy") and common aliases ("sci-fi", "thriller")
  • Topic-to-genre mapping: Common topics auto-map to genres (e.g., "space exploration" → Science Fiction, "dragons" → Fantasy)
  • Author detection: Matches full names or last names (e.g., "Asimov", "books by Clarke")
  • Similarity thresholds: Category queries (0.20), author queries (0.25), general queries (0.60) — stricter for general queries to avoid irrelevant recommendations
  • Conversational responses: All responses use a warm, librarian tone — including fallbacks when the LLM fails or no good matches are found
  • Latency breakdown: The terminal logs timing for each stage (embedding, sql_vector_search in ms, llm, total)

Performance Tuning

The AI service performance can be tuned via app/backend/ai/.env:

Variable Default Description
LLM_NUM_PREDICT 150 Max tokens to generate (lower = faster)
OLLAMA_NUM_THREAD 0 CPU threads for inference (0 = auto-detect)
LLM_TEMPERATURE 0.1 LLM creativity (lower = more deterministic)

Tip

For live demos, set LLM_NUM_PREDICT=100 for faster responses (~3-5s). Limit SQL Server container CPU/memory to free resources for Ollama inference.

Microsoft Fabric Setup (Alternative)

If you want to use Microsoft Fabric SQL Database instead of a local Docker container:

1. Provision Infrastructure with Terraform

cd infrastructure
terraform init
terraform plan -out main.tfplan
terraform apply main.tfplan

Note

Update infrastructure/terraform.tfvars with your Fabric capacity name. To find it: go to your Fabric workspace settings, then click License info.

2. Configure Environment for Fabric

Create app/backend/config/.env with:

DB_CONNECTION_STRING=mssql://<your-fabric-server>.database.fabric.microsoft.com:1433/<your-db>?encrypt=true&trustServerCertificate=false

Note

  • The sequelize-cli db:create command does not work for Fabric SQL Databases. Create the database via Terraform, Azure portal, or Fabric portal.
  • Fabric connections use Microsoft Entra ID (Azure Active Directory) authentication via DefaultAzureCredential. Make sure you are logged in with the Azure CLI (az login).

API Reference

Backend REST API (port 3000)

Method Endpoint Description Body / Query
GET /books List all books with authors ?search=keyword (optional)
POST /books Create a book { title, year, pages, image_url, category, authorId }
PUT /books/:id Update a book { title, year, pages, image_url, category }
DELETE /books/:id Delete a book and its associations --
GET /authors List all authors with their books --
POST /authors Create an author { first_name, middle_name, last_name, image_url }
DELETE /authors/:id Delete an author --
GET /books_authors List all book-author associations --
POST /books_authors Create a book-author association { book_id, author_id }
DELETE /books_authors Delete a book-author association { book_id, author_id }

AI Chat Service (port 8000)

Method Endpoint Description Body
POST /chat Semantic search + RAG response { question, conversation_history? }
GET / Service info --
GET /health Health check (verifies DB connection) --
GET /docs Swagger UI --

Example chat response:

{
  "question": "I want to read science fiction about space exploration",
  "response": "Based on our library collection, I recommend...",
  "results": [
    {
      "id": 9,
      "title": "2001: A Space Odyssey",
      "category": "Science Fiction",
      "year": 1968,
      "author": "Arthur C. Clarke",
      "similarity_score": 0.89
    }
  ]
}

Database Schema

erDiagram
    books ||--o{ books_authors : ""
    authors ||--o{ books_authors : ""

    books {
        int id PK
        nvarchar title
        int year
        int pages
        nvarchar image_url
        nvarchar category
        vector768 description_embedding
    }

    authors {
        int id PK
        nvarchar first_name
        nvarchar middle_name
        nvarchar last_name
        nvarchar image_url
    }

    books_authors {
        int book_id FK
        int author_id FK
    }
Loading

Seed data: 29 authors (Asimov, Clarke, Dick, Wells, Verne, Herbert, Tolkien, and more -- each with Wikipedia profile photos) and ~200 books across 16 categories including Science Fiction, Fantasy, Cyberpunk, Dystopian, Non-Fiction, and more.

Documentation & Demo Guides

Product Requirements Document

The PRD defines the full scope of the application — data model, user stories, API endpoints, seed data, and non-goals. Use it as the source of truth for what the app should and shouldn't do.

GitHub Copilot Custom Instructions

The project includes a GitHub Copilot custom instructions file that teaches GitHub Copilot about the project's conventions: SQL Server only, Sequelize patterns, lowercase snake_case naming, global.models access, and more. This file is automatically loaded by GitHub Copilot in VS Code whenever you work in this repository, helping it generate code that follows the project's rules without you having to repeat them.

Tip

Additional AI agent instruction files are included for other tools: AGENTS.md (Codex, Cline, Windsurf, Gemini CLI, Aider), CLAUDE.md (Claude Code), and .cursorrules (Cursor).

Demo Walkthroughs

Interactive HTML guides are available in docs/demos/ for reproducing live presentations. See docs/demos/README.md for full details.

Demo Duration File Description
Schema Designer + GitHub Copilot ~15 min schema-designer-demo.html Goes from hardcoded frontend data to a fully connected stack using the Schema Designer, GitHub Copilot, and Data API Builder
Making the App AI-Ready ~15 min library-ai-ready-demo.html Spec-driven development (PRD + custom instructions + cross-platform AI agent files) and AI-powered semantic search with SQL Server 2025's VECTOR data type

Open any .html file in a browser to follow along step-by-step with copyable code snippets.

Cleanup / Reset

Drop All Tables (Any Database)

cd app/backend
node ./scripts/dropAllTables.js

Drop Database (Local SQL Server Only)

cd app/backend
npx sequelize-cli db:drop

Note

This command does not work for Microsoft Fabric SQL Database. For Fabric, drop the database manually via the Azure/Fabric portal.

Destroy Fabric Infrastructure

cd infrastructure
terraform destroy

Important

This permanently deletes all provisioned Fabric resources.

Troubleshooting

SQL Server Connection Issues

  • Docker not running: Verify the container is running with docker ps. Restart with docker start sql2025.
  • Port conflict: Ensure port 1433 is not in use by another process.
  • Fabric connection: Verify DB_CONNECTION_STRING format. Ensure you are logged in with az login.

Different Connection String Formats

The Node.js backend and Python AI service use different .env formats:

Service File Format
Node.js backend app/backend/config/.env Separate vars: DB_SERVER, DB_USER, DB_PASSWORD, DB_DATABASE
Python AI service app/backend/ai/.env Single string: MSSQL_CONNECTION_STRING=Server=...;Database=...;UID=...;PWD=...;

Sequelize Sync Errors

  • First run may show warnings as tables are being created -- this is normal.
  • If schema changes fail: run node ./scripts/dropAllTables.js from app/backend, then restart the backend.

Ollama Issues

  • Ensure ollama serve is running before starting the AI service.
  • Verify models are available: ollama list
  • Required models: nomic-embed-text (embeddings) and llama3.2:3b (LLM)

CORS Errors

  • The Express backend allows all origins via the cors() middleware.
  • The Python AI service allows requests from http://localhost:3001 and http://127.0.0.1:3001 by default. Override with the CORS_ORIGINS environment variable in app/backend/ai/.env.

Vector Search Returns No Results

Verify embeddings exist in the database:

SELECT TOP 5 id, title,
  CASE WHEN description_embedding IS NOT NULL
       THEN 'Embedding exists'
       ELSE 'No embedding'
  END AS EmbeddingStatus
FROM books;

If embeddings are missing, re-run the backfill script:

cd app/backend/ai
python backfill_embeddings.py

Vector Dimension Mismatch

If you change the embedding model, the vector dimensions must match:

  1. Check your model's output dimension
  2. Drop and re-create the description_embedding column with the correct size
  3. Re-run the backfill script

Resources

Learn more about the tools and features showcased in this project:

About

This is sample app using Azure SQL Database and Node.js for the backend and React for the frontend

Topics

devcontainergithub-copilotmicrosoft-fabricnodejsreactsql-server-2025terraformvector-search

Resources

Readme
MIT license
Activity

Stars

7 stars

Watchers

1 watching

Forks

1 fork
Report repository

Releases

Packages

Contributors

Languages