Database Access

Lapis comes with a set of classes and functions for working with eitherPostgreSQL,MySQL orSQLite.

In the supported environments, queries are performed asynchronously (eg.OpenResty cosocket API)to allow for high throughput under heavy load. A request will yield and resumeautomatically when issuing queries so there’s no need to code with callbacks,queries can be written sequentially as if they were in a synchronousenvironment.

In supported environments, connection pooling will be used to ensure an optimalnumber of connections are opened to depending on the concurrent load to yourapplication.

Since SQL is embedded into your application, all queries are blocking and no connection pooling is used.

Depending on which database you use, a different library is used. You may needto install these libraries manually if you wish the use the associateddatabase. (It is recommended to add supplemental dependencies of yourapplication to a localrockspecfile.)

• PostgreSQL:pgmoon. Supports a widerange of environments like OpenResty’s cosocket API in addition LuaSocket andcqueues
• MySQL: When in the context of the OpenResty,lua-resty-mysql is usedotherwiseLuaSQL-MySQL
• SQLite:LuaSQLite3

Database Modules

Lapis provides a collection of Lua modules for performing operations againstyour database. The generic named modules will automatically select theappropriate database specific module based on your application’s configuration:

• require("lapis.db") — Manages connection,query,insert, etc. functions
• require("lapis.db.model") — The base class for Model classes backed by database tables & rows
• require("lapis.db.schema") — Changing your database’s schema, eg. creating tables, indexes, renaming, etc.

As an example, if your application is configured to PostgreSQL, then thefollowing three require statements above will actually load the followingmodules:lapis.db.postgres,lapis.db.postgres.model andlapis.db.postgres.schema.

The precedence for selecting a datbase is PostgreSQL, MySQL, SQLite. If youhave multiple database configurations then you will need to manually requirethe module for the database you wish to use.

Additionally, thelapis.db.migrations module manages a table that keeps trackof schema changes (aka Migrations). This module will utilize the genericlapis.db module, meaning it will use whatever database takes precedence inyour application’s configuration. Learn more aboutDatabaseMigrations

Establishing A Connection

You'll need to configure Lapis so it can connect to the database. Lapis managesa single global database connection (or pool of connections) in thelapis.dbmodule. It will ensure that you are using connections efficiently, and clean upany resources automatically. When you first attempt to send a query, Lapis willautomatically prepare a connection for use. It is not necessary to manuallyconnect or disconnect to the database for standard use.

If you need multiple database connections in the same project then you willhave to manually create and release them.

PostgreSQL

To use PostgreSQL create apostgres block in theconfig.moonconfig.luafile.

-- config.lualocalconfig=require("lapis.config")config("development",{postgres={host="127.0.0.1",user="pg_user",password="the_password",database="my_database"}})

-- config.moonimportconfigfromrequire"lapis.config"config"development",->postgres->host"127.0.0.1"user"pg_user"password"the_password"database"my_database"

host defaults to127.0.0.1 anduser defaults topostgres, so you canleave those fields out if they aren’t different from the defaults. If anon-default port is required it can be appended to thehost with colonsyntax:my_host:1234 (Otherwise5432, the PostgreSQL default, is used).

MySQL

To use MySQL create amysql block in theconfig.moonconfig.luafile.

-- config.lualocalconfig=require("lapis.config")config("development",{mysql={host="127.0.0.1",user="mysql_user",password="the_password",database="my_database"}})

-- config.moonimportconfigfromrequire"lapis.config"config"development",->mysql->host"127.0.0.1"user"mysql_user"password"the_password"database"my_database"

SQLite

If you use thelapis.db.sqlite module then a database namedlapis.sqlite will be created in the working directory by default.You can configure what database file to use like so:

-- config.lualocalconfig=require("lapis.config")config("development",{sqlite={database="my_database.sqlite",-- open_flags = ...}})

-- config.moonimportconfigfromrequire"lapis.config"config"development",->sqlite->database"my_database.sqlite"-- open_flags ...

You can use the specially named":memory:" database to have a temporarydatabase that lives only for the duration of your apps runtime.

Making a Query

There are generally two ways to work with the data in your database:

1. Thelapis.db module is a collection of functions to make queries to the database, returning the reults as plain Lua tables.
2. TheModel class is a wrapper around a Lua table that helps you synchronize it with a row in a database table. When appropriate, the results from the database are converted to instances of the model’s class.

