Setting up the database and running migrations
With MongoDB, Meteor automatically sets up and runs the database for you, and you don't need any migrations because there is no schema. With Postgres, we haven't yet built functionality to install and run the database for you, and you will definitely need migrations.
Installing and running PostgreSQL
Currently, we only have directions for Mac OS, Windows and Ubuntu. Please add more for other systems!
On Mac
The easiest way to install Postgres on a Mac is with Homebrew:
brew install postgresql
Initialize postgres with a data directory:
initdb /usr/local/var/postgres -E utf8
Running the database:
postgres -D /usr/local/var/postgres
On Windows
The officially recommended installation method for PostgreSQL on Windows is with the installer from EnterpriseDB.
The installer automatically initializes the data directory and registers PostgreSQL as a Windows Service.
You can also install the graphical management interface PGAdmin III using the same installer.
On Ubuntu (14.04)
Make sure you're up to date:
sudo apt-get update
Install PostgreSQL (at the time of writing this will install v9.3):
sudo apt-get install postgresql
This will initialize Postgres and start the required daemons. Note that you may use sudo service postgresql <command>
to manage the
daemons, where <command>
is one of start
, stop
, restart
, status
.
Creating and deleting databases
You will want to create a new database for each new app. Do this with the createdb
command:
createdb todos
You can drop the database if you screw something up or want to free disk space:
dropdb todos
Running Postgres console
To connect to your database with a SQL console, run:
psql todos
For this command to work, your local Postgres server needs to be running, as described in "Running the database" above.
Migrations
To run migrations on our database, we will use the Knex CLI.
Setting up
First, create a .knex
directory in your repo. Putting a period at the beginning of the directory name will tell Meteor to ignore it, so that we can put custom scripts in here.
mkdir .knex
cd .knex
Then, install Knex and dependencies:
# Command line tool
npm install -g knex
# Local Knex and Postgres driver
npm install --save knex pg
Finally, initialize Knex:
knex init
Now, you can either set up knexfile.js
with your database information, or replace it with a file that just loads the information from an environment variable, like so:
// knexfile.js
module.exports = {
client: 'pg',
connection: process.env.POSTGRESQL_URL
};
This is the same environment variable that Meteor uses to find your database.
All Knex commands below should be run inside the .knex
directory we just created.
Creating a migration
To create a migration, first create a timestamped migration file with Knex:
knex migrate:make my_migration_name
Then, edit the file to write your migration, using the Knex schema builder API. Here's an example:
exports.up = function(knex, Promise) {
return Promise.all([
knex.schema.createTable("lists", function (table) {
table.increments(); // integer id
table.timestamp("created_at").defaultTo(knex.raw('now()')).notNullable();
// It's null if the list is public
table.integer("user_id").nullable();
// The name will be the same as the ID
table.string("name").defaultTo(knex.raw("'List '||currval('lists_id_seq')")).notNullable();
}),
knex.schema.createTable("todos", function (table) {
table.increments(); // integer id
table.timestamp("created_at").defaultTo(knex.raw('now()')).notNullable();
// It's null if the list is public
table.integer("list_id").notNullable();
table.string("text").notNullable();
table.boolean("checked").notNullable();
})
]);
};
exports.down = function(knex, Promise) {
return Promise.all([
knex.schema.dropTable("lists"),
knex.schema.dropTable("todos")
]);
};
Running migrations
Before running any migrations, make sure you have created a Postgres database for your app and specified the correct database name, username, and password in your configuration file.
To migrate to the latest version, run:
knex migrate:latest
To roll back the latest migration:
knex migrate:rollback
To print the most recent migration performed:
knex migrate:currentVersion