prestd 试用笔记(三)——查询篇

为了在体验上连贯有序,在阅读本篇之前,建议先阅读下列两篇笔记,

在前面的文章中我们已经成功安装了 prestd 并根据具体使用需求调整了配置,是时候来探索一下 prestd 的数据查询功能了,简单起见,我在配置文件中关掉了 auth 功能(将 jwt.defaultauth.enabled 置为 false )和缓存功能(将 cache.enabled 置为 false )。

简单查询

在这一部分中将说明如何通过 prestd 进行简单的增删查改功能。由于是通过 http 请求进行操作,所以数据库的增删查改也恰好分别对应了 http 请求的动作语义POSTDELETEGETPUT )。

文档链接: https://docs.prestd.com/prestd/docs/api-reference/endpoints/#post

查询之前,我们需要插入一些数据,现在数据库中已经有了数据表 prestd.prest.persons ,并且有了一条名为 Bob 的记录,接下来增加新的几条记录,

~ » curl -X POST http://localhost:3000/prestd/prest/persons -d '
{
    "id":2, 
    "name":"Carol",
    "age":21,
    "gender":"female"
}'

prestd 也支持批量插入,按照文档中所说,发送如下请求可以一次性插入多条数据,注意路径中多了一个 batch

~ » curl -X POST http://localhost:3000/batch/prestd/prest/persons -d '
[
    {"id":3,"name":"Charlie","age":22,"gender":"male"},
    {"id":4,"name":"Chuck","age":23,"gender":"male"},
    {"id":5,"name":"Eve","age":24,"gender":"female"}
]'

文档链接: https://docs.prestd.com/prestd/docs/api-reference/endpoints/#get

此时如果进行整表查询的话,可以看到表中有如下五条记录,

~ » curl http://localhost:3000/prestd/prest/persons
[{"id":1,"name":"Bob","age":20,"gender":"male"},
 {"id":2,"name":"Carol","age":21,"gender":"female"},
 {"id":3,"name":"Charlie","age":22,"gender":"male"},
 {"id":4,"name":"Chuck","age":23,"gender":"male"},
 {"id":5,"name":"Eve","age":24,"gender":"female"}]

不同类型的查询通过提供 URL 中的查询参数来完成,完整的参数可以参考官方文档,下面将简单介绍几种比较常用的查询操作。

指定列查询

prestd 还支持仅仅返回其中的几列数据而不是整个记录,例如我们只想知道名字年龄,其他的一概不关心, SQL 的写法为

SELECT name, age
FROM prestd.prest.persons

对应的 prestd 请求则为

~ » curl http://localhost:3000/prestd/prest/persons\?_select\=name,age
[{"name":"Bob","age":20},
 {"name":"Carol","age":21},
 {"name":"Charlie","age":22},
 {"name":"Chuck","age":23},
 {"name":"Eve","age":24}]

条件查询

很多时候用不到这么多数据,这时往往更希望得到符合某个条件的结果,prestd 也提供了条件查询功能,将条件通过 URL 的查询参数发过去即可。比如说我们只想知道性别为男的有哪些记录,

用 SQL 查询很容易,

SELECT *
FROM prestd.prest.persons
WHERE gender='male'

对应的 prestd 请求为

~ » curl http://localhost:3000/prestd/prest/persons\?gender\=male
[{"id":1,"name":"Bob","age":20,"gender":"male"},
 {"id":3,"name":"Charlie","age":22,"gender":"male"},
 {"id":4,"name":"Chuck","age":23,"gender":"male"}]

或者只需要年龄范围在 23 岁(包含)到 25 岁(包含)之间的结果,

如下的 SQL

SELECT *
FROM prestd.prest.persons
WHERE age>=23 AND age<=25

可以转换为

~ » curl http://localhost:3000/prestd/prest/persons\?age\='$gte.23'\&age\='$lte.25'
[{"id":4,"name":"Chuck","age":23,"gender":"male"},
 {"id":5,"name":"Eve","age":24,"gender":"female"}]

👉 关于 prestd 范围查询说明,可以参考这篇文档。prestd 还支持其他比较操作符,可以参考操作符列表

文档链接: https://docs.prestd.com/prestd/docs/api-reference/endpoints/#patch-and-put

修改数据的操作比较简单,通常是向 prestd 发送一个 PUT 请求,在 URL 的路径中指定数据表,用查询参数过滤出想要操作的数据,请求载荷即为想要修改的数据。

比如说,我们发现之前的数据有错,希望把 id 为 1 的数据记录中的 age 改为 23 ,不难写出如下 SQL

UPDATE prestd.prest.persons
SET age=23
WHERE id=1

那么对应的 prestd 请求为

~ » curl -X PUT http://localhost:3000/prestd/prest/persons\?id\=1 -d '
{
    "age": 23
}'

此时查看该条数据,可以看到 age 的值已经变为 23 了

~ » curl http://localhost:3000/prestd/prest/persons\?id\=1
[{"id":1,"name":"Bob","age":23,"gender":"male"}]

注意:如果在修改数据时不加条件,该请求会修改表中所有的数据!

文档链接: https://docs.prestd.com/prestd/docs/api-reference/endpoints/#delete

删数据的请求格式类似于上面的改数据,只不过将 PUT 请求换成了 DELETE 请求。

比如说现在有个需求,要删掉 id 为 5 的数据, SQL 为

DELETE FROM prestd.prest.persons
WHERE id=5

对应的 prestd 请求为

~ » curl -X DELETE http://localhost:3000/prestd/prest/persons\?id\=5

注意:如果在删除数据时不加条件,该请求会删掉表中所有的数据!

复杂查询