TheModel class is the preferred way to interact with the database. Issuingqueries from thelapis.db module should preferred for achieving things theModel class is unable to do easily.

Here’s an example of how you might use thequery function in thelapis.dbmodule to issue a query to the database:

locallapis=require("lapis")localdb=require("lapis.db")localapp=lapis.Application()app:match("/",function()localres=db.query("select * from my_table where id = ?",10)return"ok!"end)returnapp

lapis=require"lapis"db=require"lapis.db"classextendslapis.Application"/":=>res=db.query"select * from my_table where id = ?",10"ok!"

And here’s how you would accomplish something similar usingModel class torepresent rows in a table:

locallapis=require("lapis")localModel=require("lapis.db.model").Modellocalapp=lapis.Application()localMyTable=Model:extend("my_table")app:match("/",function()localrow=MyTable:find(10)return"ok!"end)returnapp

lapis=require"lapis"importModelfromrequire"lapis.db.model"classMyTableextendsModelclassextendslapis.Application"/":=>row=MyTable\find10"ok!"

By default all queries will log to the Nginx notice log. You'll be able to seeeach query as it happens.

You can also issue queries in your command line scripts using the sameconfiguration, just require the model orlapis.db module and start using it.Keep in mind that your configuration is loaded based on the working directoryof your project, so you should execute your scripts from the same directory asyourconfig.moonconfig.lua file so that you configuration canbe loaded.

Query Interface

localdb=require("lapis.db")

db=require"lapis.db"

Thedb module provides the following functions:

db.query(query, params...)

Sends a query to the database using an active & available connection. If thereis no connection, then Lapis will automatically allocate & connect one usingthe details provided in your configuration.

Returns the results as a Lua table if successful. Will throw an error if theoperation failed.

The first argument is the query to perform. If the query contains any?s thenthey are replaced in the order they appear with the remaining arguments. Theremaining arguments are escaped withescape_literal before beinginterpolated, making SQL injection impossible.

localres1=db.query("SELECT * FROM hello")res1=db.query("SELECT * FROM users WHERE ?",db.clause({deleted=true,status="deleted"}))localres2=db.query("UPDATE things SET color = ?","blue")localres3=db.query("INSERT INTO cats (age, name, alive) VALUES (?, ?, ?)",25,"dogman",true)

res1=db.query"SELECT * FROM hello"res1=db.query"SELECT * FROM users WHERE ?",db.clause{deleted:truestatus:"deleted"}res2=db.query"UPDATE things SET color = ?","blue"res3=db.query"INSERT INTO cats (age, name, alive) VALUES (?, ?, ?)",25,"dogman",true

SELECT*FROMhelloSELECT*FROMusersWHEREdeletedANDstatus='deleted'UPDATEthingsSETcolor='blue'INSERTINTOcats(age,name,alive)VALUES(25,'dogman',TRUE)

A query that fails to execute will raise a Lua error. The error will containthe message from the database along with the query.

Every single function that Lapis provides which communicates with the databasewill eventually end up callingdb.query. The same logic with regards to errorhandling and connection management applies to all database operations thatLapis does.

db.select(query, params...)

Similar todb.query, but it appends"SELECT" to the front of the query.

localres=db.select("* from hello where active = ?",false)

res=db.select"* from hello where active = ?",false

SELECT*fromhellowhereactive=FALSE

db.insert(table, values, opts_or_returning...)

Inserts a row intotable.values is a Lua table of column names and values.

db.insert("my_table",{age=10,name="Hello World"})

db.insert"my_table",{age:10name:"Hello World"}

INSERTINTO"my_table"("age","name")VALUES(10,'Hello World')

A list of column names to be returned can be given after the value table:

localres=db.insert("some_other_table",{name="Hello World"},"id")

res=db.insert"some_other_table",{name:"Hello World"},"id"

INSERTINTO"some_other_table"("name")VALUES('Hello World')RETURNING"id"

RETURNING andON CONFLICT are PostgreSQL feature, and not available when using MySQL

Alternatively, a options table can be provided as third argument with supportfor the following fields: (When providing an options table, all other argumentsare ignored)

localres=db.insert("my_table",{color="blue"},{returning="*"})res=db.insert("my_table",{created_at="2021-4-11 6:6:20"},{returning={db.raw("date_trunc('year', created_at) as create_year")}})

res=db.insert"my_table",{color:"blue"},returning:"*"res=db.insert"my_table",{created_at:"2021-4-11 6:6:20"},{returning:{db.raw"date_trunc('year', created_at) as create_year"}}

