With the CData Cmdlets users can install a data module, set the connection properties, and start scripting. This section provides examples of using our ApacheCouchDB Cmdlets with native PowerShell cmdlets, like the CSV import and export cmdlets.

Installing and Connecting

If you have PSGet, installing the cmdlets can be accomplished from the PowerShell Gallery with the following command. You can also obtain a setup from the CData site.

Install-Module ApacheCouchDBCmdlets

The following line is then added to your profile, loading the cmdlets on the next session:

Import-Module ApacheCouchDBCmdlets;

You can then use the Connect-ApacheCouchDB cmdlet to create a connection object that can be passed to other cmdlets:

$conn = Connect-ApacheCouchDB -User 'abc123' -Password 'abcdef'

Connecting to Apache CouchDB

Apache CouchDB supports three types of authentication:

• Basic: Basic username/password authentication.
• JWT: Authentication with JWT token.
• None: Anonymous access for databases that are public.

If you want your users (or JWT tokens) to have access to only specific databases, you have to configure the admin_only_all_dbs option in the Apache CouchDB instance to grant all users access to the "/_all_dbs" endpoint which is required by the cmdlet for listing tables. Otherwise the cmdlet won't be able to connect, because the endpoint will throw an authentication error.

Basic Authentication

Set the following to connect to data:

• AuthScheme: Basic.
• Url: The Url of your Apache CouchDB instance. For example: http://localhost:5984
• User The Apache CouchDB user account used to authenticate.
• Password The Apache CouchDB password associated with the authenticating user.

JWT Authentication

The following connection properties are required and must always be specified:

• AuthScheme: JWT.
• Url: The Url of your Apache CouchDB instance. For example: http://localhost:5984.

From here you can:

1. Set the following so that the cmdlet automatically generates (and refreshes if applicable) the tokens for you:

Required

• JWTSubject: The name of the user to assign to the JWT token.
• JWTAlgorithm: The algorithm to use for the JWT token signature.
• JWTKeyType: The type of the encryption key.
• JWTKey: The encryption key used to sign the JWT token generated by the cmdlet.

Optional

• JWTIssuer: The issuer of the JWT token.
• JWTExpiration: How long the JWT token should remain valid, in seconds.
• JWTHeaders: A collection of extra headers that should be included in the JWT header.
• JWTClaims: A collection of extra claims that should be included in the JWT payload.
• CredentialsLocation: The location of the settings file where the JWT token is saved.

2. Or you can generate the tokens yourself manually and pass them to the cmdlet by using the JWTToken connection property.

Generating the key pair

When using asymmetric algorithms to sign the tokens, you must generate a private/public key pair. For that, a cryptographic library like OpenSSL can be used. For example:

# generate private key
openssl genrsa --out private_rsa256.pem 2048

# extract public key
openssl rsa -in private_rsa256.pem -pubout > public_rsa256.pem

JWT Configurations

Refer to CouchDB JWT Authentication Documentation for the following.

The alg and sub are required claims and will always be validated by the Apache CouchDB instance. Other required claims can be configured in the server (see required_claims). In that case, you must use JWTHeaders and JWTClaims so that the cmdlet can include those additional claims when generating the JWT token.

You can use roles_claim_name or the roles_claim_path options to assign roles to the JWT tokens.

Refer to the following example for configuring the server and the cmdlet:

server configuration

[chttpd]
...
authentication_handlers = {chttpd_auth, jwt_authentication_handler}, {chttpd_auth, cookie_authentication_handler}, {chttpd_auth, default_authentication_handler}
admin_only_all_dbs = false
...

[jwt_auth]
...
required_claims = exp
roles_claim_path = my.nested._couchdb\.roles
rsa:rsa_256 = -----BEGIN PUBLIC KEY-----\nYOUR_PUBLIC_KEY\n-----END PUBLIC KEY-----\n
...

cmdlet configuration

Url=http://localhost:5984;
JWTSubject=JWT User 1;
JWTAlgorithm=RS256;
JWTKeyType=PEMKEY_FILE;
JWTKey=PATH_TO_FOLDER\private_rsa256.pem;
JWTHeaders=kid : rsa_256 | Custom Header 1 : Test 1;
JWTClaims= my : eyJuZXN0ZWQiOnsiX2NvdWNoZGIucm9sZXMiOlsidXNlcjIiXX19 | Custom Claim 1 : Test 1;

Anonymous

Set the following to connect to data:

• AuthScheme: None.
• Url: The Url of your Apache CouchDB instance. For example: http://localhost:5984
• PublicDatabases: A comma-separated list of public databases to list as tables.

Retrieving Data

The Select-ApacheCouchDB cmdlet provides a native PowerShell interface for retrieving data:

$results = Select-ApacheCouchDB -Connection $conn -Table "Movies" -Columns @("MovieRuntime, MovieRating") -Where "MovieRating='R'"

The Invoke-ApacheCouchDB cmdlet provides an SQL interface. This cmdlet can be used to execute an SQL query via the Query parameter.

Piping Cmdlet Output

The cmdlets return row objects to the pipeline one row at a time. The following line exports results to a CSV file:

Select-ApacheCouchDB -Connection $conn -Table Movies -Where "MovieRating = 'R'" | Select -Property * -ExcludeProperty Connection,Table,Columns | Export-Csv -Path c:\myMoviesData.csv -NoTypeInformation

You will notice that we piped the results from Select-ApacheCouchDB into a Select-Object cmdlet and excluded some properties before piping them into an Export-CSV cmdlet. We do this because the CData Cmdlets append Connection, Table, and Columns information onto each row object in the result set, and we do not necessarily want that information in our CSV file.

However, this makes it easy to pipe the output of one cmdlet to another. The following is an example of converting a result set to JSON:

 
PS C:\> $conn  = Connect-ApacheCouchDB -User 'abc123' -Password 'abcdef'
PS C:\> $row = Select-ApacheCouchDB -Connection $conn -Table "Movies" -Columns (MovieRuntime, MovieRating) -Where "MovieRating = 'R'" | select -first 1
PS C:\> $row | ConvertTo-Json
{
  "Connection":  {

  },
  "Table":  "Movies",
  "Columns":  [

  ],
  "MovieRuntime":  "MyMovieRuntime",
  "MovieRating":  "MyMovieRating"
} 

Deleting Data

The following line deletes any records that match the criteria:

Select-ApacheCouchDB -Connection $conn -Table Movies -Where "MovieRating = 'R'" | Remove-ApacheCouchDB

Modifying Data

The cmdlets make data transformation easy as well as data cleansing. The following example loads data from a CSV file into Apache CouchDB, checking first whether a record already exists and needs to be updated instead of inserted.

Import-Csv -Path C:\MyMoviesUpdates.csv | %{
  $record = Select-ApacheCouchDB -Connection $conn -Table Movies -Where ("Id = `'"+$_.Id+"`'")
  if($record){
    Update-ApacheCouchDB -Connection $conn -Table Movies -Columns @("MovieRuntime","MovieRating") -Values @($_.MovieRuntime, $_.MovieRating) -Where "Id  = `'$_.Id`'"
  }else{
    Add-ApacheCouchDB -Connection $conn -Table Movies -Columns @("MovieRuntime","MovieRating") -Values @($_.MovieRuntime, $_.MovieRating)
  }
}