BitBadger.Npgsql.Documents
Migrate to v2
Why
In version 1 of this library, the document tables used by this library had two columns: id and data. id served as the primary key, and data was the JSONB column for the document. Since its release, the author learned that a field in a JSONB column could have a unique index that would then serve the role of a primary key.
Version 2 of this library implements this change, both in table setup and in how it constructs queries that occur by a document's ID.
How
On the GitHub release page, there is a MigrateToV2 utility program - one for Windows, and one for Linux. Download and extract the single file in the archive; it requires no installation. It uses an environment variable for the connection string, and takes a table name and an ID column field via the command line.
A quick example under Linux/bash (assuming the ID field in the JSON document is named Id)…
export PGDOC_CONN_STR="Host=localhost;Port=5432;User ID=example_user;Password=example_pw;Database=my_docs"
./MigrateToV2 ex.doc_table
./MigrateToV2 ex.another_one
If the ID field has a different name, it can be passed as a second parameter. The utility will display the table name and ID field and ask for confirmation; if you are scripting it, you can set the environment variable PGDOC_I_KNOW_WHAT_I_AM_DOING to true, and it will bypass this confirmation. Note that the utility itself is quite basic; you are responsible for giving it sane input. If you have customized the tables or the JSON serializer, though, keep reading.
What
If you have extended the original tables, you may need to handle this migration within either PostgreSQL/psql or your code. The process entails two steps. First, create a unique index on the ID field; in this example, we'll use name for the example ID field. Then, drop the id column. The below SQL will accomplish this for the fictional my_table table.
CREATE UNIQUE INDEX idx_my_table_key ON my_table ((data ->> 'name')); ALTER TABLE my_table DROP COLUMN id;
If the ID field is different, you will also need to tell the library that. Use Configuration.UseIdField("name") (C#) / Configuration.useIdField "name" (F#) to specify the name. This will need to be done before queries are executed, as the library uses this field for ID queries. See the Setting Up instructions for details on this new configuration parameter.