localres=db.insert("my_table",{color="blue"},{on_conflict="do_nothing"})

res=db.insert("my_table",{color:"blue"},on_conflict:"do_nothing")

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

Updatestable withvalues on all rows that matchconditions. Ifconditions is a plain table or adb.clause object, then it will be convertedto SQL usingdb.encode_clause.

db.update("the_table",{name="Dogbert 2.0",active=true},{id=100,active=db.NULL})

db.update"the_table",{name:"Dogbert 2.0"active:true},{id:100active:db.NULL}

UPDATE"the_table"SET"name"='Dogbert 2.0',"active"=TRUEWHERE"id"=100and"active"ISNULL

The return value is a table containing the status of the update. The number ofrows updated can be determined by theaffected_rows field of the returnedtable.

conditions can also be a string, the remaining arguments will be interpolatedinto the query as if you calleddb.interpolate_query.

db.update("the_table",{count=db.raw("count + 1")},"count > ?",10)

db.update"the_table",{count:db.raw"count + 1"},"count > ?",10

UPDATE"the_table"SET"count"=count+1WHEREcount>10

When using a table ordb.clause argument for conditions, all the extraarguments are escaped as identifiers and appended as aRETURNING clause:

db.update("cats",{count=db.raw("count + 1")},{id=1200},"count")

db.update"cats",{count:db.raw"count + 1"},{id:1200},"count"

UPDATE"cats"SET"count"=count+1,WHERE"id"=1200RETURNINGcount

You can use adb.raw() in place of the returning identifier name toevaluate a raw sql expression.

When using the returning clause the return value ofdb.update will be anarray of rows generated by theRETURNING expression, in addition tocontaining theaffected_rows field.

RETURNING is a PostgreSQL feature, and is not available when using MySQL

db.delete(table, conditions, params...)

Deletes rows fromtable that matchconditions.

Theconditions arugment can either be a Lua table mapping column to value, adb.clause, or a string as a SQL fragment. When using the string condition,the remaining arguments as passed as parameters to the SQL fragment as if youcalleddb.interpolate_query.

The return value is a table containing the status of the delete. The number ofrows deleted can be determined by theaffected_rows field of the returnedtable.

db.delete("cats",{name="Roo"})db.delete("cats","name = ? and age is null","Gato")

db.delete"cats",name:"Roo"db.delete"cats","name = ? and age is null","Gato"

DELETEFROM"cats"WHERE"name"='Roo'DELETEFROM"cats"WHEREname='Gato'andageisnull

When using a table argument for conditions, all the extra arguments areescaped as identifiers and appended as aRETURNING clause:

db.delete("cats",{id=1200},"last_updated_at")

db.delete"cats",{id:1200},"last_updated_at"

DELETEFROM"cats"WHERE"id"=1200RETURNING"last_updated_at"

You can use adb.raw() in place of the returning identifier name toevaluate a raw sql expression.

The return value will now be an array of rows generated by the return values,in addition to containing theaffected_rows field.

RETURNING is a PostgreSQL feature, and is not available when using MySQL

db.escape_literal(value)

Escapes a value for use in a query. A value is any type that can be stored in acolumn. Numbers, strings, and booleans will be escaped accordingly.

localescaped=db.escape_literal(value)localres=db.query("select * from hello where id = "..escaped)

escaped=db.escape_literalvalueres=db.query"select * from hello where id = #{escaped}"

escape_literal is not appropriate for escaping column or table names. Seeescape_identifier.

db.escape_identifier(str)

Escapes a string for use in a query as an identifier. An identifier is a columnor table name.

localtable_name=db.escape_identifier("table")localres=db.query("select * from "..table_name)

table_name=db.escape_identifier"table"res=db.query"select * from #{table_name}"

escape_identifier is not appropriate for escaping values. Seeescape_literal for escaping values.

db.interpolate_query(query, ...)

Interpolates a query containing? markers with the rest of the argumentsescaped viaescape_literal. If adb.clause is passed as one of thearguments, then it will be encoded usingdb.encode_clause.

localq=db.interpolate_query("select * from table where value = ?",42)localres=db.query(q)

q=db.interpolate_query"select * from table where value = ?",42res=db.queryq

db.encode_clause(clause_obj)

Generates a boolean SQL expression from an object describing one or manyconditions. Theclause argument must be either a plain Lua table or a valuereturned bydb.clause.

