Sqlite DB

[ripe shoal]

Is there a way I can add an sqlite database to a project in railway and have its data persist across deployments in my project?

[flint sail]

Project ID: dfc8d88b-459f-4ec8-9e35-824c0bc7fbc0

[ripe shoal]

dfc8d88b-459f-4ec8-9e35-824c0bc7fbc0

[soft cape]

It is possible through the use of volumes

[ripe shoal]

currently I have a volume added to a deployment in my project, but when the deployment is redeployed, the volume is reset. Maybe I am not adding the sqlite db to the volume correctly?

the mount path for the volume is set as /db, and in my code, I store the sqlite database file as /db/sqlite.db

[soft cape]

volumes are mounted to the root of your container and your app is in the /app directory in your container, to make /db persist, you'll want to mount the volume to /app/db

[ripe shoal]

ahh ok

crap didnt mean to delete

does the project ID allow you to see that about my project?

[soft cape]

Conductors can't see anything inside your project, only team members can

when your app is built via nixpacks then your app is deployed into the /app directory by default

[ripe shoal]

my app is deployed via docker

[soft cape]

ooh i see, just make sure the volume is mounted on the global path of your db then

[ripe shoal]

so in my dockerfile, if I set the WORKDIR to /app the volume should mount to /app/db

like you suggested

[soft cape]

exactly right

[ripe shoal]

sorry im kinda new to this.

[soft cape]

no worries 🙂

[ripe shoal]

ok thanks for your help! I will go and try this out

[soft cape]

happy to help

[ripe shoal]

doesn't seem like it is working. When I set the mount path to /app, I get errors saying that /app/backend is not found. When I set it to /app/server the error disappears, but the database is not persistent as this directory is overwritten on each deployment.
Do you have any idea what is causing this?
Here is my dockerfile:

FROM golang:latest as builder

WORKDIR /app

COPY go.mod go.sum ./
RUN go mod download
COPY . .

RUN GOOS=linux go build -v -o backend ./server

FROM ubuntu:latest
RUN apt-get update && \
    apt-get install -y ca-certificates && \
    update-ca-certificates && \
    rm -rf /var/lib/apt/lists/*

COPY --from=builder /app/backend /app/backend
RUN chmod +x /app/backend
CMD ["/app/backend"]

[fringe kestrel]

fragly asked me to take over, it was already very late for him.

please show me the code that opens the sqlite database

[ripe shoal]

here is the code:

err := godotenv.Load()

    dbPath, ok := os.LookupEnv("RAILWAY_VOLUME_MOUNT_PATH")
    fmt.Println("ENV path: ", dbPath)
    if !ok {
        dbPath = "./"
    } 
    
    fmt.Println("Database path: ", dbPath)

    err = database.LoadAndMigrate(dbPath + "altera.db")
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println("Database loaded and migrated")

[fringe kestrel]

database.LoadAndMigrate("db/altera.db")
then mount the volume to /app/db

[ripe shoal]

I'll give it a try

It cant create the file db file in the directory

[fringe kestrel]

if the file doesnt exist your code needs to create it, its an empty volume after all

[ripe shoal]

In my github repo, there is an empty folder called db

so it should make it in there

maybe I need to add a chmod -R 777 /db to my dockerfile

[fringe kestrel]

I'm talking about the database file itself

[ripe shoal]

my code dos create it

when I run it locally at least. I think that its a linux permission issue, where the program doesnt have permission to create the file.

[fringe kestrel]

show me the updated code please

[ripe shoal]

The code didn't change. The LoadAndMigrate function creates the file (or the gorm.Open function inside that function does:

func LoadAndMigrate(sqliteFile string) error {
    if Instance != nil {
        return errors.New("database already initialized")
    }

    db, err := gorm.Open(sqlite.Open(sqliteFile), &gorm.Config{})
    if err != nil {
        return err
    }

    err = db.AutoMigrate(Models...)
    if err != nil {
        return err
    }

    Instance = db

    return nil
}

[fringe kestrel]

I had suggested code changes though

[ripe shoal]

ohh ye I did these

my bad i just wasnt thinking

[fringe kestrel]

please show me the updated code

[ripe shoal]

    err := godotenv.Load()

    err = database.LoadAndMigrate("db/altera.db")
    if err != nil {
        log.Fatal(err)
    }

removed the prints, changed the volume mount location and string in LoadAndMigrate function to "db/altera.db" as you suggested

I removed the environment variable stuff since it wasn't really serving any use

I believe you said to change the string passed to LoadAndMigrate which is what I did and that removed the necessity to have the previous code

[fringe kestrel]

I'd have to do some testing myself

currently making dinner though

[ripe shoal]

ok take ur time. Imma just mess around with stuff on my side and see if smth makes it work

[ripe shoal]

got it to work. I misstyped smth in railways

😭

[fringe kestrel]

what did you miss type

[ripe shoal]

db

was dv

[ripe shoal]

just wondering, is there a way to retrieve (or download) data from inside volumes (via ssh or smth)? or is that not supported?

[fringe kestrel]

that sure would do it

[fringe kestrel]

theres noting native, but there are these two templates
https://railway.app/template/guiCBv
https://railway.app/template/Nan7Bs