{"meta":{"title":"Refactoring data access layers","intro":"Copilot Chat can suggest ways to decouple your data access code from your business logic, making an application easier to maintain and scale.","product":"GitHub Copilot","breadcrumbs":[{"href":"/en/copilot","title":"GitHub Copilot"},{"href":"/en/copilot/tutorials","title":"Tutorials"},{"href":"/en/copilot/tutorials/copilot-cookbook","title":"GitHub Copilot Cookbook"},{"href":"/en/copilot/tutorials/copilot-cookbook/refactor-code","title":"Refactor code"},{"href":"/en/copilot/tutorials/copilot-cookbook/refactor-code/refactor-data-access-layers","title":"Refactor data access layers"}],"documentType":"article"},"body":"# Refactoring data access layers\n\nCopilot Chat can suggest ways to decouple your data access code from your business logic, making an application easier to maintain and scale.\n\nHardcoded SQL queries and tightly coupled data access code can make it difficult to scale or switch databases, and they often lead to repeated logic.\n\nCopilot Chat can recommend implementing a repository pattern or data access layer that abstracts database interactions, making the code more modular and reducing redundancy. It can also help refactor SQL queries into optimized versions, improving performance and maintainability.\n\n> \\[!NOTE] The responses described in this article are examples. Copilot Chat responses are non-deterministic, so you may get different responses from the ones shown here.\n\n## Example scenario\n\nThis Python code connects to a SQLite database, retrieves a user record, and returns the user data. However, it fails to abstract the database connection logic and uses a hardcoded query that's vulnerable to SQL injection.\n\n```python id=database-connection\nimport sqlite3\n\ndef get_user_by_id(user_id):\n    conn = sqlite3.connect('database.db')\n    cursor = conn.cursor()\n    cursor.execute(f\"SELECT display_name FROM users WHERE id = {user_id}\")\n    user = cursor.fetchone()\n    conn.close()\n    return user\n```\n\n## Example prompt 1\n\nYou can start by asking Copilot a general question about how to improve the code.\n\n```copilot copy prompt ref=database-connection\nHow can I improve this code to make it safe and easier to update and expand? List possible improvements but don't show revised code.\n```\n\n## Example response 1\n\nCopilot makes several suggestions for improving the code:\n\n* Abstract the database connection details.\n* Use parameterized queries to prevent SQL injection.\n* Use a context manager to ensure that the database connection is properly closed if an error occurs.\n* Add error handling.\n\n## Example prompt 2\n\nYou can use the response to your first prompt to write a more specific prompt.\n\n```copilot copy prompt ref=database-connection\nRewrite this code to make it more scalable and easier to maintain. Use a context manager. Avoid hardcoded SQL queries and tightly coupled data access code. Instead, use a repository pattern to abstract database interactions and make the code more modular and reusable. Where possible optimize the code to improve performance. Include error trapping, and make sure the code is not vulnerable to SQL injection.\n```\n\n## Example response 2\n\nCopilot suggests splitting the code into separate files, each with a specific purpose. It also provides example code for returning a user's details.\n\nThe suggested changes implement a repository pattern and use parameterized queries to avoid SQL injection.\n\n```python\n# database.py\nimport sqlite3\n\nclass Database:\n    def __init__(self, db_path):\n        self.db_path = db_path\n\n    def __enter__(self):\n        self.conn = sqlite3.connect(self.db_path)\n        self.cursor = self.conn.cursor()\n        return self\n\n    def __exit__(self, exc_type, exc_val, exc_tb):\n        if exc_type or exc_val or exc_tb:\n            self.conn.rollback()\n        else:\n            self.conn.commit()\n        self.conn.close()\n\n# user_repository.py\nclass UserRepository:\n    def __init__(self, db):\n        self.db = db\n\n    def get_user_by_id(self, user_id):\n        try:\n            self.db.cursor.execute(\"SELECT display_name FROM users WHERE id = ?\", (user_id,))\n            user = self.db.cursor.fetchone()\n            return user\n        except sqlite3.Error as e:\n            print(f\"An error occurred: {e}\")\n            return None\n\n# main.py\nfrom database import Database\nfrom user_repository import UserRepository\n\ndef get_user_by_id(user_id):\n    with Database('database.db') as db:\n        user_repo = UserRepository(db)\n        return user_repo.get_user_by_id(user_id)\n\n# Example usage\nif __name__ == \"__main__\":\n    user = get_user_by_id(1)\n    if user:\n        print(user)\n    else:\n        print(\"User not found or an error occurred.\")\n```\n\nThe revised code improves on the original in several ways:\n\n* The code now implements a basic data access layer.\n* The `Database` class handles the connection to the SQLite database, implementing the context manager protocol with the `__enter__` and `__exit__` methods. This ensures that the database connection is properly managed, including committing transactions and closing the connection.\n* The `UserRepository` class encapsulates the logic for accessing user data.\n* Values for the queries are parameterized to prevent SQL injection.\n* Errors are caught, with details printed to the console.\n\n## Further reading\n\n* [Prompt engineering for GitHub Copilot Chat](/en/copilot/concepts/prompting/prompt-engineering)\n* [Best practices for using GitHub Copilot](/en/copilot/get-started/best-practices)"}