diff options
| -rw-r--r-- | .env.sqlite3 | 2 | ||||
| -rw-r--r-- | .github/workflows/test.yml | 2 | ||||
| -rw-r--r-- | CHANGELOG.md | 1 | ||||
| -rw-r--r-- | README.md | 3 | ||||
| -rw-r--r-- | go.mod | 1 | ||||
| -rw-r--r-- | go.sum | 2 | ||||
| -rw-r--r-- | internal/storage/db.go | 5 | ||||
| -rw-r--r-- | internal/storage/db_test.go | 12 | ||||
| -rw-r--r-- | internal/storage/mssql_test.go | 10 | ||||
| -rw-r--r-- | internal/storage/mysql_test.go | 8 | ||||
| -rw-r--r-- | internal/storage/postgresql_test.go | 8 | ||||
| -rw-r--r-- | internal/storage/sqlite3.go | 48 | ||||
| -rw-r--r-- | internal/storage/sqlite3_test.go | 94 |
13 files changed, 177 insertions, 19 deletions
diff --git a/.env.sqlite3 b/.env.sqlite3 new file mode 100644 index 0000000..b2300d7 --- /dev/null +++ b/.env.sqlite3 @@ -0,0 +1,2 @@ +BOLT_DB_CONN_DBNAME=/tmp/tmp.db +BOLT_DB_CONN_DRIVER=sqlite3 diff --git a/.github/workflows/test.yml b/.github/workflows/test.yml index 565aa80..6b2aa5e 100644 --- a/.github/workflows/test.yml +++ b/.github/workflows/test.yml @@ -15,10 +15,12 @@ jobs: - {name: postgresql} - {name: mysql} - {name: mssql} + - {name: sqlite3} steps: - name: Checkout Source Code uses: actions/checkout@v4 - name: Start Database + if: ${{ matrix.db.name }} != 'sqlite3' run: | env $(cat ".env.${{ matrix.db.name }}" | xargs) docker compose -f "docker-compose.${{ matrix.db.name }}.yml" up -d - name: Run Tests diff --git a/CHANGELOG.md b/CHANGELOG.md index 964d621..f627ead 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -10,6 +10,7 @@ and this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0 ### Added - Support for Microsoft SQL Server. +- Support for SQLite3. ## [0.5.0] - 2024-03-10 @@ -248,6 +248,7 @@ In your `upgrade.sql` or `downgrade.sql`, add the following line as the first li - PostgreSQL - MySQL - Microsoft SQL Server +- SQLite3 ### Configuration @@ -288,7 +289,7 @@ password = # The name of the database within your DBMS. dbname = # The name of the database driver to use to connect to -# the database. Either "postgresql", "mysql", or "mssql". +# the database. Either "postgresql", "mysql", "mssql", or "sqlite3". driver = ``` @@ -19,6 +19,7 @@ require ( github.com/jackc/pgpassfile v1.0.0 // indirect github.com/jackc/pgservicefile v0.0.0-20221227161230-091c0ba34f0a // indirect github.com/jackc/puddle/v2 v2.2.1 // indirect + github.com/mattn/go-sqlite3 v1.14.22 // indirect github.com/microsoft/go-mssqldb v1.7.0 // indirect golang.org/x/crypto v0.20.0 // indirect golang.org/x/sync v0.1.0 // indirect @@ -35,6 +35,8 @@ github.com/jackc/puddle/v2 v2.2.1 h1:RhxXJtFG022u4ibrCSMSiu5aOq1i77R3OHKNJj77OAk github.com/jackc/puddle/v2 v2.2.1/go.mod h1:vriiEXHvEE654aYKXXjOvZM39qJ0q+azkZFrfEOc3H4= github.com/kelseyhightower/envconfig v1.4.0 h1:Im6hONhd3pLkfDFsbRgu68RDNkGF1r3dvMUtDTo2cv8= github.com/kelseyhightower/envconfig v1.4.0/go.mod h1:cccZRl6mQpaq41TPp5QxidR+Sa3axMbJDNb//FQX6Gg= +github.com/mattn/go-sqlite3 v1.14.22 h1:2gZY6PC6kBnID23Tichd1K+Z0oS6nE/XwU+Vz/5o4kU= +github.com/mattn/go-sqlite3 v1.14.22/go.mod h1:Uh1q+B4BYcTPb+yiD3kU8Ct7aC0hY9fxUwlHK0RXw+Y= github.com/microsoft/go-mssqldb v1.7.0 h1:sgMPW0HA6Ihd37Yx0MzHyKD726C2kY/8KJsQtXHNaAs= github.com/microsoft/go-mssqldb v1.7.0/go.mod h1:kOvZKUdrhhFQmxLZqbwUV0rHkNkZpthMITIb2Ko1IoA= github.com/modocache/gover v0.0.0-20171022184752-b58185e213c5/go.mod h1:caMODM3PzxT8aQXRPkAt8xlV/e7d7w8GM5g0fa5F0D8= diff --git a/internal/storage/db.go b/internal/storage/db.go index 12adc4c..dc32304 100644 --- a/internal/storage/db.go +++ b/internal/storage/db.go @@ -8,17 +8,20 @@ import ( "github.com/eugenetriguba/bolt/internal/configloader" _ "github.com/go-sql-driver/mysql" _ "github.com/jackc/pgx/v5/stdlib" + _ "github.com/mattn/go-sqlite3" _ "github.com/microsoft/go-mssqldb" ) var postgresqlDriverName = "postgresql" var mysqlDriverName = "mysql" var mssqlDriverName = "mssql" +var sqliteDriverName = "sqlite3" var supportedDrivers = map[string]dbDriver{ postgresqlDriverName: {name: "pgx", adapter: PostgresqlAdapter{}}, mysqlDriverName: {name: "mysql", adapter: MySQLAdapter{}}, mssqlDriverName: {name: "sqlserver", adapter: MSSQLAdapter{}}, + sqliteDriverName: {name: "sqlite3", adapter: SqliteAdapter{}}, } type dbDriver struct { @@ -33,7 +36,7 @@ var ( ErrUnableToConnect = errors.New("unable to open connection to database") ErrUnsupportedDriver = fmt.Errorf( "unsupported driver, supported drivers are %s", - []string{postgresqlDriverName, mysqlDriverName, mssqlDriverName}, + []string{postgresqlDriverName, mysqlDriverName, mssqlDriverName, sqliteDriverName}, ) ) diff --git a/internal/storage/db_test.go b/internal/storage/db_test.go index 4b7dc93..f3386f5 100644 --- a/internal/storage/db_test.go +++ b/internal/storage/db_test.go @@ -3,6 +3,7 @@ package storage_test import ( "database/sql" "errors" + "os" "testing" "github.com/eugenetriguba/bolt/internal/bolttest" @@ -27,10 +28,13 @@ func TestNewDB_UnsupportedDriver(t *testing.T) { } func TestNewDB_UnableToConnect(t *testing.T) { - t.Setenv("BOLT_DB_CONN_HOST", "") - t.Setenv("BOLT_DB_CONN_PORT", "") - _, err := storage.NewDB(bolttest.NewTestConnectionConfig()) - assert.ErrorIs(t, err, storage.ErrUnableToConnect) + driver := os.Getenv("BOLT_DB_CONN_DRIVER") + if driver != "sqlite3" { + t.Setenv("BOLT_DB_CONN_HOST", "") + t.Setenv("BOLT_DB_CONN_PORT", "") + _, err := storage.NewDB(bolttest.NewTestConnectionConfig()) + assert.ErrorIs(t, err, storage.ErrUnableToConnect) + } } func TestClose_IsClosed(t *testing.T) { diff --git a/internal/storage/mssql_test.go b/internal/storage/mssql_test.go index 400b210..4a99ffd 100644 --- a/internal/storage/mssql_test.go +++ b/internal/storage/mssql_test.go @@ -13,7 +13,7 @@ import ( "github.com/eugenetriguba/checkmate/assert" ) -func TestConvertGenericPlaceholders(t *testing.T) { +func TestMSSQL_ConvertGenericPlaceholders(t *testing.T) { type test struct { query string argCount int @@ -44,7 +44,7 @@ func TestConvertGenericPlaceholders(t *testing.T) { } } -func TestTableExists(t *testing.T) { +func TestMSSQL_TableExists(t *testing.T) { cfg := bolttest.NewTestConnectionConfig() adapter := storage.MSSQLAdapter{} db, err := sql.Open("sqlserver", adapter.CreateDSN(cfg)) @@ -67,7 +67,7 @@ func TestTableExists(t *testing.T) { assert.True(t, exists) } -func TestDatabaseName(t *testing.T) { +func TestMSSQL_DatabaseName(t *testing.T) { cfg := bolttest.NewTestConnectionConfig() adapter := storage.MSSQLAdapter{} db, err := sql.Open("sqlserver", adapter.CreateDSN(cfg)) @@ -82,7 +82,7 @@ func TestDatabaseName(t *testing.T) { assert.Equal(t, name, cfg.DBName) } -func TestCreateDSN(t *testing.T) { +func TestMSSQL_CreateDSN(t *testing.T) { cfg := configloader.ConnectionConfig{ Driver: "mssql", Host: "db1", @@ -102,7 +102,7 @@ func TestCreateDSN(t *testing.T) { assert.Equal(t, cs, expectedConnectionString) } -func TestCreateDSN_UnparsablePort(t *testing.T) { +func TestMSSQL_CreateDSN_UnparsablePort(t *testing.T) { cfg := configloader.ConnectionConfig{ Driver: "mssql", Host: "db1", diff --git a/internal/storage/mysql_test.go b/internal/storage/mysql_test.go index 76f1098..b6f48b3 100644 --- a/internal/storage/mysql_test.go +++ b/internal/storage/mysql_test.go @@ -13,7 +13,7 @@ import ( "github.com/eugenetriguba/checkmate/assert" ) -func TestConvertGenericPlaceholders(t *testing.T) { +func TestMySQL_ConvertGenericPlaceholders(t *testing.T) { type test struct { query string argCount int @@ -44,7 +44,7 @@ func TestConvertGenericPlaceholders(t *testing.T) { } } -func TestTableExists(t *testing.T) { +func TestMySQL_TableExists(t *testing.T) { cfg := bolttest.NewTestConnectionConfig() adapter := storage.MySQLAdapter{} db, err := sql.Open("mysql", adapter.CreateDSN(cfg)) @@ -67,7 +67,7 @@ func TestTableExists(t *testing.T) { assert.True(t, exists) } -func TestDatabaseName(t *testing.T) { +func TestMySQL_DatabaseName(t *testing.T) { cfg := bolttest.NewTestConnectionConfig() adapter := storage.MySQLAdapter{} db, err := sql.Open("mysql", adapter.CreateDSN(cfg)) @@ -82,7 +82,7 @@ func TestDatabaseName(t *testing.T) { assert.Equal(t, name, cfg.DBName) } -func TestCreateDSN(t *testing.T) { +func TestMySQL_CreateDSN(t *testing.T) { cfg := configloader.ConnectionConfig{ Driver: "mysql", Host: "db1", diff --git a/internal/storage/postgresql_test.go b/internal/storage/postgresql_test.go index 8218cc1..ef89cff 100644 --- a/internal/storage/postgresql_test.go +++ b/internal/storage/postgresql_test.go @@ -13,7 +13,7 @@ import ( "github.com/eugenetriguba/checkmate/assert" ) -func TestConvertGenericPlaceholders(t *testing.T) { +func TestPostgresql_ConvertGenericPlaceholders(t *testing.T) { type test struct { query string argCount int @@ -44,7 +44,7 @@ func TestConvertGenericPlaceholders(t *testing.T) { } } -func TestTableExists(t *testing.T) { +func TestPostgresql_TableExists(t *testing.T) { cfg := bolttest.NewTestConnectionConfig() adapter := storage.PostgresqlAdapter{} db, err := sql.Open("pgx", adapter.CreateDSN(cfg)) @@ -67,7 +67,7 @@ func TestTableExists(t *testing.T) { assert.True(t, exists) } -func TestDatabaseName(t *testing.T) { +func TestPostgresql_DatabaseName(t *testing.T) { cfg := bolttest.NewTestConnectionConfig() adapter := storage.PostgresqlAdapter{} db, err := sql.Open("pgx", adapter.CreateDSN(cfg)) @@ -82,7 +82,7 @@ func TestDatabaseName(t *testing.T) { assert.Equal(t, name, cfg.DBName) } -func TestCreateDSN(t *testing.T) { +func TestPostgresql_CreateDSN(t *testing.T) { cfg := configloader.ConnectionConfig{ Driver: "postgres", Host: "db1", diff --git a/internal/storage/sqlite3.go b/internal/storage/sqlite3.go new file mode 100644 index 0000000..877dc7e --- /dev/null +++ b/internal/storage/sqlite3.go @@ -0,0 +1,48 @@ +package storage + +import ( + "fmt" + + "github.com/eugenetriguba/bolt/internal/configloader" +) + +type SqliteAdapter struct{} + +func (s SqliteAdapter) ConvertGenericPlaceholders( + query string, + argsCount int, +) string { + // The sqlite driver uses ? as a generic placeholder, + // so no translation is necessary. + return query +} + +func (s SqliteAdapter) TableExists( + executor sqlExecutor, + tableName string, +) (bool, error) { + var count int + err := executor.QueryRow(` + SELECT COUNT(*) + FROM sqlite_master + WHERE type='table' AND name=?; + `, tableName).Scan(&count) + if err != nil { + return false, fmt.Errorf( + "unable to check if %s exists: %w", + tableName, + err, + ) + } + + return count > 0, nil +} + +func (s SqliteAdapter) DatabaseName(executor sqlExecutor) (string, error) { + return "main", nil +} + +func (s SqliteAdapter) CreateDSN(cfg configloader.ConnectionConfig) string { + // Note: Use the dbname as the sqlite db name/path + return cfg.DBName +} diff --git a/internal/storage/sqlite3_test.go b/internal/storage/sqlite3_test.go new file mode 100644 index 0000000..69eea38 --- /dev/null +++ b/internal/storage/sqlite3_test.go @@ -0,0 +1,94 @@ +//go:build sqlite3 + +package storage_test + +import ( + "database/sql" + "testing" + + "github.com/eugenetriguba/bolt/internal/bolttest" + "github.com/eugenetriguba/bolt/internal/configloader" + "github.com/eugenetriguba/bolt/internal/storage" + "github.com/eugenetriguba/checkmate/assert" +) + +func TestSqlite3_ConvertGenericPlaceholders(t *testing.T) { + type test struct { + query string + argCount int + expectedQuery string + } + + testCases := []test{ + { + query: "SELECT id, name FROM some_table;", + argCount: 0, + expectedQuery: "SELECT id, name FROM some_table;", + }, + { + query: "SELECT id, name FROM some_table WHERE id = ?;", + argCount: 1, + expectedQuery: "SELECT id, name FROM some_table WHERE id = ?;", + }, + { + query: "SELECT id, name FROM some_table WHERE id = ? AND name = ?;", + argCount: 2, + expectedQuery: "SELECT id, name FROM some_table WHERE id = ? AND name = ?;", + }, + } + for _, tc := range testCases { + adapter := storage.MySQLAdapter{} + newQuery := adapter.ConvertGenericPlaceholders(tc.query, tc.argCount) + assert.Equal(t, newQuery, tc.expectedQuery) + } +} + +func TestSqlite3_TableExists(t *testing.T) { + cfg := bolttest.NewTestConnectionConfig() + adapter := storage.SqliteAdapter{} + db, err := sql.Open("sqlite3", adapter.CreateDSN(cfg)) + assert.Nil(t, err) + t.Cleanup(func() { + _, err = db.Exec("DROP TABLE IF EXISTS tmp;") + assert.Nil(t, err) + assert.Nil(t, db.Close()) + }) + + exists, err := adapter.TableExists(db, "tmp") + assert.Nil(t, err) + assert.False(t, exists) + + _, err = db.Exec("CREATE TABLE tmp(id INT PRIMARY KEY);") + assert.Nil(t, err) + + exists, err = adapter.TableExists(db, "tmp") + assert.Nil(t, err) + assert.True(t, exists) +} + +func TestSqlite3_DatabaseName(t *testing.T) { + cfg := bolttest.NewTestConnectionConfig() + adapter := storage.SqliteAdapter{} + db, err := sql.Open("sqlite3", adapter.CreateDSN(cfg)) + assert.Nil(t, err) + t.Cleanup(func() { + assert.Nil(t, db.Close()) + }) + + name, err := adapter.DatabaseName(db) + assert.Nil(t, err) + + assert.Equal(t, name, "main") +} + +func TestSqlite3_CreateDSN(t *testing.T) { + cfg := configloader.ConnectionConfig{ + Driver: "sqlite3", + DBName: "./tmp/test.db", + } + adapter := storage.SqliteAdapter{} + + cs := adapter.CreateDSN(cfg) + + assert.Equal(t, cs, cfg.DBName) +} |