除了上述的简单操作, prestd 也支持更加复杂的查询,同样地,在此只介绍平时用的比较多的几种。

在深入之前,我们先把上面删掉的 id 为 5 的记录添加回来,

curl -X POST http://localhost:3000/prestd/prest/persons -d '
{
    "id":5,
    "name":"Eve",
    "age":24,
    "gender":"female"
}'

聚合查询

文档链接: https://docs.prestd.com/prestd/api-reference/parameters/#functions-support

SQL 中允许用户从一组数据记录中得出一条单独的结论,比如说求平均值、最大值等,我们也可以用 prestd 得到想要的结果。

比如说我们想知道在表中男女分组中最大年龄分别是多少,用 SQL 的话很容易,

SELECT gender, MAX(age)
FROM prestd.prest.persons
GROUP BY gender

用 prestd 也不难,

~ » curl http://localhost:3000/prestd/prest/persons\?_select\=gender,max:age\&_groupby\=gender
[{"gender":"female","max":24},
 {"gender":"male","max":23}]

但是通过 prestd 进行聚合查询有一个限制,就是必须要在查询参数里面带上 _groupby ,否则会报错说找不到该列,比如说想要查询五个人中最大的年龄是多少,就会得到如下的错误,

~ » curl http://localhost:3000/prestd/prest/persons\?_select\=max:age
{
	"error": "invalid identifier max:age"
}

希望 prestd 不久之后可以添加对这种查询语句的支持。

JOIN

文档链接: https://docs.prestd.com/prestd/docs/api-reference/advanced-queries/#join

JOIN 操作可以说是 SQL 中多表查询的常客,因为在生产环境中往往有不同的数据模型分别存在不同的表中,在查询数据时如果涉及到多个表的内容,就需要通过 JOIN 将两个表中有联系的数据放在一起才能得到结果。

为了演示,首先要创建第二张数据表 schools

CREATE TABLE prest.schools (
    id SERIAL PRIMARY KEY,
    name VARCHAR(30)
);

因为一所学校中可以有多个学生,学校和学生为一对多的关系,所以为已有的 persons 表添加指向学校的外键,

ALTER TABLE prest.persons ADD COLUMN school_id int;
ALTER TABLE prest.persons ADD CONSTRAINT fk_persons_schools FOREIGN KEY (school_id) REFERENCES schools(id);

向表中填入数据,

~ » curl -X POST http://localhost:3000/batch/prestd/prest/schools -d '
[
    {"id": 1, "name": "Foo"},
    {"id": 2, "name": "Bar"}
]'

随便给学生们分配一下学校, id 小于等于 3 的到学校 1 号,大于 3 的到学校 2 号,

curl -X PUT http://localhost:3000/prestd/prest/persons\?id\='$lte.3' -d '{"school_id":1}'
curl -X PUT http://localhost:3000/prestd/prest/persons\?id\='$gt.3' -d '{"school_id":2}'

那么现在问题来了,在学校 “Foo” 就读的学生中年龄大于等于 22 岁的学生都有谁?

SQL 的语法依然很简单,

SELECT persons.name
FROM prestd.prest.persons
JOIN prestd.prest.schools
ON (persons.school_id=schools.id)
WHERE schools.name='Foo'
  AND persons.age>=22;

照着官方文档翻译成 prestd 请求则为

curl http://localhost:3000/prestd/prest/persons\?_select\=persons.name\&_join\=inner:schools:persons.school_id:\$eq:schools.id\&schools.name\='Foo'\&persons.age\='$gte.22'
[{"name":"Bob"},
 {"name":"Charlie"}]

可以看出,用 prestd 进行 JOIN 查询是非常麻烦的一件事, URL 中的参数很多,一不小心就容易写错,为了方便用户, prestd 还支持通过 SQL 模板进行自定义查询。

自定义查询

文档链接: https://docs.prestd.com/prestd/docs/api-reference/queries/

prestd 允许用户自定义 SQL 查询模板,在发送 http 请求的时候填入参数即可使用。回到上面 JOIN 查询的问题,如果将其一般化的话,就是给定学校名称,给定年龄限制,要求得到学生的名单,那么如何创建这样的自定义查询呢?

首先在 ~/prestd 下创建文件夹 queries/my_queries

~/prestd » mkdir -p queries/my_queries

然后在 my_queries 文件夹里创建文件 student_names.read.sql ,并填入如下查询语句,

SELECT persons.name
FROM prestd.prest.persons
JOIN prestd.prest.schools
ON (persons.school_id=schools.id)
WHERE schools.name='{{.school_name}}'
  AND persons.age>={{.person_age}}

此时 ~/prestd 文件夹的内容应如下所示,

~/prestd » tree .
.
├── prest.toml
└── queries
    └── my_queries
        └── student_names.read.sql

在 prestd 配置文件中添加自定义查询文件的路径并重启,

[queries]
location = /path/to/prestd/queries/

这里的路径需要 queries 文件夹的绝对路径。

发送一条请求试试看,

~ » curl http://localhost:3000/_QUERIES/my_queries/student_names\?school_name\='Foo'\&person_age\='22'
[{"name":"Bob"},
 {"name":"Charlie"}]

正是我们想要的结果!

值得一提的是 prestd 对 sql 模板文件的后缀有要求,创建文件之前需要想好该查询的目的, http 请求的动作必须与后缀相匹配,要不然就会翻车了。详情可以查看文档中对文件后缀的说明。

👉 在官方文档中有对模板语法的详细介绍,由于不是本文的重点,在此不再赘述,有兴趣的读者可以去 prestd 的官网查询参考。

参考资料

发表评论

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据