{"id":"cca589e1-ac77-429a-b67a-4025e2b7b3b5","shortId":"YkwB96","kind":"skill","title":"Sql Code Review","tagline":"Awesome Copilot skill by Github","description":"# SQL Code Review\n\nPerform a thorough SQL code review of ${selection} (or entire project if no selection) focusing on security, performance, maintainability, and database best practices.\n\n## 🔒 Security Analysis\n\n### SQL Injection Prevention\n```sql\n-- ❌ CRITICAL: SQL Injection vulnerability\nquery = \"SELECT * FROM users WHERE id = \" + userInput;\nquery = f\"DELETE FROM orders WHERE user_id = {user_id}\";\n\n-- ✅ SECURE: Parameterized queries\n-- PostgreSQL/MySQL\nPREPARE stmt FROM 'SELECT * FROM users WHERE id = ?';\nEXECUTE stmt USING @user_id;\n\n-- SQL Server\nEXEC sp_executesql N'SELECT * FROM users WHERE id = @id', N'@id INT', @id = @user_id;\n```\n\n### Access Control & Permissions\n- **Principle of Least Privilege**: Grant minimum required permissions\n- **Role-Based Access**: Use database roles instead of direct user permissions\n- **Schema Security**: Proper schema ownership and access controls\n- **Function/Procedure Security**: Review DEFINER vs INVOKER rights\n\n### Data Protection\n- **Sensitive Data Exposure**: Avoid SELECT * on tables with sensitive columns\n- **Audit Logging**: Ensure sensitive operations are logged\n- **Data Masking**: Use views or functions to mask sensitive data\n- **Encryption**: Verify encrypted storage for sensitive data\n\n## ⚡ Performance Optimization\n\n### Query Structure Analysis\n```sql\n-- ❌ BAD: Inefficient query patterns\nSELECT DISTINCT u.* \nFROM users u, orders o, products p\nWHERE u.id = o.user_id \nAND o.product_id = p.id\nAND YEAR(o.order_date) = 2024;\n\n-- ✅ GOOD: Optimized structure\nSELECT u.id, u.name, u.email\nFROM users u\nINNER JOIN orders o ON u.id = o.user_id\nWHERE o.order_date >= '2024-01-01' \nAND o.order_date < '2025-01-01';\n```\n\n### Index Strategy Review\n- **Missing Indexes**: Identify columns that need indexing\n- **Over-Indexing**: Find unused or redundant indexes\n- **Composite Indexes**: Multi-column indexes for complex queries\n- **Index Maintenance**: Check for fragmented or outdated indexes\n\n### Join Optimization\n- **Join Types**: Verify appropriate join types (INNER vs LEFT vs EXISTS)\n- **Join Order**: Optimize for smaller result sets first\n- **Cartesian Products**: Identify and fix missing join conditions\n- **Subquery vs JOIN**: Choose the most efficient approach\n\n### Aggregate and Window Functions\n```sql\n-- ❌ BAD: Inefficient aggregation\nSELECT user_id, \n       (SELECT COUNT(*) FROM orders o2 WHERE o2.user_id = o1.user_id) as order_count\nFROM orders o1\nGROUP BY user_id;\n\n-- ✅ GOOD: Efficient aggregation\nSELECT user_id, COUNT(*) as order_count\nFROM orders\nGROUP BY user_id;\n```\n\n## 🛠️ Code Quality & Maintainability\n\n### SQL Style & Formatting\n```sql\n-- ❌ BAD: Poor formatting and style\nselect u.id,u.name,o.total from users u left join orders o on u.id=o.user_id where u.status='active' and o.order_date>='2024-01-01';\n\n-- ✅ GOOD: Clean, readable formatting\nSELECT u.id,\n       u.name,\n       o.total\nFROM users u\nLEFT JOIN orders o ON u.id = o.user_id\nWHERE u.status = 'active'\n  AND o.order_date >= '2024-01-01';\n```\n\n### Naming Conventions\n- **Consistent Naming**: Tables, columns, constraints follow consistent patterns\n- **Descriptive Names**: Clear, meaningful names for database objects\n- **Reserved Words**: Avoid using database reserved words as identifiers\n- **Case Sensitivity**: Consistent case usage across schema\n\n### Schema Design Review\n- **Normalization**: Appropriate normalization level (avoid over/under-normalization)\n- **Data Types**: Optimal data type choices for storage and performance\n- **Constraints**: Proper use of PRIMARY KEY, FOREIGN KEY, CHECK, NOT NULL\n- **Default Values**: Appropriate default values for columns\n\n## 🗄️ Database-Specific Best Practices\n\n### PostgreSQL\n```sql\n-- Use JSONB for JSON data\nCREATE TABLE events (\n    id SERIAL PRIMARY KEY,\n    data JSONB NOT NULL,\n    created_at TIMESTAMPTZ DEFAULT NOW()\n);\n\n-- GIN index for JSONB queries\nCREATE INDEX idx_events_data ON events USING gin(data);\n\n-- Array types for multi-value columns\nCREATE TABLE tags (\n    post_id INT,\n    tag_names TEXT[]\n);\n```\n\n### MySQL\n```sql\n-- Use appropriate storage engines\nCREATE TABLE sessions (\n    id VARCHAR(128) PRIMARY KEY,\n    data TEXT,\n    expires TIMESTAMP\n) ENGINE=InnoDB;\n\n-- Optimize for InnoDB\nALTER TABLE large_table \nADD INDEX idx_covering (status, created_at, id);\n```\n\n### SQL Server\n```sql\n-- Use appropriate data types\nCREATE TABLE products (\n    id BIGINT IDENTITY(1,1) PRIMARY KEY,\n    name NVARCHAR(255) NOT NULL,\n    price DECIMAL(10,2) NOT NULL,\n    created_at DATETIME2 DEFAULT GETUTCDATE()\n);\n\n-- Columnstore indexes for analytics\nCREATE COLUMNSTORE INDEX idx_sales_cs ON sales;\n```\n\n### Oracle\n```sql\n-- Use sequences for auto-increment\nCREATE SEQUENCE user_id_seq START WITH 1 INCREMENT BY 1;\n\nCREATE TABLE users (\n    id NUMBER DEFAULT user_id_seq.NEXTVAL PRIMARY KEY,\n    name VARCHAR2(255) NOT NULL\n);\n```\n\n## 🧪 Testing & Validation\n\n### Data Integrity Checks\n```sql\n-- Verify referential integrity\nSELECT o.user_id \nFROM orders o \nLEFT JOIN users u ON o.user_id = u.id \nWHERE u.id IS NULL;\n\n-- Check for data consistency\nSELECT COUNT(*) as inconsistent_records\nFROM products \nWHERE price < 0 OR stock_quantity < 0;\n```\n\n### Performance Testing\n- **Execution Plans**: Review query execution plans\n- **Load Testing**: Test queries with realistic data volumes\n- **Stress Testing**: Verify performance under concurrent load\n- **Regression Testing**: Ensure optimizations don't break functionality\n\n## 📊 Common Anti-Patterns\n\n### N+1 Query Problem\n```sql\n-- ❌ BAD: N+1 queries in application code\nfor user in users:\n    orders = query(\"SELECT * FROM orders WHERE user_id = ?\", user.id)\n\n-- ✅ GOOD: Single optimized query\nSELECT u.*, o.*\nFROM users u\nLEFT JOIN orders o ON u.id = o.user_id;\n```\n\n### Overuse of DISTINCT\n```sql\n-- ❌ BAD: DISTINCT masking join issues\nSELECT DISTINCT u.name \nFROM users u, orders o \nWHERE u.id = o.user_id;\n\n-- ✅ GOOD: Proper join without DISTINCT\nSELECT u.name\nFROM users u\nINNER JOIN orders o ON u.id = o.user_id\nGROUP BY u.name;\n```\n\n### Function Misuse in WHERE Clauses\n```sql\n-- ❌ BAD: Functions prevent index usage\nSELECT * FROM orders \nWHERE YEAR(order_date) = 2024;\n\n-- ✅ GOOD: Range conditions use indexes\nSELECT * FROM orders \nWHERE order_date >= '2024-01-01' \n  AND order_date < '2025-01-01';\n```\n\n## 📋 SQL Review Checklist\n\n### Security\n- [ ] All user inputs are parameterized\n- [ ] No dynamic SQL construction with string concatenation\n- [ ] Appropriate access controls and permissions\n- [ ] Sensitive data is properly protected\n- [ ] SQL injection attack vectors are eliminated\n\n### Performance\n- [ ] Indexes exist for frequently queried columns\n- [ ] No unnecessary SELECT * statements\n- [ ] JOINs are optimized and use appropriate types\n- [ ] WHERE clauses are selective and use indexes\n- [ ] Subqueries are optimized or converted to JOINs\n\n### Code Quality\n- [ ] Consistent naming conventions\n- [ ] Proper formatting and indentation\n- [ ] Meaningful comments for complex logic\n- [ ] Appropriate data types are used\n- [ ] Error handling is implemented\n\n### Schema Design\n- [ ] Tables are properly normalized\n- [ ] Constraints enforce data integrity\n- [ ] Indexes support query patterns\n- [ ] Foreign key relationships are defined\n- [ ] Default values are appropriate\n\n## 🎯 Review Output Format\n\n### Issue Template\n```\n## [PRIORITY] [CATEGORY]: [Brief Description]\n\n**Location**: [Table/View/Procedure name and line number if applicable]\n**Issue**: [Detailed explanation of the problem]\n**Security Risk**: [If applicable - injection risk, data exposure, etc.]\n**Performance Impact**: [Query cost, execution time impact]\n**Recommendation**: [Specific fix with code example]\n\n**Before**:\n```sql\n-- Problematic SQL\n```\n\n**After**:\n```sql\n-- Improved SQL\n```\n\n**Expected Improvement**: [Performance gain, security benefit]\n```\n\n### Summary Assessment\n- **Security Score**: [1-10] - SQL injection protection, access controls\n- **Performance Score**: [1-10] - Query efficiency, index usage\n- **Maintainability Score**: [1-10] - Code quality, documentation\n- **Schema Quality Score**: [1-10] - Design patterns, normalization\n\n### Top 3 Priority Actions\n1. **[Critical Security Fix]**: Address SQL injection vulnerabilities\n2. **[Performance Optimization]**: Add missing indexes or optimize queries\n3. **[Code Quality]**: Improve naming conventions and documentation\n\nFocus on providing actionable, database-agnostic recommendations while highlighting platform-specific optimizations and best practices.","tags":["sql","code","review","awesome","copilot","github"],"capabilities":["skill","source-github","category-awesome-copilot"],"categories":["awesome-copilot"],"synonyms":[],"warnings":[],"endpointUrl":"https://skills.sh/github/awesome-copilot/sql-code-review","protocol":"skill","transport":"skills-sh","auth":{"type":"none","details":{"install_from":"skills.sh"}},"qualityScore":"0.300","qualityRationale":"deterministic score 0.30 from registry signals: · indexed on skills.sh · published under github/awesome-copilot","verified":false,"liveness":"unknown","lastLivenessCheck":null,"agentReviews":{"count":0,"score_avg":null,"cost_usd_avg":null,"success_rate":null,"latency_p50_ms":null,"narrative_summary":null,"summary_updated_at":null},"enrichmentModel":"deterministic:skill:v1","enrichmentVersion":1,"enrichedAt":"2026-04-22T17:40:17.241Z","embedding":null,"createdAt":"2026-04-18T20:25:25.137Z","updatedAt":"2026-04-22T17:40:17.241Z","lastSeenAt":"2026-04-22T17:40:17.241Z","tsv":"'+1':741,747 '-01':226,227,232,233,387,388,415,416,856,857,862,863 '-10':1038,1047,1055,1063 '0':700,704 '1':595,596,642,645,1037,1046,1054,1062,1071 '10':606 '128':558 '2':607,1079 '2024':203,225,386,414,843,855 '2025':231,861 '255':601,657 '3':1068,1088 'access':97,111,126,881,1042 'across':449 'action':1070,1099 'activ':382,410 'add':574,1082 'address':1075 'aggreg':306,313,339 'agnost':1102 'alter':570 'analysi':36,175 'analyt':618 'anti':738 'anti-pattern':737 'applic':750,990,1000 'approach':305 'appropri':274,455,483,550,586,880,912,942,973 'array':531 'assess':1034 'attack':892 'audit':147 'auto':633 'auto-incr':632 'avoid':140,437,458 'awesom':4 'bad':177,311,360,745,787,831 'base':110 'benefit':1032 'best':33,491,1111 'bigint':593 'break':734 'brief':981 'cartesian':290 'case':444,447 'categori':980 'category-awesome-copilot' 'check':263,478,664,687 'checklist':866 'choic':465 'choos':301 'claus':829,915 'clean':390 'clear':429 'code':2,10,16,353,751,928,1017,1056,1089 'column':146,240,256,422,487,537,902 'columnstor':615,620 'comment':938 'common':736 'complex':259,940 'composit':252 'concaten':879 'concurr':726 'condit':297,846 'consist':419,425,446,690,930 'constraint':423,470,957 'construct':876 'control':98,127,882,1043 'convent':418,932,1093 'convert':925 'copilot':5 'cost':1009 'count':318,329,343,346,692 'cover':577 'creat':500,511,521,538,553,579,589,610,619,635,646 'critic':41,1072 'cs':624 'data':135,138,154,163,170,460,463,499,507,525,530,561,587,662,689,719,886,943,959,1003 'databas':32,113,433,439,489,1101 'database-agnost':1100 'database-specif':488 'date':202,224,230,385,413,842,854,860 'datetime2':612 'decim':605 'default':481,484,514,613,651,970 'defin':131,969 'delet':54 'descript':427,982 'design':452,952,1064 'detail':992 'direct':117 'distinct':182,785,788,793,808 'document':1058,1095 'dynam':874 'effici':304,338,1049 'elimin':895 'encrypt':164,166 'enforc':958 'engin':552,565 'ensur':149,730 'entir':21 'error':947 'etc':1005 'event':502,524,527 'exampl':1018 'exec':81 'execut':74,707,711,1010 'executesql':83 'exist':281,898 'expect':1027 'expir':563 'explan':993 'exposur':139,1004 'f':53 'find':247 'first':289 'fix':294,1015,1074 'focus':26,1096 'follow':424 'foreign':476,965 'format':358,362,392,934,976 'fragment':265 'frequent':900 'function':159,309,735,825,832 'function/procedure':128 'gain':1030 'getutcd':614 'gin':516,529 'github':8 'good':204,337,389,765,804,844 'grant':104 'group':333,349,822 'handl':948 'highlight':1105 'id':50,59,61,73,78,89,90,92,94,96,194,197,221,316,324,326,336,342,352,379,407,503,542,556,581,592,638,649,671,681,763,782,803,821 'ident':594 'identifi':239,292,443 'idx':523,576,622 'impact':1007,1012 'implement':950 'improv':1025,1028,1091 'inconsist':694 'increment':634,643 'indent':936 'index':234,238,243,246,251,253,257,261,268,517,522,575,616,621,834,848,897,920,961,1050,1084 'ineffici':178,312 'inject':38,43,891,1001,1040,1077 'inner':214,277,814 'innodb':566,569 'input':870 'instead':115 'int':93,543 'integr':663,668,960 'invok':133 'issu':791,977,991 'join':215,269,271,275,282,296,300,373,401,676,776,790,806,815,907,927 'json':498 'jsonb':496,508,519 'key':475,477,506,560,598,654,966 'larg':572 'least':102 'left':279,372,400,675,775 'level':457 'line':987 'load':713,727 'locat':983 'log':148,153 'logic':941 'maintain':30,355,1052 'mainten':262 'mask':155,161,789 'meaning':430,937 'minimum':105 'miss':237,295,1083 'misus':826 'multi':255,535 'multi-column':254 'multi-valu':534 'mysql':547 'n':84,91,740,746 'name':417,420,428,431,545,599,655,931,985,1092 'need':242 'normal':454,456,956,1066 'null':480,510,603,609,659,686 'number':650,988 'nvarchar':600 'o':188,217,375,403,674,771,778,799,817 'o.order':201,223,229,384,412 'o.product':196 'o.total':368,396 'o.user':193,220,378,406,670,680,781,802,820 'o1':332 'o1.user':325 'o2':321 'o2.user':323 'object':434 'oper':151 'optim':172,205,270,284,462,567,731,767,909,923,1081,1086,1109 'oracl':627 'order':56,187,216,283,320,328,331,345,348,374,402,673,756,760,777,798,816,838,841,851,853,859 'outdat':267 'output':975 'over-index':244 'over/under-normalization':459 'overus':783 'ownership':124 'p':190 'p.id':198 'parameter':63,872 'pattern':180,426,739,964,1065 'perform':12,29,171,469,705,724,896,1006,1029,1044,1080 'permiss':99,107,119,884 'plan':708,712 'platform':1107 'platform-specif':1106 'poor':361 'post':541 'postgresql':493 'postgresql/mysql':65 'practic':34,492,1112 'prepar':66 'prevent':39,833 'price':604,699 'primari':474,505,559,597,653 'principl':100 'prioriti':979,1069 'privileg':103 'problem':743,996 'problemat':1021 'product':189,291,591,697 'project':22 'proper':122,471,805,888,933,955 'protect':136,889,1041 'provid':1098 'qualiti':354,929,1057,1060,1090 'quantiti':703 'queri':45,52,64,173,179,260,520,710,716,742,748,757,768,901,963,1008,1048,1087 'rang':845 'readabl':391 'realist':718 'recommend':1013,1103 'record':695 'redund':250 'referenti':667 'regress':728 'relationship':967 'requir':106 'reserv':435,440 'result':287 'review':3,11,17,130,236,453,709,865,974 'right':134 'risk':998,1002 'role':109,114 'role-bas':108 'sale':623,626 'schema':120,123,450,451,951,1059 'score':1036,1045,1053,1061 'secur':28,35,62,121,129,867,997,1031,1035,1073 'select':19,25,46,69,85,141,181,207,314,317,340,365,393,669,691,758,769,792,809,836,849,905,917 'sensit':137,145,150,162,169,445,885 'seq':639 'sequenc':630,636 'serial':504 'server':80,583 'session':555 'set':288 'singl':766 'skill':6 'smaller':286 'source-github' 'sp':82 'specif':490,1014,1108 'sql':1,9,15,37,40,42,79,176,310,356,359,494,548,582,584,628,665,744,786,830,864,875,890,1020,1022,1024,1026,1039,1076 'start':640 'statement':906 'status':578 'stmt':67,75 'stock':702 'storag':167,467,551 'strategi':235 'stress':721 'string':878 'structur':174,206 'style':357,364 'subqueri':298,921 'summari':1033 'support':962 'tabl':143,421,501,539,554,571,573,590,647,953 'table/view/procedure':984 'tag':540,544 'templat':978 'test':660,706,714,715,722,729 'text':546,562 'thorough':14 'time':1011 'timestamp':564 'timestamptz':513 'top':1067 'type':272,276,461,464,532,588,913,944 'u':183,186,213,371,399,678,770,774,797,813 'u.email':210 'u.id':192,208,219,366,377,394,405,682,684,780,801,819 'u.name':209,367,395,794,810,824 'u.status':381,409 'unnecessari':904 'unus':248 'usag':448,835,1051 'use':76,112,156,438,472,495,528,549,585,629,847,911,919,946 'user':48,58,60,71,77,87,95,118,185,212,315,335,341,351,370,398,637,648,677,753,755,762,773,796,812,869 'user.id':764 'user_id_seq.nextval':652 'userinput':51 'valid':661 'valu':482,485,536,971 'varchar':557 'varchar2':656 'vector':893 'verifi':165,273,666,723 'view':157 'volum':720 'vs':132,278,280,299 'vulner':44,1078 'window':308 'without':807 'word':436,441 'year':200,840","prices":[{"id":"bf2e0b8f-cbce-4836-ad23-8fa3ce539bbe","listingId":"cca589e1-ac77-429a-b67a-4025e2b7b3b5","amountUsd":"0","unit":"free","nativeCurrency":null,"nativeAmount":null,"chain":null,"payTo":null,"paymentMethod":"skill-free","isPrimary":true,"details":{"org":"github","category":"awesome-copilot","install_from":"skills.sh"},"createdAt":"2026-04-18T20:25:25.137Z"}],"sources":[{"listingId":"cca589e1-ac77-429a-b67a-4025e2b7b3b5","source":"github","sourceId":"github/awesome-copilot/sql-code-review","sourceUrl":"https://github.com/github/awesome-copilot/tree/main/skills/sql-code-review","isPrimary":false,"firstSeenAt":"2026-04-18T21:51:19.061Z","lastSeenAt":"2026-04-22T12:52:24.220Z"},{"listingId":"cca589e1-ac77-429a-b67a-4025e2b7b3b5","source":"skills_sh","sourceId":"github/awesome-copilot/sql-code-review","sourceUrl":"https://skills.sh/github/awesome-copilot/sql-code-review","isPrimary":true,"firstSeenAt":"2026-04-18T20:25:25.137Z","lastSeenAt":"2026-04-22T17:40:17.241Z"}],"details":{"listingId":"cca589e1-ac77-429a-b67a-4025e2b7b3b5","quickStartSnippet":null,"exampleRequest":null,"exampleResponse":null,"schema":null,"openapiUrl":null,"agentsTxtUrl":null,"citations":[],"useCases":[],"bestFor":[],"notFor":[],"kindDetails":{"org":"github","slug":"sql-code-review","source":"skills_sh","category":"awesome-copilot","skills_sh_url":"https://skills.sh/github/awesome-copilot/sql-code-review"},"updatedAt":"2026-04-22T17:40:17.241Z"}}