database-query-and-export

Database Query and Export

Query relational databases (SQLite, PostgreSQL, MySQL) and export results to CSV, JSON, or other formats. Essential for data extraction, reporting, backup automation, and analytics pipelines.

When to use

• Use case 1: When the user asks to query a database and export results
• Use case 2: When you need to extract data for analysis or reporting
• Use case 3: For backup and data migration workflows
• Use case 4: When building automated database monitoring and alerts

Required tools / APIs

• SQLite — Lightweight file-based database (often pre-installed)
• PostgreSQL client — For PostgreSQL databases
• MySQL client — For MySQL/MariaDB databases
• No external API required

Install options:

# Ubuntu/Debian
sudo apt-get install -y sqlite3 postgresql-client mysql-client

# macOS
brew install sqlite3 postgresql mysql-client

# Node.js (database drivers)
npm install better-sqlite3  # SQLite
npm install pg              # PostgreSQL
npm install mysql2          # MySQL

Skills

query_sqlite_to_json

Query SQLite database and export to JSON format.

# Basic query to JSON
sqlite3 database.db "SELECT * FROM users LIMIT 10;" -json

# With pretty formatting using jq
sqlite3 database.db "SELECT * FROM users WHERE active=1;" -json | jq '.'

# Export entire table to JSON file
sqlite3 database.db "SELECT * FROM orders;" -json > orders.json

# Query with WHERE clause
sqlite3 database.db "SELECT id, name, email FROM users WHERE created_at > '2024-01-01';" -json

Node.js:

const Database = require('better-sqlite3');

function querySQLiteToJSON(dbPath, query) {
  const db = new Database(dbPath, { readonly: true });
  const rows = db.prepare(query).all();
  db.close();
  return rows;
}

// Usage
// const users = querySQLiteToJSON('./database.db', 'SELECT * FROM users LIMIT 10');
// console.log(JSON.stringify(users, null, 2));

query_sqlite_to_csv

Query SQLite database and export to CSV format.