prestd 试用笔记（三）——查询篇

Posted on by OldPanda
Photo by Ali Bakhtiari on Unsplash

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

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

简单查询

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

增

文档链接： 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 的官网查询参考。

参考资料

2 thoughts on “prestd 试用笔记（三）——查询篇”

发表评论

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