If provided a plain table, then all key, value pairs are taken from the tableusingpairs,and converted to an SQL fragment similar todb.escape_identifier(key) = db.escape_literal(value), then concatenated withtheAND SQL operator.

print(db.encode_clause({name="Garf",color=db.list({"orange","ginger"}),processed_at=db.NULL}))

printdb.encode_clause{name:"Garf"color:db.list{"orange","ginger"}processed_at:db.NULL}--> "color" IN ('orange', 'ginger') AND "processed_at" IS NULL AND "name" = 'Garf'

If provided adb.clause, then a richer set of conditions can be described.See the documentation fordb.clause

db.encode_clause will throw an error on an empty clause. This is to preventthe mistake of accidentally providingnil in place of a value ofdb.NULLthat results in generating a clause that matches a much wider range of datathan desired.

Database Primitives

To make writing queries easier and safer, Lapis provides a set of basicprimitive types that can be used within your queries for constructing morecomplicated values. Generally speaking, you should avoid interpolating datadirectly into queries whenever possible as it creates the opportunity for SQLinjection attacks when values aren’t properly encoded.

All database primitives constructors and values can be found on thedbmodule:

localdb=require("lapis.db")

db=require"lapis.db"

db.raw(str)

Returns a an object wrapping the string argument that will be inserted verbatiminto a query without being escaped. Special care should be taken to avoidgenerating invalid SQL and and to avoid introducing SQL injection attacks byconcatenated unsafe data into the string.

db.raw can be used inin almost any place where SQL query construction takesplace. For example,db.escape_literal anddb.escape_identifier will bothpass the string through unchanged. It can also be used indb.encode_clausefor both keys and values. You can use it where things like column names ortable names are also requested (eg.db.update)

db.update("the_table",{count=db.raw("count + 1")})db.select("* from another_table where x = ?",db.raw("now()"))

db.update"the_table",{count:db.raw"count + 1"}db.select"* from another_table where x = ?",db.raw"now()"

UPDATE"the_table"SET"count"=count+1SELECT*fromanother_tablewherex=now()

db.is_raw(obj)

Returnstrue ifobj is a value created bydb.raw.

db.list({values...})

Returns a special value that will be inserted into the query using SQL’s listsyntax. It takes a single argument of an array table. A new object is returnedthat wraps the original table. The original table is not modified.

The resulting object can be used in place of a value used within SQL querygeneration with functions likeinterpolate_query andencode_clause. Eachitem in the list will be escaped withescape_literal before being insertedinto the query.

Note how when it is used as a value for an SQL clause object, theIN syntaxis used.

localids=db.list({3,2,1,5})localres=db.select("* from another table where id in ?",ids)db.update("the_table",{height=55},{ids=ids})

ids=db.list{3,2,1,5}res=db.select"* from another table where id in ?",idsdb.update"the_table",{height:55},{:ids}

db.is_list(obj)

Returnstrue ifobj is a value created bydb.list.

db.clause({clause...}, opts?)

Creates aclause object that can be encoded into a boolean SQL expression forfiltering or finding operations in the database. A clause object is anencodable type that can be used in places likedb.encode_clause and anddb.interpolate_query to safely generate an SQL fragment where all values areescaped accordingly. Any built in Lapis functions that can take an object tofilter the affected rows can also take a clause object in place of a queryfragment or plain Lua table.

By default, a clause object will combine all paramters contained with theANDoperator.

When encoded to SQL, the clause object will attempt to extract filters from allentries in the table:

• Key, value pairs in the hash-table portion of the clause table will be converted to a SQL fragment similar todb.escape_identifier(key) = db.escape_literal(value). This mode is aware of booleans anddb.list objects to generate the correct syntax
• Values in the array portion of the clause table will handled based on their type:
  • String values will be treated as raw SQL fragments that will be concatenated into the clause directly. All string values are warpped in() to ensure there are no precedence issues
  • Table values will passed tointerpolate_query if the sub-table’s first item is a string, eg.{"views_count > ?", 100}
  • Anil value will be skipped, meaning you can place conditionals directly inside of the clause
  • Clause objects can be nested by placing them in the array portion of the clause table

Here is an example demonstrating all the different ways of building out a clause:

localfilter=db.clause({id=12,"username like '%admin'",deleted=false,status=db.list({3,4}),{"views_count > ?",100},db.clause({active=true,promoted=true},{operator="OR"})})localres=db.query("SELECT * FROM profiles WHERE ?",filter)

