Sample config/database.yml from Rails. Postgres, MySQL, and SQLite

mysql.database.yml

# 
# Install the MYSQL driver
#   gem install mysql2
#
# Ensure the MySQL gem is defined in your Gemfile
#   gem 'mysql2'
#
# And be sure to use new-style password hashing:
#   http://dev.mysql.com/doc/refman/5.0/en/old-client.html
development:
  adapter: mysql2
  encoding: utf8
  reconnect: false
  database: sample_sqlite3_development
  pool: 5
  username: root
  password:
  socket: /tmp/mysql.sock

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  adapter: mysql2
  encoding: utf8
  reconnect: false
  database: sample_sqlite3_test
  pool: 5
  username: root
  password:
  socket: /tmp/mysql.sock

production:
  adapter: mysql2
  encoding: utf8
  reconnect: false
  database: sample_sqlite3_production
  pool: 5
  username: root
  password:
  socket: /tmp/mysql.sock

postgres.database.yml

# PostgreSQL. Versions 8.2 and up are supported.
#
# Install the pg driver:
#   gem install pg
# On Mac OS X with macports:
#   gem install pg -- --with-pg-config=/opt/local/lib/postgresql84/bin/pg_config
# On Windows:
#   gem install pg
#       Choose the win32 build.
#       Install PostgreSQL and put its /bin directory on your path.
#
# Configure Using Gemfile
# gem 'pg'
#
development:
  adapter: postgresql
  encoding: unicode
  database: sample_postgres_development
  pool: 5
  username: sample_postgres
  password:

  # Connect on a TCP socket. Omitted by default since the client uses a
  # domain socket that doesn't need configuration. Windows does not have
  # domain sockets, so uncomment these lines.
  #host: localhost
  #port: 5432

  # Schema search path. The server defaults to $user,public
  #schema_search_path: myapp,sharedapp,public

  # Minimum log levels, in increasing order:
  #   debug5, debug4, debug3, debug2, debug1,
  #   log, notice, warning, error, fatal, and panic
  # The server defaults to notice.
  #min_messages: warning

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  adapter: postgresql
  encoding: unicode
  database: sample_postgres_test
  pool: 5
  username: sample_postgres
  password:

production:
  adapter: postgresql
  encoding: unicode
  database: sample_postgres_production
  pool: 5
  username: sample_postgres
  password:

sqlite-database.yml

# MySQL.  Versions 4.1 and 5.0 are recommended.
# 
# Install the MYSQL driver
#   gem install mysql2
#
# Ensure the MySQL gem is defined in your Gemfile
#   gem 'mysql2'
#
# And be sure to use new-style password hashing:
#   http://dev.mysql.com/doc/refman/5.0/en/old-client.html
development:
  adapter: mysql2
  encoding: utf8
  reconnect: false
  database: sample_sqlite3_development
  pool: 5
  username: root
  password:
  socket: /tmp/mysql.sock

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  adapter: mysql2
  encoding: utf8
  reconnect: false
  database: sample_sqlite3_test
  pool: 5
  username: root
  password:
  socket: /tmp/mysql.sock

production:
  adapter: mysql2
  encoding: utf8
  reconnect: false
  database: sample_sqlite3_production
  pool: 5
  username: root
  password:
  socket: /tmp/mysql.sock

I'm not convinced about the sqlite-database.yml 😉

@alexanderadam 😄

config/database.sqlite3.yml

# SQLite version 3.x
#   gem install sqlite3-ruby (not necessary on OS X Leopard)
development:
  adapter: sqlite3
  database: db/development.sqlite3
  pool: 5
  timeout: 5000

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  adapter: sqlite3
  database: db/test.sqlite3
  pool: 5
  timeout: 5000

production:
  adapter: sqlite3
  database: db/production.sqlite3
  pool: 5
  timeout: 5000

Why you using unicode instead of utf8 in postgres.database.yml?

@Pserg it doesn't matter, unicode is just a standard

This is a huge help. thanks!

Hi,
Is it possible to use the same Postgres database name for development and production? E.g.

sample_postgres in the two environments?

if yes: How to ensure the same data content when running in production?

Is it possible to use the same Postgres database name for development and production? E.g.

@dangerrg Yes, nothing will prevent that

if yes: How to ensure the same data content when running in production?

I don't know what you mean

I am using Rails 5.2 and mysql2 gem. Even though i am using actual host name in my database.yml for production config, when i run the command RAILS_ENV=production rake db:migrate it is taking host name as local ip address of my machine and shows below error.

 rake aborted!
 ActiveRecord::StatementInvalid: Mysql2::Error: CREATE command denied to 
 user 'username'@'lamu02rr8r9fvh7.xxx.com' for table 
 'schema_migrations': CREATE TABLE `schema_migrations` (`version` 
 varchar(255) NOT NULL PRIMARY KEY)
 /Users/egoutham/.rvm/gems/ruby-2.5.3/gems/mysql2 
 0.4.10/lib/mysql2/client.rb:120:in `_query'
 /Users/egoutham/.rvm/gems/ruby-2.5.3/gems/mysql2- 
 0.4.10/lib/mysql2/client.rb:120:in `block in query'
 /Users/egoutham/.rvm/gems/ruby-2.5.3/gems/mysql2- 
 0.4.10/lib/mysql2/client.rb:119:in `handle_interrupt'
 /Users/egoutham/.rvm/gems/ruby-2.5.3/gems/mysql2- 
  0.4.10/lib/mysql2/client.rb:119:in `query'

And when i run the below command it shows different error.

  RAILS_ENV=production rake db:create
  rake aborted!
  ActiveRecord::StatementInvalid: Mysql2::Error: Access denied for user 
  'username'@'%' to database 'dashboard': CREATE DATABASE `dashboard` 
  DEFAULT CHARACTER SET `utf8mb4`
  /Users/egoutham/.rvm/gems/ruby-2.5.3/gems/mysql2- 
  0.4.10/lib/mysql2/client.rb:120:in `_query'
  /Users/egoutham/.rvm/gems/ruby-2.5.3/gems/mysql2- 
  0.4.10/lib/mysql2/client.rb:120:in `block in query'
  /Users/egoutham/.rvm/gems/ruby-2.5.3/gems/mysql2- 
  0.4.10/lib/mysql2/client.rb:119:in `handle_interrupt'
  /Users/egoutham/.rvm/gems/ruby-2.5.3/gems/mysql2- 
  0.4.10/lib/mysql2/client.rb:119:in `query'

config database.yml postgres

default: &default
  adapter: postgresql
  encoding: unicode
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  host: <%= ENV['DATABASE_HOST'] %>
  username: <%= ENV['DATABASE_USERNAME'] %>
  password: <%= ENV['DATABASE_PASSWORD'] %>

development:
  <<: *default
  database: app_development

test:
  <<: *default
  database: app_test

production:
  <<: *default
  database: app_production

if your docker-compose.yml file is something like this:

  db_postgres:
    image: postgres:latest
    environment:
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: mypassword
      POSTGRES_DB: mydb
    volumes:
      - postgres_data:/var/lib/postgresql/data
    networks:
      - mynetwork

so you can configure your database.yml with the host property using the name of your service in your docker-compose

default: &default
  adapter: postgresql
  encoding: unicode
  host: db_postgres # service name of the database in your Docker Compose
  username: myuser
  password: mypassword
  database: mydb

You can use this example in others sections "test", "development" or "production"