+ Reply to Thread
Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 30

In-memory PostgreSQL database

  1. In-memory PostgreSQL database

    Howdy all,

    I'm using PostgreSQL as the server for several of my applications, but
    unit testing the application by setting up and SQLite in-memory
    database during the test case. This is workable, and certainly better
    than the unacceptable overhead of connecting to a PostgreSQL server
    when running the unit tests.

    This has limitations, though: there are many PostgreSQL features that
    aren't supported in SQLite, so the unit test fixtures have to fake a
    lot of it for the sake of the PostgreSQL-specific application code.

    What options are there for creating and connnecting to a PostgreSQL
    database that exists only in memory, similar to the same thing in
    SQLite (and, I believe, MySQL)?

    --
    \ “Leave nothing to chance. Overlook nothing. Combine |
    `\ contradictory observations. Allow yourself enough time.” |
    _o__) —Hippocrates |
    Ben Finney

  2. Re: In-memory PostgreSQL database

    Ben Finney wrote:
    > I'm using PostgreSQL as the server for several of my applications, but
    > unit testing the application by setting up and SQLite in-memory
    > database during the test case. This is workable, and certainly better
    > than the unacceptable overhead of connecting to a PostgreSQL server
    > when running the unit tests.
    >
    > This has limitations, though: there are many PostgreSQL features that
    > aren't supported in SQLite, so the unit test fixtures have to fake a
    > lot of it for the sake of the PostgreSQL-specific application code.
    >
    > What options are there for creating and connnecting to a PostgreSQL
    > database that exists only in memory, similar to the same thing in
    > SQLite (and, I believe, MySQL)?


    There is no "embedded database" in PostgreSQL.

    I am very surprised that you even consider running unit tests with
    anything else than the database you want to use in production. Doesn't
    that take away much of the benefits of unit testing?

    If it is the overhead of establishing server connections that worries
    you, why don't you write your unit tests so that the connection is kept
    open?

    If you are worried about concurrent unit tests thrashing each other's data,
    you could have one database per developer in one PostgreSQL cluster
    on a central server.

    You can also install testing databases locally on the developers' machines,
    but that will mean that you have to have a database server process running
    on each machine.

    Yours,
    Laurenz Albe

  3. Re: In-memory PostgreSQL database

    Ben Finney wrote:

    > What options are there for creating and connnecting to a PostgreSQL
    > database that exists only in memory, similar to the same thing in
    > SQLite (and, I believe, MySQL)?


    I don't think you would gain anything by keeping the DB in RAM with
    postgres, because it's CPU-bound if there is little data.

    If the test DB fits in RAM, this means Postgres already leverages the
    memory as cache (if configured properly)

    In my current application, I truncate and refill all the tables after
    each of the 500 test.

    I tried creating a tablespace in ramdisk, and had no performance
    improvement at all.


  4. Re: In-memory PostgreSQL database

    Laurenz Albe wrote:

    > I am very surprised that you even consider running unit tests with
    > anything else than the database you want to use in production. Doesn't
    > that take away much of the benefits of unit testing?


    Not if the DB schema has been evolved with "emergent design" and makes
    no use of triggers, stored procedures, advanced check constraints... if
    you design an app like some of the youngsters do, the only difference
    between sqlite and postgres is that the latter has better support for
    unicode ;-)


  5. Re: In-memory PostgreSQL database

    Laurenz Albe writes:

    > I am very surprised that you even consider running unit tests with
    > anything else than the database you want to use in production.
    > Doesn't that take away much of the benefits of unit testing?


    No. The point of unit testing is to test a unit of code, not the
    entire application. To do so, it's best to isolate that section of
    code from its external dependencies; a "real" database certainly
    counts as an external dependency.

    For "acceptance testing" or any other kind of full-application testing
    (i.e. not unit testing), certainly one wants to use the real database.
    That's not the issue here though.

    > If it is the overhead of establishing server connections that
    > worries you, why don't you write your unit tests so that the
    > connection is kept open?


    The point is less about the overhead, and more about isolating the
    code so that each unit test exercises as small a part of the code as
    feasible.

    --
    \ "Pinky, are you pondering what I'm pondering?" "Well, I think |
    `\ so, Brain, but it's a miracle that this one grew back." -- |
    _o__) _Pinky and The Brain_ |
    Ben Finney

  6. Re: In-memory PostgreSQL database

    Marco Mariani writes:

    > Ben Finney wrote:
    >
    > > What options are there for creating and connnecting to a PostgreSQL
    > > database that exists only in memory, similar to the same thing in
    > > SQLite (and, I believe, MySQL)?

    >
    > I don't think you would gain anything by keeping the DB in RAM with
    > postgres, because it's CPU-bound if there is little data.


    I'm not interested in "performance", but rather reduction in external
    dependencies for unit tests (i.e. unit tests should not rely on a
    running database server).

    Now that we've established that I want what I actually asked for, can
    someone give suggestions on how to achieve it?

    --
    \ "If you can't annoy somebody there is little point in writing." |
    `\ -- Kingsley Amis |
    _o__) |
    Ben Finney

  7. Re: In-memory PostgreSQL database

    Ben Finney wrote:

    > I'm not interested in "performance", but rather reduction in external
    > dependencies for unit tests (i.e. unit tests should not rely on a
    > running database server).
    >
    > Now that we've established that I want what I actually asked for, can
    > someone give suggestions on how to achieve it?


    I'm not sure I understand.
    You cannot connect to postgres and use all of the postgres features,
    like you asked, withouth connecting to a running database server.

    Is this what you're asking?

    Because.. with sqlite in RAM you _ARE_ using the whole stack of the
    database, only there is no separate server process.

  8. Re: In-memory PostgreSQL database

    Marco Mariani writes:

    > Ben Finney wrote:
    >
    > > I'm not interested in "performance", but rather reduction in external
    > > dependencies for unit tests (i.e. unit tests should not rely on a
    > > running database server).

    >
    > I'm not sure I understand.
    > You cannot connect to postgres and use all of the postgres features,
    > like you asked, withouth connecting to a running database server.
    >
    > Is this what you're asking?


    Yes. I want to connect to a PostgreSQL database without the external
    dependency of a cluster and already-running server.

    > Because.. with sqlite in RAM you _ARE_ using the whole stack of the
    > database, only there is no separate server process.


    And no filesystem interaction, and no concurrency, and many other
    things that come with a "dedicated to this one process" database.

    All of these make it very useful for unit testing a unit of code that
    would, under normal operation, connect to the concurrent database with
    its on-disk cluster and all the rest.

    I'm wanting to have these advantages, but have it be PostgreSQL so
    that I don't have to make an in-memory SQLite database pretend to be
    an in-memory PostgreSQL one.

    --
    \ "Pinky, are you pondering what I'm pondering?" "I think so, |
    `\ Brain, but if we give peas a chance, won't the lima beans feel |
    _o__) left out?" -- _Pinky and The Brain_ |
    Ben Finney

  9. Re: In-memory PostgreSQL database

    Ben Finney wrote:

    > Yes. I want to connect to a PostgreSQL database without the external
    > dependency of a cluster and already-running server.
    >
    >> Because.. with sqlite in RAM you _ARE_ using the whole stack of the
    >> database, only there is no separate server process.

    >
    > And no filesystem interaction, and no concurrency, and many other
    > things that come with a "dedicated to this one process" database.


    Oh well.
    How can I phrase that... the whole of Postgres is on the server.
    There is nothing you can fake client-side.

    With sqlite, there is no server part, so the point is moot.


    > All of these make it very useful for unit testing a unit of code that
    > would, under normal operation, connect to the concurrent database with
    > its on-disk cluster and all the rest.


    Actually, I see nothing wrong with having a running db for testing
    purposes. I've read about that argument in some book, but I feel free to
    disagree.

    You could have mock objects that return sets of data, sure, and many
    people just do that.


  10. Re: In-memory PostgreSQL database

    Marco Mariani writes:

    > Actually, I see nothing wrong with having a running db for testing
    > purposes.


    Note that I'm specifically talking about unit testing here, which *by
    definition* is supposed to be isolating the unit of code under test as
    much as feasible.

    For any kind of testing other than unit testing, I agree than having a
    running database server is fine.

    > I've read about that argument in some book, but I feel free to
    > disagree.


    I think you've just got different terminology.

    > You could have mock objects that return sets of data, sure, and many
    > people just do that.


    Yes, that's what I'm doing in my unit tests at the moment.

    However, mocking the feature-rich behaviour of PostgreSQL is rather
    cumbersome, hence my desire to have PostgreSQL do the work of
    implementing itself, without so many other dependencies during the
    unit test cases.

    Thanks for your feedback so far, it's useful to see what others think.

    --
    \ "Know what I hate most? Rhetorical questions." -- Henry N. Camp |
    `\ |
    _o__) |
    Ben Finney

+ Reply to Thread
Page 1 of 3 1 2 3 LastLast