Tutorial: Accessing a Lando Database Container with Sequel Pro

I’ve been doing a lot of audits recently, which means I’m pulling down codebases that weren’t always built using the common tooling our PS team brings in (like BLT). This means that I’ve been moving a lot of databases manually (yuck) and I thought I’d take a moment to show how easy it is to do this with Lando and Sequel Pro.

Why aren’t I using drush? Well, in fairness, I usually do try to use Drush! But, there are times (especially with larger databases) that I find that the drush sql:cli sometimes will crash / bomb out. So, it’s always good to have a backup (and for me, that backup is Sequel Pro). Let’s get into it, it’s super easy!

Getting Started

The basic prerequisites for this tutorial are quite simple:

1. An exported database file

2. A running Lando database container

3. Sequel Pro (or other database tool)

From here, start up lando with lando start. Once you have the telltale green output we need to run an additional Lando command to get more database info:

$ lando info

[ { service: 'appserver',
    urls:
     [ 'https://localhost:54985',
       'http://localhost:54986',
       'http://local.lndo.site/',
       'https://local.lndo.site/' ],
    type: 'php',
    healthy: true,
    via: 'apache',
    webroot: 'docroot',
    config: { php: '/Users/mike.madison/.lando/config/drupal9/php.ini' },
    version: '8.0',
    meUser: 'www-data',
    hasCerts: true,
    hostnames: [ 'appserver.local.internal' ] },
  { service: 'database',
    urls: [],
    type: 'mysql',
    healthy: true,
    internal_connection: { host: 'database', port: '3306' },
    external_connection: { host: '127.0.0.1', port: '55007' },
    healthcheck: 'bash -c "[ -f /bitnami/mysql/.mysql_initialized ]"',
    creds: { database: 'drupal9', password: 'drupal9', user: 'drupal9' },
    config: { database: '/Users/mike.madison/.lando/config/drupal9/mysql.cnf' },
    version: '5.7',
    meUser: 'www-data',
    hasCerts: false,
    hostnames: [ 'database.local.internal' ] } ]

The relevant bits for our tutorial are in the database service. Specifically, the external connection line:

external_connection: { host: '127.0.0.1', port: '55007' }

Now we just over to Sequel Pro!

The host and port come from that external connection data. The username, password, and database all come from the Lando recipe that you are using. In this case, I’m using the drupal9 recipe so all three are drupal9 (but your miles will vary based on which recipe you use).

Once you connect, that’s it. You’re in the database. From there you can easily import a database, or manipulate an existing one!

Gotchas / Recommendations

A couple quick protips:

1. Every time you restart / rebuild your vm, the port is going to change. So you will have to run lando info any time you want to connect (otherwise, the will almost certainly fail after the first time you get it working)

2. Any time you pull a database down manually, don’t forget to sanitize it. This is easily accomplished by running drush cr (once you confirm you can bootstrap) and then drush sql-sanitize. I often will then export this database and keep it as a point of reference, just in case I need to start over or share it with my development team. I label it sanitized as well!

Doing this manually should only be viewed as a temporary solution. Anytime you are going to be doing this sort of process regularly / often, please please take the time to automate (or use a tool like BLT that already has automated) commands for syncing databases AND sanitizing them every time! Happy sql-ing!

Related Content