Colin Henry
b4529e018f
Architecture Changes: - Split dialect implementations into separate files for better organization - Move SQLite dialect from dialect.go to sqlite.go - Keep only Dialect interface in dialect.go with comprehensive documentation - Each dialect now in its own file following single responsibility principle New Features: - Add PostgreSQL dialect support (Postgres() function) - PostgreSQL uses SERIAL PRIMARY KEY (auto-incrementing integer) - PostgreSQL uses $1, $2 placeholders instead of ? for parameters - PostgreSQL uses SELECT 1 for table existence check (more efficient) - Both dialects implement proper SQL identifier quoting for security Testing: - Add comprehensive dialect-specific tests in sqlite_test.go - Add comprehensive dialect-specific tests in postgres_test.go - Test SQL generation for all dialect methods - Test SQL injection protection via identifier escaping - All tests pass (8 test functions, 10 subtests) Documentation: - Update README with PostgreSQL usage example - Add "Supported Databases" section listing SQLite and PostgreSQL - Improve code examples with proper imports and error handling - Document how to implement Dialect interface for other databases File Structure: - dialect.go: Interface definition only (18 lines) - sqlite.go: SQLite dialect implementation (39 lines) - postgres.go: PostgreSQL dialect implementation (42 lines) - sqlite_test.go: SQLite dialect tests (67 lines) - postgres_test.go: PostgreSQL dialect tests (67 lines) Security: - Both dialects use quoteIdentifier() for SQL injection protection - Identifiers are quoted and internal quotes are escaped - Follows SQL standard quoting mechanism (double quotes for escaping) This change maintains backward compatibility while adding PostgreSQL support and improving code organization for future dialect additions.
migrate
migrate is a package for SQL database migrations in the spirit of dbstore. It is intended to keep its footprint small, requiring only an additional table in the database. There is no rollback support as you should only ever roll forward. SQLite and PostgreSQL support is provided, support for other databases can be added by implementing the Dialect interface.
Installation
go get git.sdf.org/jchenry/migrate
Usage
SQLite Example
import (
"database/sql"
"git.sdf.org/jchenry/migrate"
_ "modernc.org/sqlite"
)
db, _ := sql.Open("sqlite", "database.db")
changes := []migrate.Change{
{
Description: "create people table",
Apply: func(ctx migrate.Context) error {
_, err := ctx.Exec(`
CREATE TABLE people (
given_name VARCHAR(20),
surname VARCHAR(30),
sex CHAR(1),
age SMALLINT);
`)
return err
},
},
{
Description: "Insert a person into people",
Apply: func(ctx migrate.Context) error {
_, err := ctx.Exec(`INSERT INTO people VALUES('Henry','Colin','M', 42)`)
return err
},
},
}
err := migrate.Apply(db, migrate.Sqlite3(), changes)
if err != nil {
// handle error
}
PostgreSQL Example
import (
"database/sql"
"git.sdf.org/jchenry/migrate"
_ "github.com/lib/pq"
)
db, _ := sql.Open("postgres", "postgres://user:password@localhost/dbname?sslmode=disable")
changes := []migrate.Change{
{
Description: "create people table",
Apply: func(ctx migrate.Context) error {
_, err := ctx.Exec(`
CREATE TABLE people (
given_name VARCHAR(20),
surname VARCHAR(30),
sex CHAR(1),
age SMALLINT);
`)
return err
},
},
}
err := migrate.Apply(db, migrate.Postgres(), changes)
if err != nil {
// handle error
}
Supported Databases
- SQLite - Use
migrate.Sqlite3() - PostgreSQL - Use
migrate.Postgres()
To add support for other databases, implement the Dialect interface.
Contributing
Pull requests are welcome. For major changes, please open an issue first to discuss what you would like to change.
Please make sure to update tests as appropriate.