filter=db.clause{id:12"username like '%admin'"deleted:falsestatus:db.list{3,4}{"views_count > ?",100}db.clause{active:truepromoted:true},operator:"OR"}res=db.query"SELECT * FROM profiles WHERE ?",filter

The following SQL will be generated:

SELECT*FROMprofilesWHERE(usernamelike'%admin')AND(views_count>100)AND("active"OR"promoted")AND"status"IN(3,4)AND"id"=12ANDnot"deleted",

The second argument can be a table of options. The following properties aresupported:

localfilter=db.clause({status="deleted",deleted=true},{operator="OR"})print(db.encode_clause(filter))

filter=db.clause{status:"deleted"deleted:true},operator:"OR"printdb.encode_clausefilter--> "deleted" OR "status" = 'deleted'

localfilter=db.clause({color="green",published=true},{table_name="posts"})print(db.encode_clause(filter))

filter=db.clause{color:"green"published:true},table_name:"posts"printdb.encode_clausefilter--> "posts"."color" = 'green' AND "posts"."published"

localsome_object={the_id=1}-- This will throw an error to prevent you from accidentally deleting all-- rows because of an empty clause created by a nil valuedb.delete("users",db.clause({user_id=something.id-- oops, used the wrong field name and set this to nil}))

some_object={the_id:1}-- This will throw an error to prevent you from accidentally deleting all-- rows because of an empty clause created by a nil valuedb.delete"users",db.clause{user_id:something.id-- oops, used the wrong field name and set this to nil}

db.encode_clause(db.clause({},{prefix="WHERE",allow_empty=true}))--> ""db.encode_clause(db.clause({id=5},{prefix="WHERE",allow_empty=true}))--> [[WHERE "id" = 5]]db.query("SELECT FROM users ?",db.clause({-- if params.id is nil, then the clause will be encoded to empty stringid=params.id},{prefix="WHERE",allow_empty=true}))

