Best PostgreSql Guideline (edit)
manhng83/postgresql-net-core: PostgreSQL + NET 5 + EF Core + Code First (github.com)
- PostgreSQL - Database
- TimescaleDB - Database
- DBeaver - DB GUI Tool
- Navicat - DB GUI Tool
- PoWA (PostgreSQL Workload Analyzer) - Performance Monitoring
- Code-First
- Automatically create database first time run
.NET Framework + EF 6x
Code-First Database Design with Entity Framework and PostgreSQL - Compose Articles
Show me the code:
Samples/Program.cs at master · csharp-today/Samples (github.com)
Basic:
PostgreSQL/Postgres Create Database: How to Create Example (guru99.com)
Using PostgreSQL in your C# (.NET) application (An introduction) - CodeProject
.NET Core + EF Core
EF Core PostgreSQL Generate Script Update Database - @manhng (HAY HAY HAY)
Adding EF Core and PostgreSQL to an ASP.NET Core project on OS X (andrewlock.net) (HAY HAY HAY)
Entity Framework Core 3 code first with PostgreSQL provider - exentials.net (HAY HAY HAY)
exentials/DemoEFCore: C# Demo project to demonstrate Entity Framework Core 3 with PostgreSQL provider (github.com) (HAY HAY HAY)
+ Install-Package Npgsql.EntityFrameworkCore.PostgreSQL
+ Install-Package Microsoft.EntityFrameworkCore.Tools
+ Add-Migration InitialEntities
+ Update-Database
DbContext Scaffold: Genereate Entities
dotnet ef dbcontext scaffold "Host=localhost;Database=mydatabase;Username=myuser;Password=mypassword" Npgsql.EntityFrameworkCore.PostgreSQL -o Models
Show me the code:
Articles:
ASP.NET Core Web API with EF Core Code-First Approach - Code Maze (code-maze.com) (HAY HAY HAY)
How to Configure PostgreSQL in Entity Framework Core - Code Maze (code-maze.com) (HAY HAY HAY)
Others:
ABP Framework - Open Source Web Application Framework
CRUD Operations In PostgreSQL With EF Core And ASP.NET Core Web API (c-sharpcorner.com)
c# - auto create database in Entity Framework Core - Stack Overflow
Tools:
Npgsql PostgreSQL Integration - Visual Studio Marketplace
appSettings.json
{ "DbContextSettings" :{ "ConnectionString" : "User ID=Sock;Password=password;Host=localhost;Port=5432;Database=DemoArticlesApp;Pooling=true;" } }
public void ConfigureServices(IServiceCollection services) { // Add framework services. services.AddMvc(); var connectionString = Configuration["DbContextSettings:ConnectionString"]; services.AddDbContext<ArticleContext>( opts => opts.UseNpgsql(connectionString) ); }
"PostgreSQL": {
"ConnectionString": "server=localhost;port=5432;userid=postgres;database=postgres;",
"DbPassword": "YourPasswordHere"
}
public void ConfigureServices(IServiceCollection services)
{
services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_2);
var connectionString = Configuration["PostgreSql:ConnectionString"];
var dbPassword = Configuration["PostgreSql:DbPassword"];
var builder = new NpgsqlConnectionStringBuilder(connectionString)
{
Password = dbPassword
};
services.AddDbContext<ApplicationContext>(options => options.UseNpgsql(builder.ConnectionString));
}
A curated list of awesome PostgreSQL software, libraries, tools and resources, inspired by awesome-mysql
PostgreSQL Workload Analyzer
PostgreSQL Workload Analyzer — PoWA 4.0.0 documentation
PoWA (PostgreSQL Workload Analyzer) is a performance tool for PostgreSQL 9.4 and newer allowing to collect, aggregate and purge statistics on multiple PostgreSQL instances from various Stats Extensions.
Depending on your needs, you can either use the provided background worker (requires a PostgreSQL restart, and more suited for single-instance setups), or the provided PoWA-collector daemon (does not require a PostgreSQL restart, can gather performance metrics from multiple instances, including standby).
Access PostgreSQL databases using the Azure Data Studio extension (sqlshack.com)
Debugging PostgreSQL performance, the hard way · JustWatch Blog
Which Is the Best PostgreSQL GUI? 2021 Comparison (scalegrid.io)
DBeaver
DBeaver is a major cross-platform GUI tool for PostgreSQL that both developers and database administrators love. DBeaver is not a native GUI tool for PostgreSQL, as it supports all the popular databases like MySQL, MariaDB, Sybase, SQLite, Oracle, SQL Server, DB2, MS Access, Firebird, Teradata, Apache Hive, Phoenix, Presto, and Derby – any database which has a JDBC driver (over 80 databases!).
Here are some of the top DBeaver GUI features for PostgreSQL:
- Visual Query builder helps you to construct complex SQL queries without actual knowledge of SQL.
- It has one of the best editors – multiple data views are available to support a variety of user needs.
- Convenient navigation among data.
- In DBeaver, you can generate fake data that looks like real data allowing you to test your systems.
- Full-text data search against all chosen tables/views with search results shown as filtered tables/views.
- Metadata search among rows in database system tables.
- Import and export data with many file formats such as CSV, HTML, XML, JSON, XLS, XLSX.
- Provides advanced security for your databases by storing passwords in secured storage protected by a master password.
- Automatically generated ER diagrams for a database/schema.
- Enterprise Edition provides a special online support system.
One of the cons of DBeaver is it may be slow when dealing with large data sets compared to some expensive GUI tools like Navicat and DataGrip.
You can run DBeaver on Windows, Linux, and macOS, and easily connect DBeaver PostgreSQL with or without SSL. It has a free open-source edition as well an enterprise edition. You can buy the standard license for enterprise edition at $199, or by subscription at $19/month. The free version is good enough for most companies, as many of the DBeaver users will tell you the free edition is better than pgAdmin.
DBeaver Pricing: Free community, $199 standard license
Navicat
Navicat is an easy-to-use graphical tool that targets both beginner and experienced developers. It supports several database systems such as MySQL, PostgreSQL, and MongoDB. One of the special features of Navicat is its collaboration with cloud databases like Amazon Redshift, Amazon RDS, Amazon Aurora, Microsoft Azure, Google Cloud, Tencent Cloud, Alibaba Cloud, and Huawei Cloud.
Important features of Navicat for Postgres include:
- It has a very intuitive and fast UI. You can easily create and edit SQL statements with its visual SQL builder, and the powerful code auto-completion saves you a lot of time and helps you avoid mistakes.
- Navicat has a powerful data modeling tool for visualizing database structures, making changes, and designing entire schemas from scratch. You can manipulate almost any database object visually through diagrams.
- Navicat can run scheduled jobs and notify you via email when the job is done running.
- Navicat is capable of synchronizing different data sources and schemas.
- Navicat has an add-on feature (Navicat Cloud) that offers project-based team collaboration.
- It establishes secure connections through SSH tunneling and SSL ensuring every connection is secure, stable, and reliable.
- You can import and export data to diverse formats like Excel, Access, CSV, and more.
Despite all the good features, there are a few cons that you need to consider before buying Navicat:
- The license is locked to a single platform. You need to buy different licenses for PostgreSQL and MySQL. Considering its heavy price, this is a bit difficult for a small company or a freelancer.
- It has many features that will take some time for a newbie to get going.
You can use Navicat in Windows, Linux, Mac OS, and iOS environments. The quality of Navicat is endorsed by its world-popular clients, including Apple, Oracle, Google, Microsoft, Facebook, Disney, and Adobe. Navicat comes in three editions called enterprise edition, standard edition, and non-commercial edition. Enterprise edition costs you $14.99/month up to $299 for a perpetual license, the standard edition is $9.99/month up to $199 for a perpetual license, and then the non-commercial edition costs $5.99/month up to $119 for its perpetual license. You can get full price details here, and download the Navicat trial version for 14 days from here.
