【原文】 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
2
3
4
5
6
7
8
9
10
-- config.lua
local config = require("lapis.config")
config("development", {
  postgres = {
    host = "127.0.0.1",
    user = "pg_user",
    password = "the_password",
    database = "my_database"
  }
})

host 默认值是 127.0.0.1， user 默认值是 postgres，所以你看你的配置都默认值没有区别的话可以直接去掉。如果没有使用默认的端口号，你可以直接跟在 host 的后面，语法如下：my_host:1234 （否则 5432， 这是 PostgreSQL 的默认值）。

MySQL

如果你使用的是 MySQL ，和上面是类似的；你需呀定义一个 mysql 块：

1
2
3
4
5
6
7
8
9
10
-- config.lua
local config = require("lapis.config")
config("development", {
  mysql = {
    host = "127.0.0.1",
    user = "mysql_user",
    password = "the_password",
    database = "my_database"
  }
})

现在你可以开始查询操作了。

创建一个查询

这是两种查询的方式：

  • 使用原生的 SQL 查询，也就是我们常说的裸写 SQL。
  • Model 类里面封装了常见的查询，可以直接使用。

优先选择使用 Model 里面的方法。当然有些复杂的操作还是选择使用原生的 SQL 查询更加的方便。

以下是一个裸写 SQL 的事例：

1
2
3
4
5
6
7
8
9
10
11
local lapis = require("lapis")
local db = require("lapis.db")

local app = lapis.Application()

app:match("/", function()
  local res = db.query("select * from my_table where id = ?", 10)
  return "ok!"
end)

return app

下面是使用 Model 实现同样的功能：

1
2
3
4
5
6
7
8
9
10
11
12
13
local lapis = require("lapis")
local Model = require("lapis.db.model").Model

local app = lapis.Application()

local MyTable = Model:extend("my_table")

app:match("/", function()
  local row = MyTable:find(10)
  return "ok!"
end)

return app

默认情况下，所有的查询都会被记录日志。你可以直接通过日志查看具体的操作。

查询接口

1
local db = require("lapis.db")

这个 db 模块提供了如下的方法：

query(query, params...)

执行原生的查询。如果查询成功会返回一个结果集，如果失败会返回一个 nil。

第一个参数就是要被执行的 SQL 语句。如果 SQL 语句中包含 ? 会使用后面的参数的值依次替换。后面的参数在赋值 SQL 语句之前会进行 escape_literal 转换操作，避免 SQL 注入的问题。

1
2
3
4
5
local res

res = db.query("SELECT * FROM hello")
res = db.query("UPDATE things SET color = ?", "blue")
res = db.query("INSERT INTO cats (age, name, alive) VALUES (?, ?, ?)", 25, "dogman", true)
1
2
3
SELECT * FROM hello
UPDATE things SET color = 'blue'
INSERT INTO cats (age, name, alive) VALUES (25, 'dogman', TRUE)

如果查询失败会向上抛出一个 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
2
3
4
db.insert("my_table", {
  age = 10,
  name = "Hello World"
})
1
INSERT INTO "my_table" ("age", "name") VALUES (10, 'Hello World')

当然也可以指定插入行的数据的某一列作为返回值：

1
2
3
local res = db.insert("some_other_table", {
  name = "Hello World"
}, "id")
1
INSERT INTO "some_other_table" ("name") VALUES ('Hello World') RETURNING "id"

注意：RETURNING 这个是 PostgreSQL 的特性，MySQL 没有。

update(table, values, conditions, params...)

更新表中满足 conditions 条件的所有的值。

1
2
3
4
5
6
db.update("the_table", {
  name = "Dogbert 2.0",
  active = true
}, {
  id = 100
})
1
UPDATE "the_table" SET "name" = 'Dogbert 2.0', "active" = TRUE WHERE "id" = 100

conditions 也可以是字符串，params 将要替换掉其中需要替换的值：

1
2
3
db.update("the_table", {
  count = db.raw("count + 1")
}, "count < ?", 10)
1
UPDATE "the_table" SET "count" = count + 1 WHERE count < 10

当 conditions 是一个 table 的时候，所有的其它的参数会被用于 "RETURNING"：

1
2
3
4
5
db.update("cats", {
  count = db.raw("count + 1")
}, {
  id = 1200
}, "count")
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
2
3
4
5
db.update("the_table", {
  count = db.raw("count + 1")
})

