Ruby for quick database scripts (MySQL, Postgres, etc.)

General Discussion
,
1

Whenever I need to write quick scripts that involve a database, I usually reach for Ruby, even though I’m more familiar with Python and JS.

I thought this info might be useful to people, so I’ll post some examples here for MySQL and Postgres.

MySQL

Install the “gem” (package dependency).

$ gem install mysql2

This example reads from a CSV file and inserts the data into MySQL.

Tip: MySQL is case-insensitive. If you’re dealing with user input, like email addresses, be careful. MySQL sees [email protected] and [email protected] as the same, but Ruby (and Postgres) will see them as two different email addresses. (SQL function: LOWER() or Ruby function: "string".downcase)

require 'csv'
require 'mysql2'

# connect to the database
client = Mysql2::Client.new(
  :host => 'localhost',
  # get the env variables into your terminal with:
  # export DB_USERNAME=your_username
  # export DB_PASSWORD=your_password
  :username => ENV['DB_USERNAME'],
  :password => ENV['DB_PASSWORD'],
  :database => 'database_name',
  :encoding => 'utf8',
)

csv_data = CSV.read("./data/some_file.csv")

# this is a loop in Ruby
csv_data.each do |row|
  # process each row of the CSV here (array) using the name `row`
  # example: `row[0]`, `row[1]`

  # This example inserts the row into the database.
  # You can do this task with SQL too, but if you have to process 
  # the data first, Ruby might be easier.
  query = %Q(
    INSERT INTO
      some_table (
        column1,
        column2
      )
    VALUES (
      "#{row[1]}",
      "#{row[2]}"
    )
  )

  # run the SQL
  client.query(query)
end

Postgres

Install the gem:

$ gem install pg

This example queries a Postgres database:

require 'pg'

conn = PG.connect(
  user: ENV['DB_USERNAME'],
  password: ENV['DB_PASSWORD'],
  dbname: 'database_name',
  host: 'localhost',
  port: '5432',
)

# `%Q` interpolates variables (see previous example), `%q` doesn't
query = %q(
  SELECT
    id,
    name,
    email
  FROM table_name
  LIMIT 10
)

# run the SQL
data = conn.exec(query)

data.each do |row|
  # process each row of the results here
end

sqlite3

Check out this page for examples using sqlite3 and Ruby:

MongoDB

Mongo examples:

https://docs.mongodb.com/ruby-driver/current/quick-start/

Other useful things to know

Two other common tasks are reading/writing files and using JSON. They syntax is simple and easy to use. (See those links for a variety of examples.)

There’s a quick introduction to the language over here:
https://learnxinyminutes.com/docs/ruby/