Lapis-数据库连接和查询
【原文】 http://leafo.net/lapis/reference/database.html
Lapis
有一系列的 class
和 function
用于 PostgreSQL
和 MySQL
的操作。未来将会支持更多的数据库。与此同时,你也可以使用 OpenResty
的数据库驱动,而不使用 Lapis
的查询 API
。
这里的每一个查询都是基于 OpenResty cosocket API
实现的异步查询。yield
和 resume
这些操作都是自动处理的;同时如果是异步环境,查询也可以顺序写入。另外会创建连接池以获得更好的性能。
具体细节看你使用的是什么数据库,以及使用的是哪个三方库:
pgmoon
是 PostgreSQL
的驱动。它的优势是除了命令行使用 LuaSocket
的同步 API
,其它的都使用 OpenResty cosocket API
。
在服务器的上下文中,lua-resty-mysql
是 MySQL
的驱动。当在命令行中是 LuaSQL
。
建立连接
你先需要创建你的数据库的配置
PostgreSQL
如果你使用的是 PostgreSQL
,你需要在 config.lua
中创建一个 postgres
块。
1 | -- config.lua |
host
默认值是 127.0.0.1
, user
默认值是 postgres
,所以你看你的配置都默认值没有区别的话可以直接去掉。如果没有使用默认的端口号,你可以直接跟在 host
的后面,语法如下:my_host:1234
(否则 5432
, 这是 PostgreSQL
的默认值)。
MySQL
如果你使用的是 MySQL
,和上面是类似的;你需呀定义一个 mysql
块:
1 | -- config.lua |
现在你可以开始查询操作了。
创建一个查询
这是两种查询的方式:
- 使用原生的
SQL
查询,也就是我们常说的裸写SQL
。 Model
类里面封装了常见的查询,可以直接使用。
优先选择使用 Model
里面的方法。当然有些复杂的操作还是选择使用原生的 SQL
查询更加的方便。
以下是一个裸写 SQL
的事例:
1 | local lapis = require("lapis") |
下面是使用 Model
实现同样的功能:
1 | local lapis = require("lapis") |
默认情况下,所有的查询都会被记录日志。你可以直接通过日志查看具体的操作。
查询接口
1 | local db = require("lapis.db") |
这个 db
模块提供了如下的方法:
query(query, params...)
执行原生的查询。如果查询成功会返回一个结果集,如果失败会返回一个 nil
。
第一个参数就是要被执行的 SQL
语句。如果 SQL
语句中包含 ?
会使用后面的参数的值依次替换。后面的参数在赋值 SQL
语句之前会进行 escape_literal
转换操作,避免 SQL
注入的问题。
1 | local res |
1 | SELECT * FROM hello |
如果查询失败会向上抛出一个 Lua
错误。错误信息包含查询的错误信息。
select(query, params...)
几乎和 query
的功能一样除了在查询语句前面的 "SELECT"
。
1 | local res = db.select("* from hello where active = ?", db.FALSE) |
1 | SELECT * from hello where active = FALSE |
insert(table, values, returning...)
向表里插入一行数据, values
就是列名和值。
1 | db.insert("my_table", { |
1 | INSERT INTO "my_table" ("age", "name") VALUES (10, 'Hello World') |
当然也可以指定插入行的数据的某一列作为返回值:
1 | local res = db.insert("some_other_table", { |
1 | INSERT INTO "some_other_table" ("name") VALUES ('Hello World') RETURNING "id" |
注意:RETURNING
这个是 PostgreSQL
的特性,MySQL
没有。
update(table, values, conditions, params...)
更新表中满足 conditions
条件的所有的值。
1 | db.update("the_table", { |
1 | UPDATE "the_table" SET "name" = 'Dogbert 2.0', "active" = TRUE WHERE "id" = 100 |
conditions
也可以是字符串,params
将要替换掉其中需要替换的值:
1 | db.update("the_table", { |
1 | UPDATE "the_table" SET "count" = count + 1 WHERE count < 10 |
当 conditions
是一个 table
的时候,所有的其它的参数会被用于 "RETURNING"
:
1 | db.update("cats", { |
1 | UPDATE "cats" SET "count" = count + 1, WHERE "id" = 1200 RETURNING count |
注意:RETURNING
这个是 PostgreSQL
的特性,MySQL
没有。
delete(table, conditions, params...)
删除表中的符合 conditions
条件的数据。
1 | db.delete("cats", { name = "Roo" }) |
1 | DELETE FROM "cats" WHERE "name" = 'Roo' |
conditions
也可以是一个字符串
1 | db.delete("cats", "name = ?", "Gato") |
1 | DELETE FROM "cats" WHERE name = 'Gato' |
raw(str)
返回一个原始的数据,不会被转换:
1 | db.update("the_table", { |
1 | UPDATE "the_table" SET "count" = count + 1 |
list({values...})
会按照 SQL
的 list
的语法转换成指定的数据。
这个函数通常在查询时使用。同时里面的数据在传递给 SQL
之前会经过 escape_literal
进行转义操作。
同时我们也可以用在数据库更新的场景下:
1 | local ids = db.list({3,2,1,5}) |
1 | SELECT * from another table where id in (3, 2, 1, 5) |
array({values...})
在 PostgreSQL
中将数据转换成数组进行插入/更新操作的。MySQL
里面没有这个功能。
可以在任意的常规的SQL
传值查询操作中。在查询前所有的数据都会被 escape_literal
进行转义操作。
参数会被转换,不是单纯的拷贝。在使用过程中需要注意这一点。
1 | db.insert("some_table", { |
1 | INSERT INTO "some_table" ("tags") VALUES (ARRAY['hello','world']) |
escape_literal(value)
数据转义功能。任何数据都可以使用进行转义。Number
、 string
、 boolean
都会被相应的转义。
1 | local escaped = db.escape_literal(value) |
escape_literal
不适合表名、列名。具体想对表名操作可以看 escape_identifier
。
escape_identifier(str)
用于转换一个字符串用于查询中的标识符。标识符通常是列名、表名等。
1 | local table_name = db.escape_identifier("table") |
escape_identifier
不适合转义值。想对值进行转义可以看 escape_literal
。
interpolate_query(query, ...)
用于替换查询语句中的 ?
,剩下的参数的转义通过 escape_literal
。
1 | local q = "select * from table" |
常量
以下是常见的常量:
NULL
– 代表SQL
里面的NULL
TRUE
– 代表SQL
里面的TRUE
FALSE
– 代表SQL
里面的FALSE
1 | db.update("the_table", { |
数据库模式
Lapis
在 lapis.db.schema
模块中提供了一系列的创建数据的模式。
创建和drop
表
create_table(table_name, { table_declarations... })
第一个参数是需要创建的表名称,第二个参数一个 table
里面存储着这张表的细节。
1 | local schema = require("lapis.db.schema") |
注意:在 MySQL
中你可以用 types.id
去设置自增主键 ID
。不可以使用 PRIMARY KEY (id)
。
上面的例子会被解析成下面的 SQL
:
1 | CREATE TABLE IF NOT EXISTS "users" ( |
第二个参数可以是字符串,也可以是table
。如果是一个 table
的话,会默认以 column / type
的格式进行解析:
1 | { column_name, column_type } |
它们都是普通的字符串。列名会被自动进行转义。列类型会被 toString
处理后进行传输。schema.types
有一系列的类型可选。例如,schema.types.varchar
等同于 character varying(255) NOT NULL
。更多例子下面会继续详细阐述。
如果第二次参数是字符串会被直接插入CREATE TABLE
语句中,比如上面的创建主键的例子。
drop_table(table_name)
drop
一张表。
1 | schema.drop_table("users") |
1 | DROP TABLE IF EXISTS "users"; |
索引
create_index(table_name, col1, col2..., [options])
create_index
用于常见表的索引。第一个参数是表名,其余的参数就是需要创建索引的列。最后一个参数可选,可以是一个 table
类型。
有两个可选值 unique: BOOL
, where: clause_string
。
create_index
创建索引之前会去检查这个索引是否存在。如果存在就什么都不做。
下面就是一个创建索引的例子:
1 | local create_index = schema.create_index |
解析成 SQL
语句就是:
1 | CREATE INDEX ON "users" (created_at); |
drop_index(table_name, col1, col2...)
删除表的索引。跟创建索引几乎类似。
1 | local drop_index = schema.drop_index |
解析成 SQL
如下:
1 | DROP INDEX IF EXISTS "users_created_at_idx" |
修改表结构
add_column(table_name, column_name, column_type)
给表添加一个列。
1 | schema.add_column("users", "age", types.integer) |
解析成 SQL
就如下:
1 | ALTER TABLE "users" ADD COLUMN "age" integer NOT NULL DEFAULT 0 |
drop_column(table_name, column_name)
删除列
1 | schema.drop_column("users", "age") |
解析成 SQL
为:
1 | ALTER TABLE "users" DROP COLUMN "age" |
rename_column(table_name, old_name, new_name)
列名的重命名操作
1 | schema.rename_column("users", "age", "lifespan") |
解析成 SQL
为:
1 | ALTER TABLE "users" RENAME COLUMN "age" TO "lifespan" |
rename_table(old_name, new_name)
表名重命名操作
1 | schema.rename_table("users", "members") |
解析成 SQL
为:
1 | ALTER TABLE "users" RENAME TO "members" |
列类型
所有的列类型都存储在 schema.types
中。所有的类型都是特殊的对象,不但可以通过 tostring
转换成一个类型声明的字符串,还可以像自定义函数进行调用。
以下是所有的默认值:
1 | local types = require("lapis.db.schema").types |
你会发现所有的类型的默认都是 NOT NULL
,同时数字类型默认 0
布尔类型默认 false
。
当类型当作函数一样调用的时候,以下是可选值:
default: value
– 设置默认值null: boolean
– 定义列是否是NOT NULL
unique: boolean
– 定义列是否有唯一索引primary_key: boolean
– 定义列是否是主键array: bool|number
– 仅限PostgreSQL
,设置一个几维的数组,true
等同于1
以下是具体的事例:
1 | types.integer({ default = 1, null = true }) --> integer DEFAULT 1 |
注意:MySQL
的类型集和 PostgreSQL
完全不一样,具体请查看 MySQL 类型
数据库 Migration
为了迎合业务逻辑的变更,我们需要有一个机制可以同步修改我们的数据库。
我们通常会定义一个函数的 table
,table
的 key
就是 migration
的名称。我们可以随意定义 migration
的名称,当然还是建议用时间戳定义 migration
的名字:
1 | local schema = require("lapis.db.schema") |
一个 migration
函数表面看就是一个普通的函数。通常它们会调用上面的 schema
函数,但是实际上有些是没有必要的。
当启动 migration
时,只有没有被执行过的会被调用成功。所有已经被执行的 migration
会存储在 migrations
表中,这个表就用于存储哪些已经执行过了的 migration
的名字。同时所有的 migration
会被升序排列依次执行。
启动 Migration
Lapis
命令行工具又一个特殊的命令 lapis migrate
可以直接执行 migration
。
这个命令将会调用 migrations
模块将上面的数据格式进行数据库操作。
现在我们创建一个仅有一个 migration
的文件:
1 | -- migrations.lua |
创建完毕之后,可以执行 lapis migrate
确保可以编译为 Lua
。这个命令首先会去判断这个表是不是存在,如果不存在,会去创建这个表,如果存在会去执行没有被执行的 migration
。
后续会做进一步的展开。
自定义启动 Migration
我们可以创建一个 migration
的表,具体的代码如下:
1 | local migrations = require("lapis.db.migrations") |
会被解析成如下的 SQL
:
1 | CREATE TABLE IF NOT EXISTS "lapis_migrations" ( |
然后我们可以自定义启动 migration
了:
1 | local migrations = require("lapis.db.migrations") |
数据库辅助功能
这些 db
模块里面的辅助功能不能直接在查询接口里使用。
format_date(time)
返回一个数据库格式的时间字符串。
time
参数是可选的,默认是 UTC
时间。
1 | local date = db.format_date() |