setup-database.sh•1.67 kB
#!/bin/bash
echo "🚀 Setting up PostgreSQL database for MCP server..."
# Start PostgreSQL container
echo "Starting PostgreSQL container..."
docker compose up -d
# Wait for PostgreSQL to be ready
echo "Waiting for PostgreSQL to be ready..."
sleep 5
# Create tables for MCP server
echo "Creating database tables..."
docker exec -i mcp-cole-pg-test psql -U mcp_user -d mcp_database << EOF
-- Create a test table for the MCP server to query
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
username VARCHAR(100) NOT NULL,
email VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Create a products table for testing
CREATE TABLE IF NOT EXISTS products (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price DECIMAL(10,2),
stock INTEGER DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Insert some test data
INSERT INTO users (username, email) VALUES
('alice', 'alice@example.com'),
('bob', 'bob@example.com'),
('charlie', 'charlie@example.com');
INSERT INTO products (name, price, stock) VALUES
('Laptop', 999.99, 10),
('Mouse', 29.99, 50),
('Keyboard', 79.99, 25);
-- Grant permissions
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO mcp_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO mcp_user;
EOF
echo "✅ Database setup complete!"
echo ""
echo "Connection details:"
echo " Host: localhost"
echo " Port: 5432"
echo " Database: mcp_database"
echo " Username: mcp_user"
echo " Password: mcp_password"
echo ""
echo "Connection string for .dev.vars:"
echo "DATABASE_URL=postgresql://mcp_user:mcp_password@localhost:5432/mcp_database"