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.

Connecting to Apache CouchDB

Set the URL connection property to the URL of your Apache CouchDB instance. For example: http://localhost:5984

If you want your users (or JWTs) to have access only to specific databases, configure the admin_only_all_dbs option in your Apache CouchDB instance to grant all users access to the "/_all_dbs" endpoint, which is required by the cmdlet for listing tables.

Authenticating to Apache CouchDB

Apache CouchDB supports three types of authentication:

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

Basic Authentication

Set the following to connect to data:

• AuthScheme: Basic.
• User The Apache CouchDB user account used to authenticate.
• Password The Apache CouchDB password associated with the authenticating user.

JWT Authentication

Set AuthScheme to JWT.

You can either automatically generate (and, if applicable, refresh) a JWT or manually generate one:

Automatic Token Generation

Configure the cmdlet to automatically generate the tokens:

Required

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

Optional

• JWTIssuer: The issuer of the JWT.
• JWTExpiration: The duration for which the JWT remains valid, in seconds.
• JWTHeaders: A collection of extra headers to include in the JWT header.
• JWTClaims: A collection of extra claims to include in the JWT payload.
• CredentialsLocation: The filepath of the settings file containing the JWT. If a settings file does not exist in this location, the cmdlet creates a new settings file when it retrieves a JWT.

Manual Token Generation

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.

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

An example configuration of the server and corresponding sample connection string are shown below:

Example 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
...

Example Connection String

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 Authentication

Set the following to connect to data:

• AuthScheme: None.
• PublicDatabases: A comma-separated list of public databases to list as tables.

Creating a Connection Object

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'

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)
  }
}