sqlfilestore

package module
v1.4.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: May 19, 2026 License: GPL-3.0 Imports: 13 Imported by: 1

README

SQL File Store

tests Go Report Card PkgGoDev

SQL File Store persists a hierarchical file-system-like structure in a relational database. It handles record creation, querying, soft deletion, and path recalculation for nested directories and files, while keeping a root directory available out of the box.

Features

  • Automatic schema creation (automigration) with a configurable table name.
  • Strongly typed Record helper with builders for files and directories.
  • CRUD helpers: create, update, hard delete, soft delete, and list with filtering options.
  • Recursive path recalculation to keep child paths in sync with renamed parents.
  • Works with standard database/sql connections and supports multiple SQL drivers through query builders.

Installation

go get github.com/dracory/sqlfilestore

Quick Start

package main

import (
    "database/sql"

    "github.com/dracory/sqlfilestore"
    _ "modernc.org/sqlite" // driver
)

func main() {
    db, err := sql.Open("sqlite", ":memory:?parseTime=true")
    if err != nil {
        panic(err)
    }

    store, err := sqlfilestore.NewStore(sqlfilestore.NewStoreOptions{
        DB:                 db,
        TableName:          "file_records",
        AutomigrateEnabled: true,
    })
    if err != nil {
        panic(err)
    }

    file := sqlfilestore.NewFile().
        SetParentID(sqlfilestore.ROOT_ID).
        SetName("example.txt").
        SetPath("/example.txt").
        SetExtension("txt").
        SetContents("Hello, world!")

    if err := store.RecordCreate(file); err != nil {
        panic(err)
    }
}

The example mirrors the test setup used for the in-memory SQLite driver.

Working with Records

All records share a common Record model. File and directory helpers pre-configure type-specific fields:

  • NewDirectory() sets type to directory, zeroes size, and clears file-only fields.
  • NewFile() sets type to file and leaves contents configurable.
  • NewRecord() creates a bare record with generated ID and timestamps.

Each record exposes setters/getters for metadata including name, path, parent ID, file size, extension, and timestamps.

Creating Directories
dir := sqlfilestore.NewDirectory().
    SetParentID(sqlfilestore.ROOT_ID).
    SetName("docs").
    SetPath("/docs")

if err := store.RecordCreate(dir); err != nil {
    // handle error
}
Updating Paths

Rename operations require updating the stored path. Use RecordRecalculatePath to refresh a record and its descendants after changing the name.

dir.SetName("manuals")
if err := store.RecordUpdate(dir); err == nil {
    _ = store.RecordRecalculatePath(dir, nil)
}

Querying Data

RecordQueryOptions lets you filter by ID, parent, type, path, timestamps, and sorting options.

records, err := store.RecordList(sqlfilestore.RecordQueryOptions{
    ParentID: sqlfilestore.ROOT_ID,
    Type:     sqlfilestore.TYPE_FILE,
    OrderBy:  "created_at",
    SortOrder: "asc",
})

Use RecordFindByID and RecordFindByPath for single-record lookups.

Soft Deletes vs. Hard Deletes

  • RecordSoftDeleteByID keeps the record while setting deleted_at for reversible removals.
  • RecordDeleteByID permanently removes a record, ensuring directories are empty beforehand.

Soft-deleted records are excluded by default; enable WithSoftDeleted in query options to include them.

Debugging

Enable SQL logging with store.EnableDebug(true) to print generated statements before execution.

Running Tests

The repository includes comprehensive tests that demonstrate typical interactions with the store.

go test ./...

License

GPL-3.0. See LICENSE.

Documentation

Index

Constants

View Source 
const (
	COLUMN_ID         = "id"
	COLUMN_PARENT_ID  = "parent_id"
	COLUMN_NAME       = "name"
	COLUMN_PATH       = "path"
	COLUMN_TYPE       = "type"
	COLUMN_SIZE       = "size"
	COLUMN_EXTENSION  = "extension"
	COLUMN_CONTENTS   = "contents"
	COLUMN_CREATED_AT = "created_at"
	COLUMN_UPDATED_AT = "updated_at"
	COLUMN_DELETED_AT = "deleted_at"
)

Database column names used in the file records table.

View Source 
const PATH_SEPARATOR = "/"

PATH_SEPARATOR is the path delimiter used throughout the file store.

View Source 
const ROOT_ID = "0"

ROOT_ID is the ID of the root directory record.

