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
619
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
