A General Purpose SQL-to-HTML Routine for CoffeeScript/JavaScript/Node.js

Using node-mysql or similar, the following CoffeeScript routine will generate an HTML table containing the data in a SQL result set, including column headings:

sql_to_html:(connection,query,bindvars,callback)->
  connection.query query, bindvars, (err,rows,fields)->
    if err?
       callback(err)
    else
      buffer = '<table border=1><tr>'
      for field in fields
        buffer += "<th>#{field.name}</th>"
      buffer += '</tr>'
      for row in rows
        buffer += '<tr>'
        for field in fields
          buffer += "<td>#{row[field.name]}</td>"
        buffer += '</tr>'
      buffer += '</table>'
      callback(null,buffer)

Finding and Closing Idle Connections in PostgreSQL

To enumerate all database connections that have been idle for at least 10 minutes:

SELECT
  DATE_TRUNC('second',NOW()-query_start) AS age,
  client_port,
  current_query
FROM
  pg_stat_activity
WHERE
  current_query = '<IDLE>'
  AND NOW() - query_start > '00:10:00'
ORDER BY
  age DESC;

To close all database connections that have been idle for at least 10 minutes:

SELECT
  pg_terminate_backend(procpid)
FROM
  pg_stat_activity
WHERE
 current_query = '<IDLE>'
 AND now() - query_start > '00:10:00';

WARNING Don't be fooled by the SELECT statement used here. The query above will drop database connections.

A Cheat Sheet for SQLite

General

• Most of the SQLite "meta" commands begin with a dot. When in doubt, try .help
• Use Ctrl-d or .exit or .quit to exit (and Ctrl-c to terminiate a long-running SQL query).
• Enter .show to see current settings.

Meta-data

• Enter .databases to see a list of mounted databases.
• Enter .tables to see a list of table names.
• Enter .index to see a list of index names.
• Enter .schema TABLENAME to see the create table statement for a given table.

Import and Export

• Enter .output FILENAME to pipe output to the specified file. (Use .output stdout to return to the default behavior or printing results to the console.)
• Enter .mode [csv|column|html|insert|line|list|tabs|tcl] to change the way in which query results are printed.
• Enter .separator DELIM to change the delimiter used in (list-mode) exports and imports. (Defaults to |.)
• Enter .dump [TABLEPATTERN] to create a collection of SQL statements for recreating the database (or just those tables with naames matching the optional TABLEPATTERN).
• Enter .read FILENAME to execute the specified file as a SQL script.