View Source 
const ROOT_PATH = PATH_SEPARATOR

ROOT_PATH is the path of the root directory.

View Source 
const TYPE_DIRECTORY = "directory"

TYPE_DIRECTORY is the record type value for directories.

View Source 
const TYPE_FILE = "file"

TYPE_FILE is the record type value for files.

Variables

This section is empty.

Functions

This section is empty.

Types

type NewStoreOptions 

type NewStoreOptions struct {
	TableName          string
	DB                 *sql.DB
	DbDriverName       string
	AutomigrateEnabled bool
	DebugEnabled       bool
}

NewStoreOptions defines the configuration options for creating a new Store. All fields are optional except TableName and DB which are required.

type Record 

type Record struct {
	dataobject.DataObject
}

Record represents a file or directory entry in the hierarchical file store. It embeds DataObject for field storage and provides typed accessors for file-system attributes like path, size, contents, and timestamps.

func NewDirectory 

func NewDirectory() *Record

NewDirectory creates a new Record preconfigured as a directory. It sets type to "directory", size to "0", and clears contents and extension.

func NewFile 

func NewFile() *Record

NewFile creates a new Record preconfigured as a file. It sets type to "file". Size, contents, and other fields must be set separately.

func NewRecord 

func NewRecord() *Record

NewRecord creates a new Record with default values. It generates a unique ID and sets created_at, updated_at, and deleted_at timestamps.

func NewRecordFromExistingData 

func NewRecordFromExistingData(data map[string]string) *Record

NewRecordFromExistingData creates a Record from a map of existing data. Used internally when hydrating records from database query results.

func (*Record) Contents 

func (o *Record) Contents() string

func (*Record) CreatedAt 

func (o *Record) CreatedAt() string

func (*Record) DeletedAt 

func (o *Record) DeletedAt() string

func (*Record) Extension 

func (o *Record) Extension() string

func (*Record) ID 

func (o *Record) ID() string

func (*Record) IsDirectory 

func (o *Record) IsDirectory() bool

IsDirectory returns true if this record represents a directory.

func (*Record) IsFile 

func (o *Record) IsFile() bool

IsFile returns true if this record represents a file.

func (*Record) Name 

func (o *Record) Name() string

func (*Record) ParentID 

func (o *Record) ParentID() string

func (*Record) Path 

func (o *Record) Path() string

func (*Record) SetContents 

func (o *Record) SetContents(fileContents string) *Record

func (*Record) SetCreatedAt 

func (o *Record) SetCreatedAt(createdAt string) *Record

func (*Record) SetDeletedAt 

func (o *Record) SetDeletedAt(deletedAt string) *Record

func (*Record) SetExtension 

func (o *Record) SetExtension(extension string) *Record

func (*Record) SetID 

func (o *Record) SetID(id string) *Record

func (*Record) SetName 

func (o *Record) SetName(name string) *Record

func (*Record) SetParentID 

func (o *Record) SetParentID(parentID string) *Record

func (*Record) SetPath 

func (o *Record) SetPath(filePath string) *Record

SetPath sets the file path. As all paths must start with "/" adds a "/" if not present. Any trailing spaces is also trimmed

func (*Record) SetSize 

func (o *Record) SetSize(fileSize string) *Record

func (*Record) SetType 

func (o *Record) SetType(fileType string) *Record

func (*Record) SetUpdatedAt 

func (o *Record) SetUpdatedAt(updatedAt string) *Record

func (*Record) Size 

func (o *Record) Size() string

func (*Record) Type 

func (o *Record) Type() string

func (*Record) UpdatedAt 

func (o *Record) UpdatedAt() string

type RecordQueryOptions 

type RecordQueryOptions struct {
	ID                   string
	IDIn                 []string
	ParentID             string
	Type                 string
	Path                 string
	PathStartsWith       string
	CreatedAtLessThan    string
	CreatedAtGreaterThan string
	UpdatedAtLessThan    string
	UpdatedAtGreaterThan string
	Columns              []string
	Offset               int
	Limit                int
	SortOrder            string
	OrderBy              string
	CountOnly            bool
	WithSoftDeleted      bool
}

RecordQueryOptions defines the available filters and options for record queries. Use this struct with RecordList, RecordCount, RecordFindByID, and RecordFindByPath.

type Store 

type Store struct {
	// contains filtered or unexported fields
}

Store provides a hierarchical file-system-like storage in a SQL database. It handles record creation, querying, soft deletion, and path recalculation for nested directories and files, while keeping a root directory available out of the box.

