mcp_sql/
demo.rs

1use sqlx::any::AnyPoolOptions;
2use sqlx::AnyPool;
3
4/// Creates an in-memory SQLite database with sample tables for demo mode.
5pub async fn create_demo_database() -> Result<AnyPool, sqlx::Error> {
6    let pool = AnyPoolOptions::new()
7        .max_connections(1)
8        .connect("sqlite::memory:")
9        .await?;
10
11    sqlx::raw_sql(
12        "CREATE TABLE users (
13            id INTEGER PRIMARY KEY AUTOINCREMENT,
14            name TEXT NOT NULL,
15            email TEXT NOT NULL,
16            role TEXT NOT NULL DEFAULT 'user',
17            created_at TEXT NOT NULL DEFAULT (datetime('now'))
18        );
19
20        CREATE TABLE posts (
21            id INTEGER PRIMARY KEY AUTOINCREMENT,
22            user_id INTEGER NOT NULL,
23            title TEXT NOT NULL,
24            body TEXT,
25            status TEXT NOT NULL DEFAULT 'draft',
26            created_at TEXT NOT NULL DEFAULT (datetime('now')),
27            FOREIGN KEY (user_id) REFERENCES users(id)
28        );
29
30        CREATE TABLE comments (
31            id INTEGER PRIMARY KEY AUTOINCREMENT,
32            post_id INTEGER NOT NULL,
33            user_id INTEGER NOT NULL,
34            body TEXT NOT NULL,
35            created_at TEXT NOT NULL DEFAULT (datetime('now')),
36            FOREIGN KEY (post_id) REFERENCES posts(id),
37            FOREIGN KEY (user_id) REFERENCES users(id)
38        );
39
40        INSERT INTO users (name, email, role) VALUES
41            ('Alice Chen', 'alice@example.com', 'admin'),
42            ('Bob Smith', 'bob@example.com', 'user'),
43            ('Carol Davis', 'carol@example.com', 'user'),
44            ('Dan Wilson', 'dan@example.com', 'editor'),
45            ('Eve Taylor', 'eve@example.com', 'user'),
46            ('Frank Brown', 'frank@example.com', 'user'),
47            ('Grace Lee', 'grace@example.com', 'editor'),
48            ('Hank Moore', 'hank@example.com', 'user'),
49            ('Iris Clark', 'iris@example.com', 'user'),
50            ('Jack White', 'jack@example.com', 'admin');
51
52        INSERT INTO posts (user_id, title, body, status) VALUES
53            (1, 'Getting Started with SQL', 'A beginner guide to SQL queries.', 'published'),
54            (1, 'Advanced Joins Explained', 'Understanding INNER, LEFT, RIGHT, and FULL joins.', 'published'),
55            (2, 'My First Post', 'Hello world from Bob!', 'published'),
56            (3, 'Database Indexing Tips', 'How to speed up your queries with indexes.', 'published'),
57            (4, 'Draft: New Feature Announcement', 'Coming soon...', 'draft'),
58            (1, 'Understanding Transactions', 'ACID properties and isolation levels.', 'published'),
59            (5, 'SQL vs NoSQL', 'When to choose each approach.', 'published'),
60            (7, 'Data Modeling Best Practices', 'Normalization and denormalization trade-offs.', 'published'),
61            (2, 'Query Optimization Guide', 'Tips for writing efficient SQL.', 'draft'),
62            (3, 'Working with NULL Values', 'Common pitfalls with NULL in SQL.', 'published');
63
64        INSERT INTO comments (post_id, user_id, body) VALUES
65            (1, 2, 'Great introduction! Very helpful.'),
66            (1, 3, 'Could you add examples with subqueries?'),
67            (1, 5, 'Bookmarked for reference.'),
68            (2, 3, 'The join diagrams really helped me understand.'),
69            (2, 6, 'What about CROSS JOIN?'),
70            (3, 1, 'Welcome to the community, Bob!'),
71            (4, 1, 'Excellent tips on covering indexes.'),
72            (4, 2, 'This saved me hours of debugging slow queries.'),
73            (6, 4, 'Great explanation of isolation levels.'),
74            (7, 8, 'We switched from SQL to NoSQL and regretted it.'),
75            (7, 9, 'It really depends on the use case.'),
76            (8, 1, 'Denormalization is underrated for read-heavy workloads.'),
77            (10, 2, 'I always forget about three-valued logic with NULL.'),
78            (10, 6, 'COALESCE is my best friend now.'),
79            (10, 7, 'The IS NULL vs = NULL distinction trips everyone up.');",
80    )
81    .execute(&pool)
82    .await?;
83
84    Ok(pool)
85}
mcp_sql/demo.rs

mcp_sql/
demo.rs
