Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ralWJ-00BLrY-Qx for pgsql-general@arkaria.postgresql.org; Thu, 15 Feb 2024 23:51:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1ralWH-00G2Ie-TQ for pgsql-general@arkaria.postgresql.org; Thu, 15 Feb 2024 23:51:57 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ralWH-00G2IV-I8 for pgsql-general@lists.postgresql.org; Thu, 15 Feb 2024 23:51:57 +0000 Received: from mail.hjp.at ([212.17.106.138] helo=rorschach.hjp.at) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ralWE-006xQS-PP for pgsql-general@lists.postgresql.org; Thu, 15 Feb 2024 23:51:56 +0000 Received: by rorschach.hjp.at (Postfix, from userid 1000) id D4B8896D7; Fri, 16 Feb 2024 00:51:52 +0100 (CET) Date: Fri, 16 Feb 2024 00:51:52 +0100 From: "Peter J. Holzer" To: pgsql-general@lists.postgresql.org Subject: Re: PostgreSQL DB in prod, test, debug Message-ID: <20240215235152.va7wohn5ftcxi5a4@hjp.at> Mail-Followup-To: pgsql-general@lists.postgresql.org References: <7304F877-D043-4395-867E-014F48EB71ED@yesql.se> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="2tf7tmvitb63zria" Content-Disposition: inline In-Reply-To: <7304F877-D043-4395-867E-014F48EB71ED@yesql.se> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --2tf7tmvitb63zria Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On 2024-02-14 11:25:48 +0100, Daniel Gustafsson wrote: > > On 14 Feb 2024, at 10:59, Simon Connah = wrote: >=20 > > This is probably a stupid question so I apologies in advance. >=20 > There is no such thing. >=20 > > What I think is the best way to do this is to do a pg_dump of the > > database (using the --schema-only flag) and then load it into a test > > only database that gets created at the start of the unit tests and > > destroyed at the end. The automated tests will insert, update, > > delete and select data to test if it all still works. >=20 > If the source of truth for your schema is the database, then sure. If the > source of truth is a .sql file in your source code repository then you sh= ould > use that. I sort of do both for one of my projects: I originally created the SQL script by running pg_dump on a manually constructed test database (I do have code to create the empty schema, but I had to insert the test data manually). That script went into the git repo. The test files all contain a fixture which drops and recreates the test database using that sql file. When I add a new test case I try to make do with the existing test data. When I need additional data for a new test case, I create a new pristine test database using the sql file, add the new data, and then create a new sql file using sql_dump which is then committed with the test cases. Same for migrations: If I need to migrate the schema, I run the migration on the test database, then dump and commit it. This project is small enough (86 tests in 10 files) that all test cases can use the same test data. However, I could easily use different test data for different tests. hp --=20 _ | Peter J. Holzer | Story must make more sense than reality. |_|_) | | | | | hjp@hjp.at | -- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" --2tf7tmvitb63zria Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmXOo5QACgkQ8g5IURL+ KF3LUA/+O5O5sfb9WLjzG4DSOEqWqW/g7UwFCGTxqqJMnfQL3d719Jc0AXjz34LR sUf0zT53uSTgVFdzOnx5cMlzJAE3mrUf7fgUMgPAYD4Nm61ML6zSpV154uDfA1QW vScLQPQwov/yruLjrga+nJafj/piHZGxjhcEUMNA7F7cfuKsfAhTPADRkuSARIpp 1SPnZYqs90JACIU2jwStUUpEMVaylABdQ3sbd71MIYraohChV1w4Va60rli4hbWi TsU9rXWrpHBsGZzDS1ndEKdVcv0c7KwK4bxAh/gZzobOY8xwD5ZAP+VgU75otj8K MCN7fVeb+WK/Lk+pMNke1rtVIB0LCB8LvQ8evLz5G4iytIa3xltpqtq+Hn5geS9J cRVqnbOvtjk4mMKUh1pBYlwLvfgFD/Jo3W8/0HOPHQo7/CG14EySAqNIIhdnTe+M oxFmyELmRP4RuSXG7KncOWsANE4KMTBfq56yxUTqwD/DgiuM29O89Krzt5VRmeOb ULvydi0JTFYcSVkDukEbkxcYb42wei6La4q0Uud92UyCQOUyCQzJf9RwQvUvgLgj qAQUCfqQap8hYVS4BL4LLFVKICKaNI6LEExkNSUO5iyJPtgIAsvgGEflVgoVE0hv cTKNDtQ/ROIggX4EQ6D5NLAO2KJKjHfPv6rMgGPS4iWp5+HP8YM= =pUFG -----END PGP SIGNATURE----- --2tf7tmvitb63zria--