func NewStore 

func NewStore(opts NewStoreOptions) (*Store, error)

NewStore creates a new file store instance with the provided options.

Required options:

  • TableName: the database table name for storing records
  • DB: a *sql.DB database connection

Optional options:

  • DbDriverName: auto-detected if not provided
  • AutomigrateEnabled: if true, creates table and root directory automatically
  • DebugEnabled: if true, logs all SQL statements

Returns an error if required options are missing or if auto-migration fails.

func (*Store) EnableDebug 

func (st *Store) EnableDebug(debug bool)

EnableDebug enables or disables SQL debug logging. When enabled, generated SQL statements are printed to stdout.

func (*Store) MigrateDown added in v1.4.0 

func (store *Store) MigrateDown(ctx context.Context, tx ...*sql.Tx) error

MigrateDown drops the sqlfilestore table

func (*Store) MigrateUp added in v1.4.0 

func (store *Store) MigrateUp(ctx context.Context, tx ...*sql.Tx) error

MigrateUp creates the database table if it doesn't exist and ensures a root directory record is present. It runs automatically if AutomigrateEnabled is set to true in NewStoreOptions.

func (*Store) RecordCount 

func (st *Store) RecordCount(ctx context.Context, options RecordQueryOptions) (int64, error)

RecordCount returns the count of records matching the provided query options.

It builds a COUNT query based on the filter criteria in options (ID, ParentID, Type, Path filters, timestamps, soft delete status, etc.). The CountOnly flag is always set internally regardless of the input options.

Returns:

  • (count, nil) on success, where count is the number of matching records (0 or more)
  • (0, error) if query building fails, database query fails, or count parsing fails

Note: This function never returns negative counts. All error cases return 0 as the count value. The original implementation returned -1 on some errors, but this was changed to 0 for consistency.

func (*Store) RecordCreate 

func (store *Store) RecordCreate(ctx context.Context, record *Record) error

RecordCreate inserts a new record into the database. It automatically sets created_at and updated_at timestamps.

func (*Store) RecordDelete 

func (store *Store) RecordDelete(ctx context.Context, record *Record) error

RecordDelete permanently removes a record from the database. It delegates to RecordDeleteByID using the record's ID.

func (*Store) RecordDeleteByID 

func (store *Store) RecordDeleteByID(ctx context.Context, id string) error

RecordDeleteByID permanently deletes a record by its ID. It ensures directories are empty before deletion (no children allowed). Returns an error if the directory is not empty or if the ID is invalid.

func (*Store) RecordFindByID 

func (store *Store) RecordFindByID(ctx context.Context, id string, options RecordQueryOptions) (*Record, error)

RecordFindByID finds a single record by its ID. Returns nil if no record is found or if the ID is empty.

func (*Store) RecordFindByPath 

func (store *Store) RecordFindByPath(ctx context.Context, path string, options RecordQueryOptions) (*Record, error)

RecordFindByPath finds a single record by its path. Returns nil if no record is found or if the path is empty.

func (*Store) RecordList 

func (store *Store) RecordList(ctx context.Context, options RecordQueryOptions) ([]Record, error)

RecordList retrieves a list of records matching the provided query options. Supports filtering by ID, ParentID, Type, Path, timestamps, and soft delete status. Results can be limited, offset, and ordered using the options struct.

func (*Store) RecordRecalculatePath 

func (store *Store) RecordRecalculatePath(ctx context.Context, record *Record, parentRecord *Record) error

RecordRecalculatePath updates the path of a record and all its children after a rename or move operation. If parentRecord is nil, it looks up the parent. This recursively updates all descendant paths to reflect the new parent path.

func (*Store) RecordSoftDelete 

func (store *Store) RecordSoftDelete(ctx context.Context, record *Record) error

RecordSoftDelete marks a record as deleted by setting the deleted_at timestamp. The record remains in the database but is excluded from default queries.

func (*Store) RecordSoftDeleteByID 

func (store *Store) RecordSoftDeleteByID(ctx context.Context, id string) error

RecordSoftDeleteByID finds a record by ID and marks it as soft deleted. Returns an error if the record is not found.

func (*Store) RecordUpdate 

func (store *Store) RecordUpdate(ctx context.Context, record *Record) error

RecordUpdate updates an existing record in the database. It automatically sets the updated_at timestamp and only modifies changed fields. The ID field cannot be updated.

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.