sql-toolkit

What This Skill Does

The SQL Toolkit skill empowers users to interact with relational databases directly from their command line interface. It supports SQLite, PostgreSQL, and MySQL, enabling a wide range of database operations without the need for Object-Relational Mappers (ORMs). Users can leverage this skill for designing and modifying database schemas, writing complex SQL queries involving joins, aggregations, window functions, and Common Table Expressions (CTEs). It also facilitates the creation of migration scripts, optimization of slow-performing queries through indexing and EXPLAIN analysis, and performing essential database operations like backup and restore.

SQLite is particularly highlighted for its zero-setup nature, making it ideal for local data management, rapid prototyping, and single-file databases. The skill provides straightforward commands for creating/opening SQLite databases, importing CSV data, executing one-liner queries, and exporting query results to CSV. For PostgreSQL, the toolkit offers guidance on establishing connections via command line or connection strings, executing single queries, running SQL scripts, and listing databases. It also delves into PostgreSQL-specific schema design patterns, such as using UUIDs for primary keys and implementing automatic updated_at timestamp updates using triggers.

Installation

To install the SQL Toolkit skill, run the following command:

clawhub install openclaw/skills/skills/gitgoodordietrying/sql-toolkit

Use Cases

• Database Schema Design & Management: Create, alter, and manage tables, columns, and constraints for SQLite, PostgreSQL, and MySQL.
• Complex Query Writing: Construct sophisticated SQL queries, including joins, subqueries, window functions, and CTEs.
• Performance Optimization: Identify and resolve slow database queries using indexing strategies and EXPLAIN plan analysis.
• Data Migration: Develop and execute migration scripts to update database schemas.
• Data Backup & Restore: Perform essential backup and restore operations for your databases.
• Rapid Prototyping: Quickly experiment with database structures and queries using SQLite's zero-setup convenience.
• Data Exploration: Analyze and extract data from databases, including exporting results to CSV format.

Example Prompts

1. "Design a PostgreSQL table called products with columns: id (UUID, primary key), name (text, not null), description (text), price (numeric, not null, non-negative), and created_at (timestamptz, default now()). Also, create a trigger function to automatically update the updated_at timestamp."
2. "Write a SQLite query to import the users.csv file into a table named users and then select the count of users created after '2023-01-01'."
3. "Analyze the performance of the query SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'; in my PostgreSQL database and suggest appropriate indexes."

Tips & Limitations

• Database Variety: While versatile, ensure you use the correct syntax and connection methods for SQLite, PostgreSQL, or MySQL.
• Security: Be cautious when executing queries that modify or delete data. Always back up your database before performing significant changes.
• Complexity: For extremely complex database administration tasks or highly specialized optimizations, consulting database-specific documentation or a dedicated DBA might be necessary.
• No ORM: This skill operates directly with SQL. If your application primarily uses an ORM, you'll need to translate the generated SQL or use this skill for tasks outside the ORM's scope.
• Error Handling: Pay close attention to the output and error messages provided by the SQL client tools (sqlite3, psql) for debugging.