Exploring CockroachDB With Microsoft PowerShell
In this installment of our series, we are going to venture out into the world of .NET through a scripting language out of Microsoft called PowerShell.
CORE
·
Join the DZone community and get the full member experience.
Join For Free- CockroachDB and Docker Compose.
- CockroachDB, Minio and Docker Compose.
- CDC with CockroachDB and Minio.
- CDC with CockroachDB and Hadoop Ozone.
- Exploring CockroachDB with Jupyter Notebook and Python.
- Exploring CockroachDB with Jupyter Notebook and R.
- Exploring CockroachDB with Jupyter Notebook and Microsoft Powershell.
- Information on CockroachDB can be found here.
- Information on Jupyter Notebook can be found here.
- Information on Microsoft Powershell can be found here.
PowerShell is pre-installed on Windows platform and it is now available on OSX and Linux. I originally attempted to build this tutorial on OSX but I was not able to get Npgsql, a Postgresql .Net driver to work. Instead, I will continue using a Jupyter Notebook container with Ubuntu Linux as my PowerShell platform. Microsoft has made a PowerShell kernel for Jupyter available and we will be using this image throughout the tutorial.
DISCLAIMER: This tutorial is a bit of a "click bait". As you will see further into the post, I was unable to make Npgsql driver to load into the Jupyter userspace. I believe it is due to an open bug, as of the time of writing, that prevents me from completing the tutorial entirely in the Jupyter Notebook.
The code I will demonstrate, does indeed work in powershell console. I filed an issue with the kernel author for my situation as I cannot explain why it works in the console even though the aforemention bug states loading assemblies pre-installed from NuGet is currently not supported. Needless to say I may be going down the wrong rabbit hole. On with the tutorial..
Install PowerShell
I am still using my old docker-compose script with subtle changes
version: '3.7'
services:
crdb:
image: cockroachdb/cockroach:v19.2.4
container_name: crdb
ports:
- "26257:26257"
- "8080:8080"
command: start-single-node --insecure
volumes:
- ${PWD}/cockroach-data/crdb:/cockroach/cockroach-data:rw
jupyter:
image: jaykul/powershell-notebook-base
container_name: jupyter
environment:
- GRANT_SUDO=yes
ports:
- "8888:8888"
volumes:
- $PWD:/home/jovyan/workEven though we are not going to rely on the Jupyter Notebook for the entirety of the post, we're going to use its OS to install PowerShell console.
Attach to the container as root:
docker exec --user root -it jupyter bash
The image is based on Ubuntu 16.04 and your steps may very based on the version and flavor of the OS:
mkdir -p /tmp/powershell && cd /tmp/powershell && \
sudo apt-get update && apt-get install -y apt-transport-https && \
wget -q https://packages.microsoft.com/config/ubuntu/16.04/packages-microsoft-prod.deb && \
sudo dpkg -i packages-microsoft-prod.deb && \
sudo apt update && sudo apt install powershellExplore Microsoft PowerShell
Attach to the container as regular user:
docker exec -it jupyter bashTyping pwsh at the shell prompt will drop you into the PowerShell console.
PowerShell 7.0.0
Copyright (c) Microsoft Corporation. All rights reserved.
https://aka.ms/powershell
Type 'help' to get help.
PS /home/jovyan>Get-Help is the most obvious starting point in PowerShell if you're unfamiliar. Here's the abbreviated output of that command.
...
EXAMPLES:
Save-Help : Download help files from the Internet and saves
them on a file share.
Update-Help : Downloads and installs help files from the
Internet or a file share.
Get-Help Get-Process : Displays help about the Get-Process cmdlet.
Get-Help Get-Process -Online
: Opens online help for the Get-Process cmdlet.
Help Get-Process : Displays help about Get-Process one page at a time.
Get-Process -? : Displays help about the Get-Process cmdlet.
Get-Help About_Modules : Displays help about PowerShell modules.
Get-Help remoting : Searches the help topics for the word "remoting."
...I see Get-Process -? command and find it interesting.
PS /Users/artem> Get-Process -?
NAME
Get-Process
SYNTAX
Get-Process [[-Name] <string[]>] [-Module] [-FileVersionInfo] [<CommonParameters>]
Get-Process [[-Name] <string[]>] -IncludeUserName [<CommonParameters>]
Get-Process -Id <int[]> [-Module] [-FileVersionInfo] [<CommonParameters>]
Get-Process -Id <int[]> -IncludeUserName [<CommonParameters>]
Get-Process -InputObject <Process[]> [-Module] [-FileVersionInfo] [<CommonParameters>]
Get-Process -InputObject <Process[]> -IncludeUserName [<CommonParameters>]
ALIASES
gpsIf you were executing the following command on the same node as CockroachDB service, you could find the `CockroachDB process like so:
PS /Users/artem> gps -Name cockroach
NPM(K) PM(M) WS(M) CPU(s) Id SI ProcessName
------ ----- ----- ------ -- -- -----------
0 0.00 211.80 7.50 64538 …45 cockroachInstall Npgsql Postgres Driver for .Net
Npgsql is a .Net driver for Postgresql and we will leverage this library while working with CockroachDB. The recommended way to install this library is with NuGet Package Manager for .Net. Luckily, we can programmatically install Npgsql from NuGet from within PowerShell.
List available modules, specifically, we're looking for Npgsql driver loaded.
PS /home/jovyan> Get-Module -ListAvailable
Directory: /opt/microsoft/powershell/7/Modules
ModuleType Version PreRelease Name PSEdition ExportedCommands
---------- ------- ---------- ---- --------- ----------------
Manifest 1.2.5 Microsoft.PowerShell.Archive Desk {Compress-Archive, Expand-…
Manifest 7.0.0.0 Microsoft.PowerShell.Host Core {Start-Transcript, Stop-Tr…
Manifest 7.0.0.0 Microsoft.PowerShell.Management Core {Add-Content, Clear-Conten…
Manifest 7.0.0.0 Microsoft.PowerShell.Security Core {Get-Credential, Get-Execu…
Manifest 7.0.0.0 Microsoft.PowerShell.Utility Core {Export-Alias, Get-Alias, …
Script 1.4.6 PackageManagement Desk {Find-Package, Get-Package…
Script 2.2.3 PowerShellGet Desk {Find-Command, Find-DSCRes…
Script 2.0.5 PSDesiredStateConfiguration Core {Configuration, New-DscChe…
Script 2.0.0 PSReadLine Desk {Get-PSReadLineKeyHandler,…
Binary 2.0.3 ThreadJob Desk Start-ThreadJobObviously the Npgsql library is not available. Let's list available repos, specifically, we're looking for Nuget where Npgsql is available:
PS /home/jovyan> Get-PackageSource
Name ProviderName IsTrusted Location
---- ------------ --------- --------
PSGallery PowerShellGet False https://www.powershe…
If unavailable, register NuGet repo:
Register-PackageSource -Name MyNuGet -Location https://www.nuget.org/api/v2 -ProviderName NuGetSearch for the package after the repo is added:
PS /home/jovyan> Find-Package Npgsql
Name Version Source Summary
---- ------- ------ -------
Npgsql 4.1.3.1 MyNuGet Npgsql is the open source .NET data prov…Install Npgsql from the newly added repo, -Scope controls the location of the installed package:
PS /home/jovyan> Install-Package Npgsql -Force -SkipDependencies -Scope CurrentUser
Name Version Source Summary ---- ------- ------ ------- Npgsql 4.1.3.1 MyNuGet Npgsql is the open source .NET data prov…
PS /home/jovyan>Verify Npgsql is installed:
PS /home/jovyan> Get-Package Npgsql
Name Version Source ProviderName
---- ------- ------ ------------
Npgsql 4.1.3.1 /home/jovyan/.local/share/Packa… NuGet
Load Sample Workload into CockroachDB
cockroach workload init movr
Since I'm using a container, command will looke slightly differently:
docker exec -it crdb ./cockroach workload init movr
I200311 16:21:22.748581 1 workload/workloadsql/dataload.go:135 imported users (0s, 50 rows)
I200311 16:21:22.760437 1 workload/workloadsql/dataload.go:135 imported vehicles (0s, 15 rows)
I200311 16:21:22.789719 1 workload/workloadsql/dataload.go:135 imported rides (0s, 500 rows)
I200311 16:21:22.820200 1 workload/workloadsql/dataload.go:135 imported vehicle_location_histories (0s, 1000 rows)
I200311 16:21:22.851894 1 workload/workloadsql/dataload.go:135 imported promo_codes (0s, 1000 rows)
Set up a user for our instance:
docker exec -it crdb ./cockroach sql --insecure
CREATE USER IF NOT EXISTS maxroach;
GRANT ALL ON DATABASE movr TO maxroach;
GRANT ALL ON TABLE movr.* TO maxroach;
\q
Verify maxroach has sufficient priveleges:
docker exec -it crdb ./cockroach sql --insecure --user maxroach --database movr#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
# Server version: CockroachDB CCL v19.2.4 (x86_64-unknown-linux-gnu, built 2020/02/06 21:55:19, go1.12.12) (same version as client)
# Cluster ID: 28d1b970-83fa-4727-a588-78c0aa5f74fb
#
# Enter \? for a brief introduction.
#
maxroach@:26257/movr> SELECT * FROM rides LIMIT 1;
id | city | vehicle_city | rider_id | vehicle_id | start_address | end_address | start_time | end_time | revenue
+--------------------------------------+-----------+--------------+--------------------------------------+--------------------------------------+--------------------------------+-----------------------------+---------------------------+---------------------------+---------+
ab020c49-ba5e-4800-8000-00000000014e | amsterdam | amsterdam | c28f5c28-f5c2-4000-8000-000000000026 | aaaaaaaa-aaaa-4800-8000-00000000000a | 1905 Christopher Locks Apt. 77 | 66037 Belinda Plaza Apt. 93 | 2018-12-13 03:04:05+00:00 | 2018-12-14 08:04:05+00:00 | 77.00
(1 row)
Time: 5.0038ms
maxroach@:26257/movr>At this point we can start working with CockroachDB from within the PowerShell console.
Going back to the jupyter container, to quickly drop into the PowerShell console while attaching to the container:
docker exec -it jupyter pwsh
12:36 $ docker exec -it jupyter pwsh
PowerShell 7.0.0
Copyright (c) Microsoft Corporation. All rights reserved.
https://aka.ms/powershell
Type 'help' to get help.
PS /home/jovyan>
We now need to import the Npgsql library into the PowerShell session, but first we need to find it.
The equivalent of ls in PowerShell is Get-ChildItem but ls works just as well. The NuGet packages get installed into the '.local' directory so drilling down into the folder structure, we find the package.
PS /home/jovyan> Get-ChildItem ./.local/share/PackageManagement/NuGet/Packages/
Directory: /home/jovyan/.local/share/PackageManagement/NuGet/Packages
Mode LastWriteTime Length Name
---- ------------- ------ ----
d---- 3/11/2020 4:19 PM Npgsql.4.1.3.1
PS /home/jovyan>Load Npgsql Package as an Assembly Into PowerShell
I found four different ways to load an assembly but sadly, none of them had worked in Jupyter Notebook. First three work fine in the pwsh console. The last one is a workaround I found but due to my limited knowledge of .Net, I cannot get it to work. In theory, it should work.
PS /home/jovyan> Import-Module '/home/jovyan/.local/share/PackageManagement/NuGet/Packages/Npgsql.4.1.3.1/lib/netstandard2.1/Npgsql.dll'
The same can be achieved with the following:
PS /home/jovyan> Add-Type -Path '/home/jovyan/.local/share/PackageManagement/NuGet/Packages/Npgsql.4.1.3.1/lib/netstandard2.1/Npgsql.dll'
And another approach, instead of relying on PowerShell registering the package management capabilities, we fetch the Npgsql binary manually, expanding it and trying to load it.
# https://books.google.com/books?id=yxiUDwAAQBAJ&pg=PA138&lpg=PA138&dq=powershell+npgsql&source=bl&ots=MCjIQZIpC6&sig=ACfU3U0YPc74WsKicn02hW5VelSTEFPVKQ&hl=en&sa=X&ved=2ahUKEwi8p9GV0I3oAhXFc98KHZrQCPwQ6AEwB3oECAsQAQ#v=onepage&q=powershell%20npgsql&f=false
Invoke-WebRequest -Uri https://www.nuget.org/api/v2/package/Npgsql/4.1.3.1 -OutFile .\postgres.zip
Expand-Archive .\postgres.zip
Add-Type -Path /home/jovyan/.local/postgres/lib/netstandard2.1/Npgsql.dll -ReferencedAssemblies "/usr/src/jupyter-powershell/System.*.dll"Yet another approach to load the NuGet library into memory and write it out as a raw assembly. Sadly, it didn't work in the pwsh or the Jupyter Notebook.
PS /home/jovyan> $zip = [System.IO.Compression.ZipFile]::Open((Get-Package Npgsql).Source,"Read")
PS /home/jovyan> $memStream = [System.IO.MemoryStream]::new()
PS /home/jovyan> $reader = [System.IO.StreamReader]($zip.entries[2]).Open()
PS /home/jovyan> $reader.BaseStream.CopyTo($memStream)
PS /home/jovyan> [byte[]]$rawAssembly = $memStream.ToArray()
PS /home/jovyan> $reader.Close()
PS /home/jovyan> $zip.dispose()
PS /home/jovyan> [System.Reflection.Assembly]::Load($rawAssembly)
MethodInvocationException: Exception calling "Load" with "1" argument(s): "Bad IL format."
PS /home/jovyan>
Finally, after executing Import-Module or Add-Type, you should have the assembly loaded, quick way to confirm is
PS /home/jovyan> [System.AppDomain]::CurrentDomain.GetAssemblies() | Select-String "Npgsql"
Npgsql, Version=4.1.3.1, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7
PS /home/jovyan>
Connect to CockroachDB with PowerShell
# Creating a new connection
$connection = [Npgsql.NpgsqlConnection]::new('Host=crdb;Port=26257;Username=maxroach;Password="";Database=movr')
$connection.Open()
$command = [Npgsql.NpgsqlCommand]::new('SELECT * from rides limit 10', $connection)
# https://www.npgsql.org/doc/faq.html
# Exception calling "GetValue" with "1" argument(s): "The field 'city' has a
# type currently unknown to Npgsql (OID 25). You can retrieve it as a string by
# marking it as unknown, please see the FAQ."
# This is only necessary if there are data types in your database that are not known to Npgsql
#$command.AllResultTypesAreUnknown = true;
$reader = $command.ExecuteReader();
$count = 0;
while ($reader.Read())
{
$reader.GetType().Name
$reader.GetDataTypeName(0)
Write-Host $($reader.GetValue(0), $reader.GetValue(1))
$count++
}
Write-Host "Total records: $($count)";
$connection.CloseAsync()After you write that into the console and execute, you will get:
...
PS /home/jovyan> $reader = $command.ExecuteReader();
PS /home/jovyan>
PS /home/jovyan> $count = 0;
PS /home/jovyan> while ($reader.Read())
>> {
>> $reader.GetType().Name
>> $reader.GetDataTypeName(0)
>> Write-Host $($reader.GetValue(0), $reader.GetValue(1))
>> $count++
>> }
NpgsqlDataReader
uuid
ab020c49-ba5e-4800-8000-00000000014e amsterdam
NpgsqlDataReader
uuid
ab851eb8-51eb-4800-8000-00000000014f amsterdam
NpgsqlDataReader
uuid
ac083126-e978-4800-8000-000000000150 amsterdam
NpgsqlDataReader
uuid
ac8b4395-8106-4800-8000-000000000151 amsterdam
NpgsqlDataReader
uuid
ad0e5604-1893-4800-8000-000000000152 amsterdam
NpgsqlDataReader
uuid
ad916872-b020-4800-8000-000000000153 amsterdam
NpgsqlDataReader
uuid
ae147ae1-47ae-4800-8000-000000000154 amsterdam
NpgsqlDataReader
uuid
ae978d4f-df3b-4800-8000-000000000155 amsterdam
NpgsqlDataReader
uuid
af1a9fbe-76c8-4800-8000-000000000156 amsterdam
NpgsqlDataReader
uuid
af9db22d-0e56-4800-8000-000000000157 amsterdam
PS /home/jovyan> Write-Host "Total records: $($count)";
Total records: 10
PS /home/jovyan> $connection.CloseAsync()
Id IsCompleted Status
-- ----------- ------
1 True RanToCompletionExplore CockroachDB From the PowerShell Console a Bit More
Let's exit out of the container and start from scratch:
Add-Type -Path '/home/jovyan/.local/share/PackageManagement/NuGet/Packages/Npgsql.4.1.3.1/lib/netstandard2.1/Npgsql.dll'
$connection = [Npgsql.NpgsqlConnection]::new('Host=crdb;Port=26257;Username=maxroach;Password="";Database=movr')
$connection.Open()
$command = [Npgsql.NpgsqlCommand]::new('SHOW CREATE TABLE vehicles', $connection)Now that we have $command object, let's explore what available methods are available to us.
Type $command. and hit tab.
PS /home/jovyan> $command.
AllResultTypesAreUnknown UnknownResultTypeList ExecuteScalar
CommandText UpdatedRowSource ExecuteScalarAsync
CommandTimeout Cancel GetHashCode
CommandType Clone GetLifetimeService
Connection CreateParameter GetType
Container Dispose InitializeLifetimeService
DesignTimeVisible DisposeAsync Prepare
IsPrepared Equals PrepareAsync
Parameters ExecuteNonQuery ToString
Site ExecuteNonQueryAsync Unprepare
Statements ExecuteReader Disposed
Transaction ExecuteReaderAsyncYou can quickly see how PowerShell can easily expose available functions to user. This is why I've been using PowerShell back in my SQL Server DBA days extensively. I will leave it to you to explore this further. Let's see what else we can do.
$reader = $command.ExecuteReader();
We now have a reader, let's try the same $reader.
PS /home/jovyan> $reader.
Depth GetDateTime GetStreamAsync
FieldCount GetDecimal GetString
HasRows GetDouble GetTextReader
IsClosed GetEnumerator GetTextReaderAsync
IsOnRow GetFieldType GetTimeSpan
RecordsAffected GetFieldValue GetTimeStamp
Statements GetFieldValueAsync GetType
VisibleFieldCount GetFloat GetValue
Close GetGuid GetValues
CloseAsync GetHashCode InitializeLifetimeService
Dispose GetInt16 IsDBNull
DisposeAsync GetInt32 IsDBNullAsync
Equals GetInt64 NextResult
GetBoolean GetInterval NextResultAsync
GetByte GetLifetimeService Read
GetBytes GetName ReadAsync
GetChar GetOrdinal ToString
GetChars GetPostgresType Item
GetColumnSchema GetProviderSpecificFieldType ReaderClosed
GetData GetProviderSpecificValue Where
GetDataTypeName GetProviderSpecificValues ForEach
GetDataTypeOID GetSchemaTable
GetDate GetStreamLet's select a method for good measure:
PS /home/jovyan> $reader.HasRows
True
PS /home/jovyan> $reader.FieldCount
2
PS /home/jovyan> $reader.Statements
SQL : SHOW CREATE TABLE vehicles
StatementType : Select
Rows : 0
OID : 0
InputParameters : {}
PS /home/jovyan> $reader.GetColumnSchema()
AllowDBNull :
BaseCatalogName : movr
BaseColumnName : table_name
BaseSchemaName :
BaseServerName : crdb
BaseTableName :
ColumnName : table_name
ColumnOrdinal : 0
ColumnSize :
IsAutoIncrement :
IsKey :
IsLong : False
IsReadOnly : True
IsUnique :
NumericPrecision :
NumericScale :
UdtAssemblyQualifiedName :
DataType : System.String
DataTypeName : text
PostgresType : text
TypeOID : 25
TableOID : 0
ColumnAttributeNumber : 0
DefaultValue :
NpgsqlDbType : Text
IsAliased : False
IsExpression : False
IsHidden : False
IsIdentity : False
AllowDBNull :
BaseCatalogName : movr
BaseColumnName : create_statement
BaseSchemaName :
BaseServerName : crdb
BaseTableName :
ColumnName : create_statement
ColumnOrdinal : 1
ColumnSize :
IsAutoIncrement :
IsKey :
IsLong : False
IsReadOnly : True
IsUnique :
NumericPrecision :
NumericScale :
UdtAssemblyQualifiedName :
DataType : System.String
DataTypeName : text
PostgresType : text
TypeOID : 25
TableOID : 0
ColumnAttributeNumber : 0
DefaultValue :
NpgsqlDbType : Text
IsAliased : False
IsExpression : False
IsHidden : False
IsIdentity : False
PS /home/jovyan> $reader.GetSchemaTable()
ColumnName : table_name
ColumnOrdinal : 0
ColumnSize : -1
NumericPrecision : 0
NumericScale : 0
IsUnique : False
IsKey : False
BaseServerName :
BaseCatalogName : movr
BaseColumnName : table_name
BaseSchemaName :
BaseTableName :
DataType : System.String
AllowDBNull :
ProviderType : 19
IsAliased : False
IsExpression : False
IsIdentity : False
IsAutoIncrement : False
IsRowVersion : False
IsHidden : False
IsLong : False
IsReadOnly :
ProviderSpecificDataType :
DataTypeName : text
ColumnName : create_statement
ColumnOrdinal : 1
ColumnSize : -1
NumericPrecision : 0
NumericScale : 0
IsUnique : False
IsKey : False
BaseServerName :
BaseCatalogName : movr
BaseColumnName : create_statement
BaseSchemaName :
BaseTableName :
DataType : System.String
AllowDBNull :
ProviderType : 19
IsAliased : False
IsExpression : False
IsIdentity : False
IsAutoIncrement : False
IsRowVersion : False
IsHidden : False
IsLong : False
IsReadOnly :
ProviderSpecificDataType :
DataTypeName : textWe can quickly see how PowerShell allows us to quickly interact with our database. It would be the same experience had the Npgsql assembly would not fail to load in the Jupyter Notebook. Finally, let me show you the output of my code running in the Jupyter Notebook in case you're still interested. Below is the Notebook in MarkDown format for your interest.
# List available modules, specifically, we're looking for Npgsql driver loaded.
Get-Module -ListAvailable
Directory: /usr/src/jupyter-powershell/Modules
ModuleType Version Name ExportedCommands
---------- ------- ---- ----------------
Manifest 1.1.0.0 Microsoft.PowerShell.Archive {Compress-Archive, Expand-Archive}
Manifest 3.0.0.0 Microsoft.PowerShell.Host {Start-Transcript, Stop-Transcript}
Manifest 3.1.0.0 Microsoft.PowerShell.Management {Add-Content, Clear-Content, Clear-ItemProperty, Join-Path...}
Manifest 3.0.0.0 Microsoft.PowerShell.Security {Get-Credential, Get-ExecutionPolicy, Set-ExecutionPolicy, ...
Manifest 3.1.0.0 Microsoft.PowerShell.Utility {Format-List, Format-Custom, Format-Table, Format-Wide...}
Script 1.1.7.0 PackageManagement {Find-Package, Get-Package, Get-PackageProvider, Get-Packag...
Script 1.6.0 PowerShellGet {Install-Module, Find-Module, Save-Module, Update-Module...}
Script 0.0 PSDesiredStateConfiguration {Generate-VersionInfo, Set-PSMetaConfigDocInsProcessedBefor...
Script 1.2 PSReadLine {Get-PSReadlineKeyHandler, Set-PSReadlineKeyHandler, Remove...
# List available repos, specifically, we're looking for Nuget where Npgsql is available
Get-PackageSourceName ProviderName IsTrusted Location
---- ------------ --------- --------
MyNuGet NuGet False https://www.nuget.org/api/v2
PSGallery PowerShellGet False https://www.powershellgallery.com/api/v2# If unavailable, register Nuget repo
Register-PackageSource -Name MyNuGet -Location https://www.nuget.org/api/v2 -ProviderName NuGet# Search for the package after repo is added.
Find-Package NpgsqlName Version Source Summary
---- ------- ------ -------
Npgsql 4.1.3.1 MyNuGet Npgsql is the open source .NET data provider for Pos...
# Install Npgsql from the newly added repo, -Scope controls the location of the installed package
# -Scope CurrentUser means the package will be installed into the .local directory of the user's home.
# leaving scope out will place the package into /usr/local/share/PackageManagement/NuGet/Packages/Npgsql.4.1.3.1
Install-Package Npgsql -Force -SkipDependencies -Scope CurrentUser
# Verify Npgsql is installed
Get-Package NpgsqlName Version Source ProviderName
---- ------- ------ ------------
Npgsql 4.1.3.1 /home/jovyan/.local/share/Pac... NuGet# (Optional) Get the Strong Name of the assembly for reference, path of the assembly depends on the installation method
[System.Reflection.AssemblyName]::GetAssemblyName('/home/jovyan/.local/share/PackageManagement/NuGet/Packages/Npgsql.4.1.3.1/lib/netstandard2.1/Npgsql.dll').FullNameNpgsql, Version=4.1.3.1, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7
# (Optional) Get the name of the assembly and version
[System.Reflection.AssemblyName]::GetAssemblyName('/home/jovyan/.local/share/PackageManagement/NuGet/Packages/Npgsql.4.1.3.1/lib/netstandard2.1/Npgsql.dll')Version Name
------- ----
4.1.3.1 Npgsql# Import the Npgsql library for use ((Currently doesn't work for Jupyter, works in powershell console)
Import-Module '/home/jovyan/.local/share/PackageManagement/NuGet/Packages/Npgsql.4.1.3.1/lib/netstandard2.1/Npgsql.dll'Import-Module : Could not load file or assembly 'netstandard, Version=2.1.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51'. The system cannot find the file specified.
^M
At line:2 char:1
+ Import-Module '/home/jovyan/.local/share/PackageManagement/NuGet/Pack ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CategoryInfo : NotSpecified: (:) [Import-Module], FileNotFoundException
FullyQualifiedErrorId : System.IO.FileNotFoundException,Microsoft.PowerShell.Commands.ImportModuleCommandAdd-Type -Path /home/jovyan/.local/share/PackageManagement/NuGet/Packages/Npgsql.4.1.3.1/lib/netstandard2.1/Npgsql.dll
# List current assemblies, notice the assembly appears in the list
[System.AppDomain]::CurrentDomain.GetAssemblies() | Select-String "Npgsql"Npgsql, Version=4.1.3.1, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7# doesn't work yet, this is helpful https://stackoverflow.com/questions/34674761/how-to-import-the-npgsql-module
$connection = [Npgsql.NpgsqlConnection]::new('Host=crdb;Port=26257;Username=maxroach;Password="";Database=movr')
$connection.Open()
$command = [Npgsql.NpgsqlCommand]::new('SELECT * from rides limit 10', $connection) : Unable to find type [Npgsql.NpgsqlConnection].^M
At line:2 char:15
+ $connection = [Npgsql.NpgsqlConnection]::new('Host=crdb;Port=26257;Us ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~
CategoryInfo : InvalidOperation: (Npgsql.NpgsqlConnection:TypeName) [], ParentContainsErrorRecordException
FullyQualifiedErrorId : TypeNotFoundFinally, here's a slightly abbreviated Jupyter notebook. I also want to make a commitment that once I hear back about the issue I was having, I will update this post with a workaround. Hope you enjoyed this post, please comment below.
That's all!
Published at DZone with permission of Artem Ervits. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments