docker搭建pg并通过xorm操作

发布时间:2024年01月14日

docker搭建pg并通过xorm操作

我这里以mac为例

  • docker环境准备大家可以去下载docker-desktop
  • docker-desktop官网地址:https://docs.docker.com/desktop/install/mac-install/

1 docker安装pg

docker run -d \
-p 5432:5432 \
-e POSTGRES_USER=postgres \
-e POSTGRES_PASSWORD=postgres \
-v /Users/xsky/docker-home/pg:/var/lib/postgresql/data \
--name pg \
--restart always \
docker.io/postgres:9.6-alpine

# -p port 映射端口,可以通过宿主机的端口访问到容器内的服务
# -d 是detach 保持程序后台运行的意思
# -e environment 设置环境变量
# -v volume 文件或者文件夹的挂载

2 pg常用命令

2.1 创建表、索引等

在这里插入图片描述

实战:创建一个角色,新建数据库并分配给新创建的角色。同时在新库下创建新表,增删改查该表的数据。

# 过滤pg容器名并进入容器内部
docker ps | grep pg
docker exec -it container_name /bin/bash
# 以postgres账户(默认账户)进入pg内部,以postgres用户角色登录postgresql数据库
psql -U postgres;
# 修改密码
ALTER USER postgres WITH PASSWORD 'postgres';
- 密码postgres要用引号引起来
- 命令最后有分号


# 创建用户
CREATE USER ziyi WITH PASSWORD '*****';

①postgres-#:短横杠的表明语句未结束,用分号标识结束
②postgres=#:postgres后面是等号的表明语句已经结束,可以开始一个新的语句

# 创建testdb数据库,并授权给ziyi
CREATE DATABASE testdb OWNER ziyi;

#切换数据库[以ziyi的用户角色切换到testdb数据库]
\c testdb ziyi

# 展示当前数据库下所有的表及索引
\d

# 展示当前数据库下的所有表
\dt

# 创建表
CREATE TABLE userdetail
(
    uid integer,
    name character varying(100),
    intro character varying(100) -- 类似于mysql的varchar(100)
)
WITH(OIDS=FALSE);

# 查看表
\dt


-- 添加记录
insert into userdetail (uid, name, intro) values (1, 'jack', '这是我的介绍哦');

-- 查询表中记录记录[起始记录从0开始,每页5条数据]
select uid, name, intro from userdetail offset 0 limit 5;

-- 新增生日时间字段
alter table userdetail add column birthday date ;
# 删除alter table userdetail drop column birthday  ;

-- 新增两条记录并根据创建时间降序排列
insert into userdetail (uid, name, intro,birthday) values (2, 'tom', 'this is tom','2024-01-10'),(3, 'alias', 'this is alias','2024-01-02');

-- 查询并根据birthday降序排列
select uid,name,birthday from userdetail  order by birthday desc offset 0 limit 3;

-- 查看表结构
\d userdetail 

-- 更新表中记录
update userdetail set name = 'jack2' where uid = 1;

-- 重新查询表中数据
select * from userdetail;

-- 给uid添加主键
alter table userdetail drop constraint if exists userdetail_pkey, add primary key(uid);

-- 给name添加唯一索引
create unique index if not exists idx_userdetail_name on userdetail (name);

-- 重新查看表结构,可以看到表结构多了唯一索引和序列
\d userdetail

-- 删除birthday列
alter table userdetail drop column birthday;

-- 添加时间字段,并插入一条记录 timestamp:2023-06-30 16:44:41.660549
alter table userdetail add column created_time timestamp;

-- 新增数据
insert into userdetail(uid, intro, name, created_time) values(7, 'intro4', 'tom4', ' 2022-06-30 16:44:41.660549');
insert into userdetail(uid, intro, name, created_time) values(9, 'intro2', 'tom2', now());


-- 创建序列sequence
CREATE SEQUENCE seq_user_id
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
# increment 表示每次增加多少,
# minvalue表示最小值,
# maxvalue表示序列的最大值

-- 修改表结构,将 uid 设置为序列的默认值(将序列应用到表)
ALTER TABLE userdetail 
    ALTER COLUMN uid SET DEFAULT nextval('seq_user_id'::regclass);   

-- 查看表结构
\d 

-- 查看表中所有数据
select * from userdetail;

-- 清空表中数据
truncate userdetail;

-- 删除表
drop table userdetail;

 -- 删除序列
 DROP SEQUENCE IF EXISTS seq_user_id;

2.2 查看数据库、表等命令

# \l 列出所有数据库
\l

# \c 切换数据库
\c testbase

# \c database_name role_name,以test角色进入testbase
\c testbase test

# \d 查询当前数据库下的所有表及sequence
\d 

# \d table_name 查看表的信息(如果有索引也会展示出来)
\d mytable

# \di 查看数据库的所有索引,在postgres=#模式下执行
\di

# \q 退出控制台
\q 

3 xorm操作pg

xorm是常用的用于操作数据库的框架。

初始化engine

package pg

import (
	"fmt"
	"github.com/aobco/log"
	"time"
	"xorm.io/xorm"
)

const (
	host     = "localhost"
	port     = 5432
	user     = "postgres"
	password = "postgres"
	dbName   = "postgres"
)

//go get "xorm.io/xorm"
var Engine *xorm.Engine

func init() {
	psqlInfo := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable", host, port, user, password, dbName)
	engine, err := xorm.NewEngine("postgres", psqlInfo)
	if err != nil {
		log.Fatal(err)
	}
	engine.ShowSQL(true) //菜鸟必备
	engine.SetMaxIdleConns(10)
	engine.SetMaxOpenConns(20)
	engine.SetConnMaxLifetime(time.Minute * 10)
	engine.Cascade(true)
	if err = engine.Ping(); err != nil {
		log.Fatalf("%v", err)
	}
	Engine = engine
	fmt.Println("connect postgresql success")
}

3.1 sync创建表结构

package main

import (
	"github.com/aobco/log"
	_ "github.com/lib/pq"
	"myTest/demo_home/xorm_demo/pg"
	"time"
)

/*
	通过xorm自动创建表结构
*/

type User struct {
	Id          int64     `xorm:"bigint pk autoincr"`
	Name        string    `xorm:"varchar(25) notnull unique comment('姓名')"`
	Age         int64     `xorm:"bigint"`
	UserInfo    Info      `xorm:"JSON"`
	CreatedTime time.Time `xorm:"created_time timestampz created"`
	ModifyTime  time.Time `xorm:"modify_time timestampz updated"`
}

type Info struct {
	Address string   `json:"address"`
	Hobbies []string `json:"hobbies"`
}

/*
 xorm官网文档:https://xorm.io/zh/docs
*/

func main() {
	err := pg.Engine.Sync(new(User))
	if err != nil {
		log.Errorf("同步表结构失败")
		return
	}
	log.Infof("同步表结构成功...")
}

3.2 insert操作

package main

import (
	"github.com/aobco/log"
	_ "github.com/lib/pq"
	"myTest/demo_home/xorm_demo/pg"
	"time"
)

/*
	演示insert操作
*/

type User struct {
	Id          int64     `xorm:"bigint pk autoincr"`
	Name        string    `xorm:"varchar(25) notnull unique comment('姓名')"`
	Age         int64     `xorm:"bigint"`
	UserInfo    Info      `xorm:"user_info JSON"`
	CreatedTime time.Time `xorm:"created_time timestampz created"`
	ModifyTime  time.Time `xorm:"modify_time timestampz updated"`
}

type Info struct {
	Address string   `json:"address"`
	Hobbies []string `json:"hobbies"`
}

func main() {
	//1. 插入一条数据 insertOne
	//normalInsert()

	//2. 忽略字段插入 pg.Engine.Omit("user_info").Insert(u)
	//insertWithIgnoreCol()

	//3. 批量插入 pg.Engine.Omit("user_info").Insert(&users)
	insertWithBatch()
}

func normalInsert() {
	u := &User{
		Name: "jack",
		Age:  17,
		UserInfo: Info{
			Address: "beijing",
			Hobbies: []string{
				"baseball",
				"soccer-ball",
			},
		},
	}
	_, err := pg.Engine.InsertOne(u)
	if err != nil {
		log.Errorf("%v", err)
		return
	}
	log.Infof("insert succ..")
}

