0

I want to drop/create a database, run my test cases every time I run my app. From my understanding postgresql must know the database when it creates the connection. So when I write this

new NpgsqlConnection("host=localhost;username=foo;password='bar';");

postgresql will automatically use the database foo. Because I'm using foo I can't drop/create it. So my question is how do I drop/create a database each time I run my app and is it possible to have an in memory database?

3 Answers3

0

You have to connect several times for this. If you would use psql, you could script it with \c metacommand, eg:

t=# create database foo1;
CREATE DATABASE
t=# \c foo1;
You are now connected to database "foo1" as user "vao".
foo1=# begin;
BEGIN
foo1=# select now();
              now
-------------------------------
 2017-04-03 21:00:34.296285+01
(1 row)

foo1=# end;
COMMIT
foo1=# \c t
You are now connected to database "t" as user "vao".
t=# drop database foo1;
DROP DATABASE

So you could save all above commands to sql file and run it with psql, but in you case, you have to make new NpgsqlConnection, create database, connect to it with new new NpgsqlConnection, run tests, connect back to foo and rop db from it...

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
  • I'll have to do it in .NET. I don't want to switch between the terminal and .NET every time I run something. I don't have psql installed on windows (there was an error last time I tried). I'm using putty to forward local connections to my debian VM which is running postgresql –  Apr 03 '17 at 20:17
0

That's what the administrative database postgres is there for.

You must close all connections to database foo, establish a connection to postgres with a user that has the CREATEDB privilege and create the database.

To facilitate initialization of the new database, you can use a preconfigured template database that you can specify during CREATE DATABASE.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • I don't see the connection. – Laurenz Albe Apr 05 '17 at 06:50
  • Your suggestion still failed and IDK why. However I got around the problem. Instead of using an alternative schema/database, I used a different name than my username and I was able to drop/create fine –  Apr 05 '17 at 06:53
  • Then you probably tried to use a user that is no superuser or does not own the database. The error message would have told you. – Laurenz Albe Apr 05 '17 at 07:58
0

I solved it by having create database permission and using a database that isn't my name (for some reason I can't connect to a dummy database and drop the database if it's my name).