Tag: postgresql

PostgreSQL TRUNCATE TABLE

TRUNCATE [ TABLE ] [ ONLY ] name [ * ] [, ... ]
    [ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]

Remove all data from one table

TRUNCATE TABLE table_name;

Besides removing data, you may want to reset the values in the identity column by using the RESTART IDENTITY option like this:

TRUNCATE TABLE table_name 
RESTART IDENTITY;

Remove all data from a table that has foreign key references

TRUNCATE TABLE table_name 
CASCADE;

References
https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-truncate-table/
https://www.postgresql.org/docs/current/sql-truncate.html

Entity Framework Core Database-First using Npgsql

dotnet ef dbcontext scaffold "Host=my_host;Database=my_db;Username=my_user;Password=my_pw" Npgsql.EntityFrameworkCore.PostgreSQL

If you have other tables in your database, you may use additional parameters – -Schemas and -Tables – to filter the list of schemas and/or tables that are added to the model. For example, you can use the following command:

dotnet ef dbcontext scaffold "host=server;database=test;user id=postgres;" Devart.Data.PostgreSql.Entity.EFCore -Tables dept,emp

References
https://www.npgsql.org/efcore/index.html
https://www.devart.com/dotconnect/postgresql/docs/EFCore-Database-First-NET-Core.html

Configure One-to-One Relationships using Fluent API in Entity Framework Core

using Microsoft.EntityFrameworkCore;

namespace BlazorApp1;

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options)
        : base(options)
    {
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseNpgsql("Host=localhost;Database=testdb;Username=postgres;Password=12345");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Student>()
            .HasOne<StudentAddress>(s => s.Address)
            .WithOne(ad => ad.Student)
            .HasForeignKey<StudentAddress>(ad => ad.AddressOfStudentId);
    }

    public DbSet<Student> Students { get; set; }
    public DbSet<StudentAddress> StudentAddresses { get; set; }
}

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }

    public StudentAddress Address { get; set; }
}

public class StudentAddress
{
    public int StudentAddressId { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public string Country { get; set; }

    public int AddressOfStudentId { get; set; }
    public Student Student { get; set; }
}

References
https://www.entityframeworktutorial.net/efcore/configure-one-to-one-relationship-using-fluent-api-in-ef-core.aspx
https://docs.microsoft.com/en-us/ef/core/modeling/relationships?tabs=fluent-api%2Cfluent-api-simple-key%2Csimple-key

Configure One-to-Many Relationships using Fluent API in Entity Framework Core

 

using Microsoft.EntityFrameworkCore;

namespace BlazorApp1;

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options)
        : base(options)
    {
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseNpgsql("Host=localhost;Database=testdb;Username=postgres;Password=12345");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Student>()
            .HasOne<Grade>(s => s.Grade)
            .WithMany(g => g.Students)
            .HasForeignKey(s => s.CurrentGradeId);
    }

    public DbSet<Student> Students { get; set; }
    public DbSet<Grade> Grades { get; set; }
}

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }

    public int CurrentGradeId { get; set; }
    public Grade Grade { get; set; }
}

public class Grade
{
    public int GradeId { get; set; }
    public string GradeName { get; set; }
    public string Section { get; set; }

    public ICollection<Student> Students { get; set; }
}

References
https://www.entityframeworktutorial.net/efcore/configure-one-to-many-relationship-using-fluent-api-in-ef-core.aspx
https://docs.microsoft.com/en-us/ef/core/modeling/relationships?tabs=fluent-api%2Cfluent-api-simple-key%2Csimple-key

Configure PostgreSQL Entity Framework for ASP.NET using Npgsql

Install Required Packages

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore

Install dotnet-ef

Install it locally or globally.

Local

dotnet new tool-manifest # if you are setting up this repo
dotnet tool install --local dotnet-ef

Global

dotnet tool install --global dotnet-ef
dotnet add package Microsoft.EntityFrameworkCore.Design

Defining a DbContext

using Microsoft.EntityFrameworkCore;

namespace BlazorApp1;

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options)
        : base(options)
    {
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseNpgsql("Host=localhost;Database=testdb;Username=postgres;Password=12345");
    }

    public DbSet<Person> People { get; set; }
}

public class Person
{
    public int ID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int Age { get; set; }
}

Create Migration

dotnet ef migrations add InitialCreate

Apply to Database

dotnet ef database update

or

dotnet ef database update InitialCreate
dotnet ef database update 20180904195021_InitialCreate --connection your_connection_string

Program.cs

builder.Services.AddDbContext<AppDbContext>(options => options.UseNpgsql(builder.Configuration.GetConnectionString("AppDbContext")));
builder.Services.AddDatabaseDeveloperPageExceptionFilter();

References
https://www.npgsql.org/efcore/index.html
https://docs.microsoft.com/en-us/aspnet/core/data/ef-rp/intro?view=aspnetcore-6.0&tabs=visual-studio
https://docs.microsoft.com/en-us/aspnet/core/data/ef-mvc/migrations?view=aspnetcore-6.0
https://medium.com/executeautomation/asp-net-core-6-0-minimal-api-with-entity-framework-core-69d0c13ba9ab
https://docs.microsoft.com/en-us/ef/core/cli/dotnet

Install PostgreSQL on Ubuntu 22.04

# Create the file repository configuration:
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Import the repository signing key:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Update the package lists:
sudo apt-get update

# Install the latest version of PostgreSQL.
# If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql':
sudo apt-get -y install postgresql postgresql-contrib
sudo systemctl start postgresql.service

Update PostgreSQL admin user’s password

The postgresql database admin user is created with the installation of PostgreSQL database server. We need to set a secure password for this user.

sudo su - postgres
psql -c "alter user postgres with password 'MySt0ngDBP@ss'"

Switching Over to the postgres Account

Switch over to the postgres account on your server by typing:

sudo -i -u postgres

You can now access the PostgreSQL prompt immediately by typing:

psql

From there you are free to interact with the database management system as necessary.
Exit out of the PostgreSQL prompt by typing:

\q

This will bring you back to the postgres Linux user’s command prompt.

Accessing a Postgres Prompt Without Switching Accounts

sudo -u postgres psql
\q

References
https://www.digitalocean.com/community/tutorials/how-to-install-postgresql-on-ubuntu-22-04-quickstart
https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-22-04
https://computingforgeeks.com/installing-postgresql-database-server-on-ubuntu/