//2. 忽略字段插入
func insertWithIgnoreCol() {
	u := &User{
		Name: "tom",
		Age:  14,
		UserInfo: Info{
			Address: "sichuan",
			Hobbies: []string{
				"baseball",
				"soccer-ball",
			},
		},
	}
	_, err := pg.Engine.Omit("user_info").Insert(u)
	if err != nil {
		log.Errorf("%v", err)
		return
	}
	log.Infof("insert succ..")
}

//3. 批量插入
func insertWithBatch() {
	u1 := &User{
		Name: "tom1",
		Age:  15,
	}
	u2 := &User{
		Name: "tom2",
		Age:  16,
	}
	u3 := &User{
		Name: "tom3",
		Age:  17,
	}
	users := []*User{u1, u2, u3}
	_, err := pg.Engine.Omit("user_info").Insert(&users)
	if err != nil {
		log.Errorf("%v", err)
		return
	}
	log.Infof("batch insert succ..")
}

3.3 delete操作

package main

import (
	"github.com/aobco/log"
	_ "github.com/lib/pq"
	"myTest/demo_home/xorm_demo/pg"
	"time"
)

type User struct {
	Id          int64     `xorm:"bigint pk autoincr"`
	Name        string    `xorm:"varchar(25) notnull unique comment('姓名')"`
	Age         int64     `xorm:"bigint"`
	UserInfo    Info      `xorm:"user_info JSON"`
	CreatedTime time.Time `xorm:"created_time timestampz created"`
	ModifyTime  time.Time `xorm:"modify_time timestampz updated"`
}

type Info struct {
	Address string   `json:"address"`
	Hobbies []string `json:"hobbies"`
}

func main() {
	//1. 根据id删除 pg.Engine.Delete(&User{Id: id})
	//deleteById(3)

	//2. 批量删除 pg.Engine.In("id", ids).Delete(new(User))
	deleteByIds([]int64{4, 5})
}

func deleteById(id int64) {
	_, err := pg.Engine.Delete(&User{Id: id})
	if err != nil {
		log.Errorf("%v", err)
		return
	}
	log.Infof("del succ...")
}

func deleteByIds(ids []int64) {
	_, err := pg.Engine.In("id", ids).Delete(new(User))
	if err != nil {
		log.Errorf("%v", err)
		return
	}
	log.Infof("batch del succ...")
}

3.4 update操作

package main

import (
	"fmt"
	_ "github.com/lib/pq"
	"myTest/demo_home/xorm_demo/pg"
	"time"
	"xorm.io/xorm"
)

/*
	演示update操作
*/

type User struct {
	Id          int64     `xorm:"bigint pk autoincr"`
	Name        string    `xorm:"varchar(25) notnull unique comment('姓名')"`
	Age         int64     `xorm:"bigint"`
	UserInfo    Info      `xorm:"user_info JSON"`
	CreatedTime time.Time `xorm:"created_time timestampz created"`
	ModifyTime  time.Time `xorm:"modify_time timestampz updated"`
}

type Info struct {
	Address string   `json:"address"`
	Hobbies []string `json:"hobbies"`
}

func main() {
	//1. 根据id更新 engine.ID(user.Id).Update(user)
	//updateById(pg.Engine, &User{Id: 1, Name: "heihei", Age: 53})

	//2. 更新指定字段 engine.ID(user.Id).Cols("age").Update(user)
	updateUserAge(pg.Engine, &User{Id: 1, Age: 23})
}

func updateById(engine *xorm.Engine, user *User) (int64, error) {
	return engine.ID(user.Id).Update(user)
}

func updateUserAge(engine *xorm.Engine, user *User) (int64, error) {
	if user == nil {
		return 0, fmt.Errorf("user cannot be nil")
	}
	return engine.ID(user.Id).Cols("age").Update(user)
}

3.5 select操作

package main

import (
	"errors"
	"github.com/aobco/log"
	_ "github.com/lib/pq"
	"myTest/demo_home/xorm_demo/pg"
	"time"
	"xorm.io/xorm"
)

