template-express-oauth-sqlite by granular-software - MCP Server

sqlite

A mcpresso MCP server

Quick Start

Install dependencies
```
npm install
```

Set up environment variables

cp .env.example .env
# Edit .env with your configuration

Initialize the database
```
npm run db:init
```
This will create the SQLite database with all necessary tables and indexes.
Start development server
```
npm run dev
```
Build for production
```
npm run build
npm start
```

Database Setup

This template uses SQLite for authentication data storage (users, sessions, tokens, etc.). The database file is created automatically on init, and the init script creates the full schema.

Initialize the schema:

npm run db:init

Database Structure

The initialization script creates the following tables:

oauth_users - User accounts (username/email, hashed_password, scopes, profile)
oauth_clients - OAuth client registry (redirect URIs, scopes, grant types)
oauth_authorization_codes - Authorization codes (with PKCE fields)
oauth_access_tokens - Access tokens with expiry
oauth_refresh_tokens - Refresh tokens with expiry
notes - Example resource (user-authored notes)

Database Features

✅ SQLite file-based storage - No external database required
✅ Foreign key constraints - Maintains data integrity
✅ Optimized indexes - Fast lookups for common queries
✅ Automatic timestamps - Created/updated tracking
✅ OAuth integration - Session and token management

Database Location

Default path: data/app.db
To customize, set DATABASE_PATH in .env before running init.

Features

OAuth2.1 authentication with SQLite
User management and sessions
Notes resource with author relationships
TypeScript support
Development and production builds
Environment variable configuration

Project Structure

src/
├── server.ts          # Main server file
├── auth/              # OAuth configuration
│   └── oauth.ts
├── resources/         # MCP resources
│   ├── schemas/       # Resource schemas
│   │   └── Note.ts    # Note data model
│   └── handlers/      # Resource handlers
│       └── note.ts    # Notes with author relationships
└── storage/           # Database layer
    └── sqlite-storage.ts

Environment Variables

Variable	Description	Required	Default
PORT	Server port	No	3000
SERVER_URL	Base URL of your server	Yes	-
JWT_SECRET	Secret key for JWT tokens	Yes	-
DATABASE_PATH	SQLite database file path	No	data/app.db

JWT Secret

Generate a secure JWT secret for token signing.

Option A — script (uses openssl under the hood):

npm run secret:generate

Option B — manual (with openssl):

JWT_SECRET=$(openssl rand -hex 64)
echo "JWT_SECRET=$JWT_SECRET" >> .env   # or replace existing JWT_SECRET in .env

Keep this value secret. Rotating it will invalidate existing tokens.

Development

npm run dev - Start development server with hot reload
npm run build - Build for production
npm run typecheck - Type check without building
npm run db:init - Initialize SQLite database and full schema
npm run secret:generate - Generate secure JWT secret

User Management

Create and manage users with secure password hashing:

npm run user:create - Create a user with default credentials
npm run user:create <username> <email> <password> - Create a user with custom credentials
npm run user:test-password - Test password verification functionality
npm run user:test-auth - Test OAuth authentication flow

For detailed user management documentation, see .

License

MIT