# PostgreSQL, templates, and test databases **URL:** \ **Category:** Rails\ **Created:** [October 5, 2007, 6:56am UTC](https://www.ruby-forum.com/t/postgresql-templates-and-test-databases/116401 "2007-10-05T06:56:06Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![marnen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/67e7ee/32.png) [@marnen](https://www.ruby-forum.com/u/marnen)\ **Post date:** [October 5, 2007, 6:56am UTC](https://www.ruby-forum.com/t/postgresql-templates-and-test-databases/116401/1 "2007-10-05T06:56:06Z")
Hi everyone. I’m having a bit of a problem with an application I’m currently developing with Rails 1.2.3 and PostgreSQL, attempting to use test-first development. Here’s the scenario: - I have the usual three databases, with migrations. Testing has been working beautifully. - I need to store geographic data, so I add PostGIS to all three databases (along with SpatialAdapter – thanks, Guilhem!). Development and production databases are happy. - However, testing no longer works. Why? Because the test database is always dropped and recreated – so that all the PostGIS goodness goes bye-bye before each round of tests. It is not feasible to put the PostGIS infrastructure in a YAML fixture file or a Ruby migration (the damned thing is huge!). However, PostgreSQL allows a template to be specified when creating a database – so my troubles would be over if I could specify something like “template: template\_postgis” in database.yml. But that doesn’t seem to work. So…is there a way to specify which template the test database will be created from in PostgreSQL? If not, what should I do? ## Best, Marnen Laibow-Koser Web developer / Composer Albany, NY, US [http://www.marnen.org](http://www.marnen.org) marnen AT marnen DOT org ---
**Author:** ![marnen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/67e7ee/32.png) [@marnen](https://www.ruby-forum.com/u/marnen)\ **Post date:** [October 5, 2007, 5:44pm UTC](https://www.ruby-forum.com/t/postgresql-templates-and-test-databases/116401/2 "2007-10-05T17:44:41Z")
> So…is there a way to specify which template the test database will be > created from in PostgreSQL? If not, what should I do? Perhaps not ideal for you, but you could add PostGIS to the template1 database… so that by default any new database you create would get it… -philip ---
**Author:** ![marnen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/67e7ee/32.png) [@marnen](https://www.ruby-forum.com/u/marnen)\ **Post date:** [October 5, 2007, 5:48pm UTC](https://www.ruby-forum.com/t/postgresql-templates-and-test-databases/116401/3 "2007-10-05T17:48:21Z")
Philip H. wrote: > Perhaps not ideal for you, but you could add PostGIS to the template1 > database… so that by default any new database you create would get > it… Definitely not ideal, but I had been considering this as well. However, I’d rather be able to specify _which_ template the test DB should be created from. I may try installing this patch: [http://dev.rubyonrails.org/ticket/9195](http://dev.rubyonrails.org/ticket/9195) but I would rather not mess around with the Rails core if I don’t have to. > -philip Best, Marnen Laibow-Koser ---
**Author:** ![marnen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/67e7ee/32.png) [@marnen](https://www.ruby-forum.com/u/marnen)\ **Post date:** [October 5, 2007, 6:50pm UTC](https://www.ruby-forum.com/t/postgresql-templates-and-test-databases/116401/4 "2007-10-05T18:50:00Z")
Kim S. wrote: > A different approach is to modify the purge task that is in > railties/lib/tasks/databases.rake. > […] Thanks. I will investigate this option, perhaps changing it to read the -T switch from database.yml (or maybe just apply that patch after all). I’m new enough to Rails that I don’t yet really know how things work under the hood, so this is very helpful. ## Best, Marnen Laibow-Koser removed\_email\_address@domain.invalid ---
**Author:** ![marnen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/67e7ee/32.png) [@marnen](https://www.ruby-forum.com/u/marnen)\ **Post date:** [October 5, 2007, 6:47pm UTC](https://www.ruby-forum.com/t/postgresql-templates-and-test-databases/116401/5 "2007-10-05T18:47:19Z")
On Oct 5, 2007, at 9:48 AM, Marnen Laibow-Koser wrote: > [http://dev.rubyonrails.org/ticket/9195](http://dev.rubyonrails.org/ticket/9195) > but I would rather not mess around with the Rails core if I don’t have > to. > > > -philip > > Best, > Marnen Laibow-Koser A different approach is to modify the purge task that is in railties/lib/tasks/databases.rake. I have changed the one I use to delete everything out of the test database instead of dropping it and recreating it. The diff of my version is: ``` desc "Empty the test database" task :purge => :environment do abcs = ActiveRecord::Base.configurations case abcs["test"]["adapter"] when "mysql" ActiveRecord::Base.establish_connection(:test) ActiveRecord::Base.connection.recreate_database(abcs ``` [“test”][“database”]) when “postgresql” ENV[‘PGHOST’] = abcs[“test”][“host”] if abcs[“test”] [“host”] ENV[‘PGPORT’] = abcs[“test”][“port”].to\_s if abcs [“test”][“port”] ENV[‘PGPASSWORD’] = abcs[“test”][“password”].to\_s if abcs [“test”][“password”] - ``` enc_option = "-E #{abcs["test"]["encoding"]}" if abcs ``` [“test”][“encoding”] ``` ActiveRecord::Base.clear_active_connections! ``` - ``` `dropdb -U "#{abcs["test"]["username"]}" #{abcs["test"] ``` [“database”]}` - ``` `createdb #{enc_option} -U "#{abcs["test"]["username"]}" # ``` {abcs[“test”][“database”]}` - ``` search_path = "--schema=#{search_path}" if search_path ``` - ``` `pg_dump -i -U "#{abcs["test"]["username"]}" -s -c -O # ``` {search\_path} #{abcs[“test”][“database”]} | ruby -n -e ‘if (/^DROP / =~ $_) && (/^DROP PROCEDURAL LANGUAGE/ !~ $_) && (/^DROP SCHEMA/ !~ $_) then $_.sub!(/;$/, " CASCADE;") if ($\_ =~ /^DROP SEQUENCE/) || ($\_ =~ /^DROP TABLE/); print; end’ \> db/test\_structure\_clean.sql` - ``` `psql -U "#{abcs["test"]["username"]}" #{abcs["test"] ``` [“database”]} \< db/test\_structure\_clean.sql` when “sqlite”,“sqlite3” ## Kim Kim S. - principal, Shrier and Deihl - [mailto:removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) Remote Unix Network Admin, Security, Internet Software Development Tinker Internet Services - Superior FreeBSD-based Web Hosting [http://www.tinker.com/](http://www.tinker.com/) ---
**Author:** ![marnen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/67e7ee/32.png) [@marnen](https://www.ruby-forum.com/u/marnen)\ **Post date:** [October 17, 2007, 6:24am UTC](https://www.ruby-forum.com/t/postgresql-templates-and-test-databases/116401/6 "2007-10-17T06:24:47Z")
Marnen Laibow-Koser wrote: > Thanks. I will investigate this option, perhaps changing it to read the > -T switch from database.yml (or maybe just apply that patch after all). > […] Update: I did indeed do this. It works quite well. ## Best, Marnen Laibow-Koser removed\_email\_address@domain.invalid