db.select("* from another_table where x = ?", db.raw("now()"))
1
2
UPDATE "the_table" SET "count" = count + 1
SELECT * from another_table where x = now()

list({values...})

会按照 SQL 的 list 的语法转换成指定的数据。

这个函数通常在查询时使用。同时里面的数据在传递给 SQL 之前会经过 escape_literal 进行转义操作。

同时我们也可以用在数据库更新的场景下：

1
2
3
4
5
6
7
8
local ids = db.list({3,2,1,5})
local res = db.select("* from another table where id in ?", ids)

db.update("the_table", {
  height = 55
}, {
  id = ids
})
1
2
SELECT * from another table where id in (3, 2, 1, 5)
UPDATE "the_table" SET "height" = 55 WHERE "ids" IN (3, 2, 1, 5)

array({values...})

在 PostgreSQL 中将数据转换成数组进行插入／更新操作的。MySQL 里面没有这个功能。

可以在任意的常规的SQL传值查询操作中。在查询前所有的数据都会被 escape_literal 进行转义操作。

参数会被转换，不是单纯的拷贝。在使用过程中需要注意这一点。

1
2
3
db.insert("some_table", {
  tags = db.array({"hello", "world"})
})
1
INSERT INTO "some_table" ("tags") VALUES (ARRAY['hello','world'])

escape_literal(value)

数据转义功能。任何数据都可以使用进行转义。Number 、 string 、 boolean 都会被相应的转义。

1
2
local escaped = db.escape_literal(value)
local res = db.query("select * from hello where id = " .. escaped)

escape_literal 不适合表名、列名。具体想对表名操作可以看 escape_identifier。

escape_identifier(str)

用于转换一个字符串用于查询中的标识符。标识符通常是列名、表名等。

1
2
local table_name = db.escape_identifier("table")
local res = db.query("select * from " .. table_name)

escape_identifier 不适合转义值。想对值进行转义可以看 escape_literal。

interpolate_query(query, ...)

用于替换查询语句中的 ?，剩下的参数的转义通过 escape_literal。

1
2
3
local q = "select * from table"
q = q .. db.interpolate_query("where value = ?", 42)
local res = db.query(q)

常量

以下是常见的常量：

  • NULL – 代表 SQL 里面的 NULL
  • TRUE – 代表 SQL 里面的 TRUE
  • FALSE – 代表 SQL 里面的 FALSE
1
2
3
db.update("the_table", {
  name = db.NULL
})

数据库模式

Lapis 在 lapis.db.schema 模块中提供了一系列的创建数据的模式。

创建和drop表

create_table(table_name, { table_declarations... })

第一个参数是需要创建的表名称，第二个参数一个 table 里面存储着这张表的细节。

1
2
3
4
5
6
7
8
9
10
local schema = require("lapis.db.schema")

local types = schema.types

schema.create_table("users", {
  {"id", types.serial},
  {"username", types.varchar},

  "PRIMARY KEY (id)"
})

注意：在 MySQL 中你可以用 types.id 去设置自增主键 ID。不可以使用 PRIMARY KEY (id)。

上面的例子会被解析成下面的 SQL：

1
2
3
4
5
 CREATE TABLE IF NOT EXISTS "users" (
  "id" serial NOT NULL,
  "username" character varying(255) NOT NULL,
  PRIMARY KEY (id)
);

第二个参数可以是字符串，也可以是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
2
3
4
5
6
7
local create_index = schema.create_index

create_index("users", "created_at")
create_index("users", "username", { unique = true })

create_index("posts", "category", "title")
create_index("uploads", "name", { where = "not deleted" })

解析成 SQL 语句就是：

1
2
3
4
CREATE INDEX ON "users" (created_at);
CREATE UNIQUE INDEX ON "users" (username);
CREATE INDEX ON "posts" (category, title);
CREATE INDEX ON "uploads" (name) WHERE not deleted;

drop_index(table_name, col1, col2...)

删除表的索引。跟创建索引几乎类似。

1
2
3
4
local drop_index = schema.drop_index

drop_index("users", "created_at")
drop_index("posts", "title", "published")

解析成 SQL 如下：

1
2
DROP INDEX IF EXISTS "users_created_at_idx"
DROP INDEX IF EXISTS "posts_title_published_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
2
3
4
5
6
7
8
9
10
11
12
13
14
local types = require("lapis.db.schema").types

