Introduction

With DB you can very easily save, restore, and archive snapshots of your database from the command line. It supports connecting to different database servers (for example a local development server and a staging or production server) and allows you to load a database dump from one environment into another environment.

For now, this is for MySQL only, but it could be extended to be used with other database systems as well.

Table of Contents

• Introduction
• Examples
• Installation
  • Additional configuration / different port or socket
• Available commands
  • db init
  • db save
  • db load
  • db remove
  • db server
    • db server add
    • db server remove
    • db server show
    • db server list
  • db export
  • db import
  • db nuke
  • db log
  • db show
  • db structure
  • db change
    • db change charset
    • db change wordpress
  • db test

Examples

$ db save localhost "snapshot before running migrations" 

> [localhost] 
> Successfully made snapshot of database - hash f4f0c1d3fac74166c12a3708cdaa5d804dcd4b970c6e2789ccb23303 

This will save a copy of your database to the repository.

By default, this repository lives in the .db folder in your project root. In this repository are gzipped database dumps. Whether or not you want to commit the contents of that folder to git or another VCS is up to you. If you do, make sure to exclude your database credentials by adding something like .db/*/config/credentials.cnf (untested!) to your .gitignore.

Imagine the following scenario: after creating the local snapshot, you run a database migration. However, the migration breaks your database and deletes some stuff that you didn't want to delete. Usually, if you run a migration, there is also a way to undo that migration - but alas, that won't return your deleted data. No problem. To restore your database to the state it was in before you started the migration, simply run:

$ db load localhost f4f0c1d3fac74166c12a3708cdaa5d804dcd4b970c6e2789ccb23303 

> [localhost] Loaded snapshot f4f0c1d3fac74166c12a3708cdaa5d804dcd4b970c6e2789ccb23303 

This will load the snapshot you made before you ran the migration. It's like the migration never happened.

There are many other things db can do, for example pulling down a database from the staging environment to your localhost:

$ db save staging "Snapshot intended for development" 

> [staging] 
> Successfully made snapshot of database - hash 1577b2173c672eb824d5b43e989f15448f2bfca43b5b1144e6977479 

$ db load localhost 1577b2173c672eb824d5b43e989f15448f2bfca43b5b1144e6977479 

> [localhost] Loaded snapshot 1577b2173c672eb824d5b43e989f15448f2bfca43b5b1144e6977479 

Here you first make a snapshot of the database on the staging server, which you then load into your localhost database. This works most reliably if your staging and your development server are on the same database version.

See the full list of available commands to get a complete overview of db's capabilities.

Installation

MacOS

On MacOS, you can install db with the HomeBrew package manager:

$ brew install db-vcs

Linux / others

On other operating systems, you can install db by cloning the repository:

$ git clone https://www.github.com/infostreams/db

and then creating a symlink to the main db script from a directory that is in your path, e.g.:

$ cd db/ # change to the directory you cloned the github repository in
$ ln -s db /usr/local/bin/ # create the symlink

You can see if it works by running

$ db

If all is well you should see the following friendly error message:

fatal: Not a db repository (or any of the parent directories). Please run 'db init'.

So, to really get started, go to a directory where you have a project that uses a database, and type db init. This will start the process of setting up your database connection details, after which the following commands will be available to you.

Additional configuration / different port or socket

You can provide additional configuration for the MySQL connection by providing them in the options file that can be found at .db/<alias>/config/credentials.cnf. Here you can provide a different port number or you can specify a socket to connect through, for example

[client]
user = root
password =
host = 127.0.0.1
port = 3307

to connect to a MySQL database on a port 3307 instead of the standard 3306, or

[client]
user = root
password =
socket = /var/run/mysqld/mysql.sock

to connect to MySQL through a socket file located at /var/run/mysqld/mysql.sock.

Available commands

db has commands to import external SQL files into your database (db import), to export a dump from the repository to a file (db export), to make minor changes to your database (such as changing the encoding, with db change), to clean out your database entirely (db nuke), or to show the table structure of a particular dump (db structure). Very high on the wish list is a way to display a "diff" between two dumps, but that's not so easy and hasn't been implemented yet.

Full list

The full list of available commands is as follows

• init
• save
• load
• remove
• server
• export
• import
• log
• nuke
• show
• structure
• change
• test

db init

Creates a new DB repository in the current directory. Starts an interactive session that allows you to specify the connection details.

Syntax

$ db init <database-type> 

If no database-type is provided, you will be asked to specify it in the interactive session. At the moment, only mysql is supported. The extension points for other database systems are already in place and could (theoretically) be added relatively easily.

Below you see an example of the questions you might be asked.

$ db init 


> Please provide the database type for this repository 
> Supported types: mysql 
> 
> Database type [mysql]: 
> 
> Please provide the connection details for the database 
> Server alias []: 
> Connection type, e.g. direct, or ssh []: 
> SSH login, e.g. user@hostname.com []: 
> Please provide the database host. For connection types SSH and direct, this is probably 127.0.0.1. 
> Database host [127.0.0.1]: 
> Username [root]: 
> Password []: 
> Database []: 
> 
> Successfully connected to database. Writing config. 

The password for your connection will be stored in plain text, in a file that only the current user has read-access to (file mode 0600). Make sure to not commit this file (.db/<server alias>/config/credentials.cnf) to source control!

If you want to add a remote server, you need to have ssh access to it. It is best if you have setup passwordless access, otherwise it will ask you for your password every time you interact with the remote server.

db save

Saves a snapshot of the database to the repository.

Syntax

$ db save [server alias] [commit message] 

If you omit the server alias it will default to using the oldest server alias, in my case almost always localhost.

Example

$ db save localhost "Hello database" 

> [localhost] 
> Successfully made snapshot of database - hash e82a736789b421e7efd0ee2071bff33945a5fab6be08be6821a3f576 

If you try to make a snapshot of a database that didn't change, it will not save a new snapshot.

db load

Loads a snapshot from the repository into the database.

CAVEAT: Any tables that are not in the snapshot you are restoring are left untouched. It only replaces the tables that are in the snapshot. If you want to completely empty your database first, have a look at db nuke.

Syntax

$ db load [server alias] [snapshot] [--match MATCH] <table_1> <table_2> <...> <table_n>

If you omit the server alias it will default to using the oldest server alias, in my case almost always localhost.

For [snapshot] you can either provide the full hash (e.g. e82a736789b421e7efd0ee2071bff33945a5fab6be08be6821a3f576) or you can provide just enough characters to uniquely identify a given dump (e.g. e82a736789b4)

You can choose to only load one or more specific tables from this snapshot. For example, the following command will only restore the wp_users table to localhost:

$ db load localhost e82a736789b421e7efd0ee2071bff33945a5fab6be08be6821a3f576 wp_users

You can provide more than one table to restore. If you don't provide any tables, it will load all the tables that are defined in the snapshot.

You can also provide a regular expression to match the table name to restore. For example, to only restore tables whose name matches the regular expression wp_13_.*, you can run the following command:

$ db load localhost e82a736789b421e7efd0ee2071bff33945a5fab6be08be6821a3f576 --match "wp_13_.*"

db remove

Removes a snapshot from the repository

Syntax

$ db remove [server alias] [snapshot]