/*
	演示查询
*/
type User struct {
	Id          int64     `xorm:"bigint pk autoincr"`
	Name        string    `xorm:"varchar(25) notnull unique comment('姓名')"`
	Age         int64     `xorm:"bigint"`
	UserInfo    Info      `xorm:"user_info JSON"`
	CreatedTime time.Time `xorm:"created_time timestampz created"`
	ModifyTime  time.Time `xorm:"modify_time timestampz updated"`
}

type Info struct {
	Address string   `json:"address"`
	Hobbies []string `json:"hobbies"`
}

func main() {
	//user, _ := getById(pg.Engine, 1)
	//log.Infof("%v", user)

	//users, _ := getByIds(pg.Engine, []int64{1, 2})
	//for _, user := range users {
	//	log.Infof("%+v", user)
	//}
	//
	//users, _ := getByName(pg.Engine, "tom")
	//for _, user := range users {
	//	log.Infof("%+v", user)
	//}

	users, _ := listByAge(pg.Engine, 10)
	for _, user := range users {
		log.Infof("%+v", user)
	}
}

//1. 根据id查询
func getById(engine xorm.Interface, id int64) (*User, error) {
	u := new(User)
	flag, err := engine.ID(id).Get(u)
	if err != nil {
		return nil, err
	}
	if !flag {
		return nil, nil
	}
	return u, nil
}

//2. 范围查询
func getByIds(engine *xorm.Engine, ids []int64) ([]*User, error) {
	users := make([]*User, 0)
	if len(ids) == 0 {
		return nil, errors.New("ids is required")
	}
	err := engine.In("id", ids).Find(&users)
	return users, err
}

//3. 模糊查询[单条记录用Get、多条记录用Find]
func getByName(engine *xorm.Engine, name string) ([]*User, error) {
	users := make([]*User, 0)
	err := engine.Where("name like ? ", "%"+name+"%").Find(&users)
	return users, err
}

func listByAge(engine *xorm.Engine, age int64) ([]*User, error) {
	users := make([]*User, 0)
	//err := engine.Where("age > ?", age).Decr("created_time").Find(&users)
	//err := engine.Where("age > ?", age).OrderBy("created_time").Find(&users)
	err := engine.Where("age > ?", age).OrderBy("created_time").Limit(2, 0).Find(&users)
	if err != nil {
		return nil, err
	}
	return users, nil
}

3.6 tx事务操作

package main

import (
	"github.com/aobco/log"
	_ "github.com/lib/pq"
	"myTest/demo_home/xorm_demo/pg"
	"time"
	"xorm.io/xorm"
)

type User struct {
	Id          int64     `xorm:"bigint pk autoincr"`
	Name        string    `xorm:"varchar(25) notnull unique comment('姓名')"`
	Age         int64     `xorm:"bigint"`
	UserInfo    Info      `xorm:"user_info JSON"`
	CreatedTime time.Time `xorm:"created_time timestampz created"`
	ModifyTime  time.Time `xorm:"modify_time timestampz updated"`
}

type Info struct {
	Address string   `json:"address"`
	Hobbies []string `json:"hobbies"`
}

/*
	xorm操作事物
*/
func main() {
	Tx(updateUserInfo)
}

func updateUserInfo(session *xorm.Session) error {
	u := new(User)
	u.Id = 1
	u.Age = 1
	_, err := session.ID(u.Id).Cols("age").Update(u)
	//err = errors.New("build an error")
	if err != nil {
		log.Errorf("%v", err)
		return err
	}
	u2 := new(User)
	u2.Id = 6
	u2.Age = 200
	_, err = session.ID(u2.Id).Cols("age").Update(u2)
	if err != nil {
		log.Errorf("%v", err)
		return err
	}
	return err
}

type SessionHandleFunc func(session *xorm.Session) error

func Tx(f SessionHandleFunc) error {
	session := pg.Engine.NewSession()
	session.Begin()
	defer func() {
		if err := recover(); err != nil {
			log.Errorf("%+v", err)
			session.Rollback()
		}
	}()
	err := f(session)
	if err != nil {
		log.Errorf("[DB_TX] error %+v", err)
		session.Rollback()
		return err
	}
	return session.Commit()
}

4 全部代码

Github地址:https://github.com/ziyifast/ziyifast-code_instruction

文章来源:https://blog.csdn.net/weixin_45565886/article/details/135588665
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。