db.encode_clause(db.clause({},{prefix:"WHERE",allow_empty:true})--> ""db.encode_clause(db.clause({id:5},{prefix:"WHERE",allow_empty:true})--> [[WHERE "id" = 5]]db.query"SELECT FROM users ?",db.clause{-- if params.id is nil, then the clause will be encoded to empty stringid:params.id},prefix:"WHERE",allow_empty:true

db.is_clause(obj)

Returnstrue ifobj is a value created bydb.clause.

db.array({values...})

Converts the argument passed to an array type that will be inserted/updatedusing PostgreSQL’s array syntax. This function does not exist for MySQL.

The return value of this function can be used in place of any regular valuepassed to a SQL query function. Each item in the list will be escaped withescape_literal before being inserted into the query.

Note: This function mutates the object passed in by setting its metatable.The returning object is the same value as the argument. This will allow theobject to still function as a regular Lua array. If you do not want to mutatethe argument, you must make a copy before passing it in.

Additionally, when a query returns an array from the database, it isautomatically converted into adb.array.

db.insert("some_table",{tags=db.array({"hello","world"})})

db.insert"some_table",{tags:db.array{"hello","world"}}

INSERTINTO"some_table"("tags")VALUES(ARRAY['hello','world'])

db.is_array(obj)

Returnstrue ifobj is a table with thePostgresArray metatable (eg. avalue created bydb.array)

db.NULL

RepresentsNULL in SQL syntax. In Lua,nil can’t be stored in a table, so thedb.NULL object can be used to provideNULL as a value. When used withencode_clause, theIS NULL syntax is automatically used.

db.update("the_table",{name=db.NULL})

db.update"the_table",{name:db.NULL}

UPDATE"the_table"SETname=NULL

db.TRUE

RepresentsTRUE in SQL syntax. In most cases, it is not necessary to use thisconstant, and instead the Lua boolean values can be used.

db.FALSE

RepresentsFALSE in SQL syntax. In most cases, it is not necessary to use thisconstant, and instead the Lua boolean values can be used.

Database Schemas

Lapis comes with a collection of tools for creating your database schema insideof thelapis.db.schema module.

Creating and Dropping Tables

create_table(table_name, { table_declarations... })

The first argument tocreate_table is the name of the table and the secondargument is an array table that describes the table.

localschema=require("lapis.db.schema")localtypes=schema.typesschema.create_table("users",{{"id",types.serial},{"username",types.varchar},"PRIMARY KEY (id)"})

schema=require"lapis.db.schema"importcreate_table,typesfromschemacreate_table"users",{{"id",types.serial}{"username",types.varchar}"PRIMARY KEY (id)"}

In MySQL you should usetypes.id to get an autoincrementing primary key ID.Additionally you should not specifyPRIMARY KEY (id) either.

This will generate the following SQL:

CREATETABLEIFNOTEXISTS"users"("id"serialNOTNULL,"username"charactervarying(255)NOTNULL,PRIMARYKEY(id));

The items in the second argument tocreate_table can either be a table, or astring. When the value is a table it is treated as a column/type tuple:

{ column_name, column_type }

They are both plain strings. The column name will be escaped automatically.The column type will be inserted verbatim after it is passed throughtostring.schema.types has a collection of common types that can be used.For example,schema.types.varchar evaluates tocharacter varying(255) NOTNULL. See more about types below.

If the value to the second argument is a string then it is inserted directlyinto theCREATE TABLE statement, that’s how we create the primary key above.

drop_table(table_name)

Drops a table.

schema.drop_table("users")

importdrop_tablefromschemadrop_table"users"

DROPTABLEIFEXISTS"users";

Indexes

create_index(table_name, col1, col2..., [options])

create_index is used to add new indexes to a table. The first argument is atable, the rest of the arguments are the ordered columns that make up theindex. Optionally the last argument can be a Lua table of options.

There are two optionsunique: BOOL,where: clause_string.

create_index will also check if the index exists before attempting to createit. If the index exists then nothing will happen.

Here are some example indexes:

localcreate_index=schema.create_indexcreate_index("users","created_at")create_index("users","username",{unique=true})create_index("posts","category","title")create_index("uploads","name",{where="not deleted"})

importcreate_indexfromschemacreate_index"users","created_at"create_index"users","username",unique:truecreate_index"posts","category","title"create_index"uploads","name",where:"not deleted"

This will generate the following SQL:

CREATEINDEXON"users"(created_at);CREATEUNIQUEINDEXON"users"(username);CREATEINDEXON"posts"(category,title);CREATEINDEXON"uploads"(name)WHEREnotdeleted;

drop_index(table_name, col1, col2...)

Drops an index from a table. It calculates the name of the index from the tablename and columns. This is the same as the default index name generated bydatabase on creation.

localdrop_index=schema.drop_indexdrop_index("users","created_at")drop_index("posts","title","published")

importdrop_indexfromschemadrop_index"users","created_at"drop_index"posts","title","published"

This will generate the following SQL:

DROPINDEXIFEXISTS"users_created_at_idx"DROPINDEXIFEXISTS"posts_title_published_idx"

Altering Tables

add_column(table_name, column_name, column_type)

Adds a column to a table.

schema.add_column("users","age",types.integer)

importadd_column,typesfromschemaadd_column"users","age",types.integer

Generates the SQL:

ALTERTABLE"users"ADDCOLUMN"age"integerNOTNULLDEFAULT0

drop_column(table_name, column_name)

Removes a column from a table.

schema.drop_column("users","age")

importdrop_columnfromschemadrop_column"users","age"

Generates the SQL:

ALTERTABLE"users"DROPCOLUMN"age"

rename_column(table_name, old_name, new_name)

Changes the name of a column.

schema.rename_column("users","age","lifespan")

importrename_columnfromschemarename_column"users","age","lifespan"

Generates the SQL:

ALTERTABLE"users"RENAMECOLUMN"age"TO"lifespan"

rename_table(old_name, new_name)

Changes the name of a table.

schema.rename_table("users","members")

importrename_tablefromschemarename_table"users","members"

Generates the SQL:

ALTERTABLE"users"RENAMETO"members"

Column Types

All of the column type generators are stored inschema.types. All the typesare special objects that can either be turned into a type declaration stringwithtostring, or called like a function to be customized.

Here are all the default values:

localtypes=require("lapis.db.schema").typesprint(types.boolean)--> boolean NOT NULL DEFAULT FALSEprint(types.date)--> date NOT NULLprint(types.double)--> double precision NOT NULL DEFAULT 0print(types.foreign_key)--> integer NOT NULLprint(types.integer)--> integer NOT NULL DEFAULT 0print(types.numeric)--> numeric NOT NULL DEFAULT 0print(types.real)--> real NOT NULL DEFAULT 0print(types.serial)--> serial NOT NULLprint(types.text)--> text NOT NULLprint(types.time)--> timestamp without time zone NOT NULLprint(types.varchar)--> character varying(255) NOT NULLprint(types.enum)--> smallint NOT NULL

importtypesfromrequire"lapis.db.schema"types.boolean--> boolean NOT NULL DEFAULT FALSEtypes.date--> date NOT NULLtypes.double--> double precision NOT NULL DEFAULT 0types.foreign_key--> integer NOT NULLtypes.integer--> integer NOT NULL DEFAULT 0types.numeric--> numeric NOT NULL DEFAULT 0types.real--> real NOT NULL DEFAULT 0types.serial--> serial NOT NULLtypes.text--> text NOT NULLtypes.time--> timestamp without time zone NOT NULLtypes.varchar--> character varying(255) NOT NULLtypes.enum--> smallint NOT NULL

You'll notice everything isNOT NULL by default, and the numeric types havedefaults of 0 and boolean false.

When a type is called like a function it takes one argument, a table ofoptions. The options include:

• default: value — sets default value
• null: boolean — determines if the column isNOT NULL
• unique: boolean — determines if the column has a unique index
• primary_key: boolean — determines if the column is the primary key
• array: bool|number — makes the type an array (PostgreSQL Only), pass number to set how many dimensions the array is,true ==1

Here are some examples:

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

types.integerdefault:1,null:true--> integer DEFAULT 1types.integerprimary_key:true--> integer NOT NULL DEFAULT 0 PRIMARY KEYtypes.textnull:true--> texttypes.varcharprimary_key:true--> character varying(255) NOT NULL PRIMARY KEYtypes.realarray:true--> real[]types.textarray:2--> text[][]

MySQL has a complete different type set than PostgreSQL, seeMySQL types

Database Migrations

Because requirements typically change over the lifespan of a web applicationit’s useful to have a system to make incremental schema changes to thedatabase.

We define migrations in our code as a table of functions where the key of eachfunction in the table is the name of the migration. You are free to name themigrations anything but it’s suggested to give them Unix timestamps as names:

localschema=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}

importadd_column,create_index,typesfromrequire"lapis.db.schema"{[1368686109]:=>add_column"my_table","hello",types.integer[1368686843]:=>create_index"my_table","hello"}

A migration function is a plain function. Generally they will call theschema functions described above, but they don’t have to.

Only the functions that haven’t already been executed will be called when wetell our migrations to run. The migrations that have already been run arestored in the migrations table, a database table that holds the names of themigrations that have already been run. Migrations are run in the order of theirkeys sorted ascending.

Running Migrations

The Lapis command line tool has a special command for running migrations. It’scalledlapis migrate.

This command expects a module calledmigrations that returns a table ofmigrations in the format described above.

Let’s create this file with a single migration as an example.

-- migrations.lualocalschema=require("lapis.db.schema")localtypes=schema.typesreturn{[1]=function()schema.create_table("articles",{{"id",types.serial},{"title",types.text},{"content",types.text},"PRIMARY KEY (id)"})end}

-- migrations.moonimportcreate_table,typesfromrequire"lapis.db.schema"{[1]:=>create_table"articles",{{"id",types.serial}{"title",types.text}{"content",types.text}"PRIMARY KEY (id)"}}

After creating the file, ensure that it is compiled to Lua and runlapismigrate. The command will first create the migrations table if it doesn’texist yet then it will run every migration that hasn’t been executed yet.

Read more aboutthe migrate command.

Manually Running Migrations

We can manually create the migrations table using the following code:

localmigrations=require("lapis.db.migrations")migrations.create_migrations_table()

migrations=require"lapis.db.migrations"migrations.create_migrations_table!

It will execute the following SQL:

CREATETABLEIFNOTEXISTS"lapis_migrations"("name"charactervarying(255)NOTNULL,PRIMARYKEY(name));

Then we can manually run migrations with the following code:

localmigrations=require("lapis.db.migrations")migrations.run_migrations(require("migrations"))

importrun_migrationsfromrequire"lapis.db.migrations"run_migrationsrequire"migrations"

Database Helpers

These are additional helper functions from thedb module thataren’t directly related to the query interface.

db.format_date(time)

Returns a date string formatted properly for insertion in the database.

Thetime argument is optional, will default to the current UTC time.

localdate=db.format_date()db.query("update things set published_at = ?",date)

date=db.format_date!db.query"update things set published_at = ?",date