MySQL

Easily import your CSV data into MySQL with Node.js

It's convenient to import a CSV file into a spreadsheet to crunch numbers. CSV's (comma-seperated-values) and it's brethren (TSV, for tab-separated, etc) are widely used to deliver information. You'll see CSV downloads available all over the place, whether it's your stock broker or bank, or a government agency, and on and on. Spreadsheet programs like LibreOffice Calc offer direct import of these files, and then as a spreadsheet you've got a zillion tools available to crunch numbers, make graphs, etc.

A brief look at Sequelize, an ORM for Node.js with MySQL, PostgreSQL or SQLITE3

Want to do some database code but not think too much about it?  Such as, avoid SQL?  You can have your SQL and a simplified model of your database thanks to a module I just found for Node.js called Sequelize.  It adds an ORM-like layer on top of MySQL, PostgreSQL or SQLITE3, allowing you to do database interactions using JavaScript code rather than SQL.  It's fairly nice and easy to use, however I think it's likely there are some limitations to the complexity of what you can do with Sequelize.

Installation:  npm install sequelize

Basic usage:

Simple data export and manipulation using Node.js and the node-mysql module

What follows is a fairly simple data processing script to extract data from a Drupal installation in a MySQL database.  The purpose was to export nodes from a Drupal site, reformatting them for some software I'd written to import content into a blogger blog.  In other words, this is the sort of simple data extraction and manipulation tool we write all the time.

The implementation is on the Node.js platform, using the node-mysql module.  I'm liking Node.js because JavaScript makes it fairly easy to write simple code.

Implementing MySQL style AUTOINCREMENT in SQLite3

Yesterday I wrote about implementing the MySQL enum datatype in SQLite3, and while that exploration turned out to be pretty simple someone tweeted a followup talking about how I needed to cover the AUTOINCREMENT feature as well.  Studying my code I realized that indeed it would be necessary.  Fortunately this came out to be very simple, much simpler than was implementing the enum datatype.

phpMyBackupPro: No simpler backup for MySQL

phpMyBackupPro is a utility which supposedly makes it easier to backup a MySQL database. The featureset includes

Using an SSH tunnel, or Putty, to securely access a remote MySQL server

The MySQL clients allow you to access a remote MySQL server pretty easily. You just specify the host name on the command line. But what if you want the data encrypted, for security? Or the data compressed, to improve data transfer speed? Or, more importantly, you want the MySQL server to be "locked down" limiting the range of systems that can access the MySQL server? Enabling the simple access to a remote MySQL server means configuring it to listen for connections from the entire world. Doing so is an obvious security risk.