print(types.boolean)       --> boolean NOT NULL DEFAULT FALSE
print(types.date)          --> date NOT NULL
print(types.double)        --> double precision NOT NULL DEFAULT 0
print(types.foreign_key)   --> integer NOT NULL
print(types.integer)       --> integer NOT NULL DEFAULT 0
print(types.numeric)       --> numeric NOT NULL DEFAULT 0
print(types.real)          --> real NOT NULL DEFAULT 0
print(types.serial)        --> serial NOT NULL
print(types.text)          --> text NOT NULL
print(types.time)          --> timestamp without time zone NOT NULL
print(types.varchar)       --> character varying(255) NOT NULL
print(types.enum)          --> smallint NOT NULL

你会发现所有的类型的默认都是 NOT NULL，同时数字类型默认 0 布尔类型默认 false。

当类型当作函数一样调用的时候，以下是可选值：

  • default: value – 设置默认值
  • null: boolean – 定义列是否是 NOT NULL
  • unique: boolean – 定义列是否有唯一索引
  • primary_key: boolean – 定义列是否是主键
  • array: bool|number– 仅限 PostgreSQL ，设置一个几维的数组， true 等同于 1

以下是具体的事例：

1
2
3
4
5
types.integer({ default = 1, null = true })  --> integer DEFAULT 1
types.integer({ primary_key = true })        --> integer NOT NULL DEFAULT 0 PRIMARY KEY
types.text({ null = true })                  --> text
types.varchar({ primary_key = true })        --> character varying(255) NOT NULL PRIMARY KEY
types.real({ array = true })                 --> real[]

注意：MySQL 的类型集和 PostgreSQL 完全不一样，具体请查看 MySQL 类型

数据库 Migration

为了迎合业务逻辑的变更，我们需要有一个机制可以同步修改我们的数据库。

我们通常会定义一个函数的 table ，table 的 key 就是 migration 的名称。我们可以随意定义 migration 的名称，当然还是建议用时间戳定义 migration 的名字：

1
2
3
4
5
6
7
8
9
10
11
local schema = require("lapis.db.schema")

return {
  [1368686109] = function()
    schema.add_column("my_table", "hello", schema.types.integer)
  end,

  [1368686843] = function()
    schema.create_index("my_table", "hello")
  end
}

一个 migration 函数表面看就是一个普通的函数。通常它们会调用上面的 schema 函数，但是实际上有些是没有必要的。

当启动 migration 时，只有没有被执行过的会被调用成功。所有已经被执行的 migration 会存储在 migrations 表中，这个表就用于存储哪些已经执行过了的 migration 的名字。同时所有的 migration 会被升序排列依次执行。

启动 Migration

Lapis 命令行工具又一个特殊的命令 lapis migrate 可以直接执行 migration。

这个命令将会调用 migrations 模块将上面的数据格式进行数据库操作。

现在我们创建一个仅有一个 migration 的文件:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- migrations.lua

local schema = require("lapis.db.schema")
local types = schema.types

return {
  [1] = function()
    schema.create_table("articles", {
      { "id", types.serial },
      { "title", types.text },
      { "content", types.text },

      "PRIMARY KEY (id)"
    })
  end
}

创建完毕之后，可以执行 lapis migrate 确保可以编译为 Lua。这个命令首先会去判断这个表是不是存在，如果不存在，会去创建这个表，如果存在会去执行没有被执行的 migration。

后续会做进一步的展开。

自定义启动 Migration

我们可以创建一个 migration 的表，具体的代码如下：

1
2
local migrations = require("lapis.db.migrations")
migrations.create_migrations_table()

会被解析成如下的 SQL：

1
2
3
4
CREATE TABLE IF NOT EXISTS "lapis_migrations" (
  "name" character varying(255) NOT NULL,
  PRIMARY KEY(name)
);

然后我们可以自定义启动 migration 了：

1
2
local migrations = require("lapis.db.migrations")
migrations.run_migrations(require("migrations"))

数据库辅助功能

这些 db 模块里面的辅助功能不能直接在查询接口里使用。

format_date(time)

返回一个数据库格式的时间字符串。

time 参数是可选的，默认是 UTC 时间。

1
2
local date = db.format_date()
db.query("update things set published_at = ?", date)