# Sqlite database remains in use by Julia REPL

**URL:** <https://discourse.julialang.org/t/sqlite-database-remains-in-use-by-julia-repl/77194>\
**Category:** New to Julia\
**Tags:** sqlite\
**Created:** [February 28, 2022, 3:17pm UTC](https://discourse.julialang.org/t/sqlite-database-remains-in-use-by-julia-repl/77194 "2022-02-28T15:17:23Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Marie](https://avatars.discourse-cdn.com/v4/letter/m/bc8723/32.png) [@Marie](https://discourse.julialang.org/u/Marie)\
**Post date:** [February 28, 2022, 3:17pm UTC](https://discourse.julialang.org/t/sqlite-database-remains-in-use-by-julia-repl/77194/1 "2022-02-28T15:17:24Z")

</div>

I have a small program that creates sqlite database with a single table. Inserts some data into the table.  
In the end of program execution database file remains in use by Julia. I have to kill REPL terminal to release it (previously created db file needs to be deleted in the beginning of execution).  
How do I close that database connection? I’ll appreciate help of the experts - been google searching for days now - no luck!  
function createdb()  
try  
if isfile(“outputs.db”)  
# delete existing outputs.db  
rm(“outputs.db”)  
end  
catch  
println(“Can’t delete”)  
db = SQLite.DB(“outputs.db”)  
SQLite.execute(db, “DROP TABLE data”)  
end  
db = SQLite.DB(“outputs.db”)  
SQLite.execute(db, “CREATE TABLE IF NOT EXISTS data(entity TEXT, date TEXT, varname TEXT, value TEXT)”)  
return db  
end

function writeToDB()  
df = readfilecsv() #populate data frame from csv  
db = createdb()  
for row in 1:size(df, 1)  
entName = df[row,1]  
date = df[row,2]  
varName = df[row,3]  
value = df[row,4]

```
    stmt = SQLite.Stmt(db, "INSERT OR REPLACE INTO data (entity, date, varname, value) VALUES (?, ?, ?, ?)")
    DBInterface.execute(stmt, (entName, date, varName, value))
    #DBInterface.close!(db) - This throws an error that db is null
end

```

end

---

<div class="post-metadata">

**Author:** ![TheCedarPrince](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/thecedarprince/32/17323_2.png) [@TheCedarPrince](https://discourse.julialang.org/u/TheCedarPrince)\
**Post date:** [February 28, 2022, 3:20pm UTC](https://discourse.julialang.org/t/sqlite-database-remains-in-use-by-julia-repl/77194/2 "2022-02-28T15:20:49Z")

</div>

I see you are a first time poster - welcome to the Julia community! 👋

You should close the connection, not the `db` variable.

Also, could you please use code fencing in your post?

Surround your code with “```” to produce code fencing.  
Example:

````julia
```
println("foo")
```

````

which would give

```julia
println("foo")

```

---

<div class="post-metadata">

**Author:** ![oheil](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/oheil/32/220745_2.png) [@oheil](https://discourse.julialang.org/u/oheil)\
**Post date:** [February 28, 2022, 3:27pm UTC](https://discourse.julialang.org/t/sqlite-database-remains-in-use-by-julia-repl/77194/3 "2022-02-28T15:27:59Z")

</div>

Welcome .  
The answer let me wonder so I checked the docs and I got:

> The `SQLite.DB` will be automatically closed/shutdown when it goes out of scope (i.e. the end of the Julia session, end of a function call wherein it was created, etc.)

and

> The `SQLite.Stmt` will be automatically closed/shutdown when it goes out of scope (i.e. the end of the Julia session, end of a function call wherein it was created, etc.), but you can close `DBInterface.close!(stmt)` to explicitly and immediately close the statement.

So you can do:

```julia
DBInterface.close!(stmt)

```

but it seems to be not necessary.

But you actual problem:

> [@Marie](#):
>
> In the end of program execution database file remains in use by Julia. I have to kill REPL terminal to release it

isn’t really addressed because it shouldn’t happen, isn’t it ?

---

<div class="post-metadata">

**Author:** ![Marie](https://avatars.discourse-cdn.com/v4/letter/m/bc8723/32.png) [@Marie](https://discourse.julialang.org/u/Marie)\
**Post date:** [February 28, 2022, 3:49pm UTC](https://discourse.julialang.org/t/sqlite-database-remains-in-use-by-julia-repl/77194/5 "2022-02-28T15:49:40Z")

</div>

Thank you! I’ve tried closing my stmt

```julia
 stmt = SQLite.Stmt(db, "INSERT OR REPLACE INTO data (entity, date, varname, value) VALUES (?, ?, ?, ?)")
        DBInterface.execute(stmt, (entName, date, varName, value))
        DBInterface.close!(stmt)

```

But that doesn’t help. I don’t have a variable representing connection in my code - I operate on that db object: The `SQLite.DB` object represents a single connection to an SQLite database. I can’t figure out why it remains in use after the execution…

---

<div class="post-metadata">

**Author:** ![oheil](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/oheil/32/220745_2.png) [@oheil](https://discourse.julialang.org/u/oheil)\
**Post date:** [February 28, 2022, 4:09pm UTC](https://discourse.julialang.org/t/sqlite-database-remains-in-use-by-julia-repl/77194/6 "2022-02-28T16:09:58Z")

</div>

Did some experiments with it and come to the solution, that it is some kind of a bug, because:

```julia
julia> cd("D:\\Temp")

julia> using SQLite

julia> function createdb()
       db = SQLite.DB("outputs.db")
       SQLite.execute(db, "CREATE TABLE IF NOT EXISTS data(entity TEXT, date TEXT, varname TEXT, value TEXT)")
       end
createdb (generic function with 1 method)

julia> createdb()
101

#can't delete outputs.db now

julia> GC.gc()

# CAN DELETE outputs.db NOW

```

So it needs a garbage collection to _become really out of scope_.

I don’t think this is intended.  
As this is not yet in the issues, it may be important, that I am experienced this on Windows:

```julia
julia> versioninfo()
Julia Version 1.6.2
Commit 1b93d53fc4 (2021-07-14 15:36 UTC)
Platform Info:
  OS: Windows (x86_64-w64-mingw32)
  CPU: AMD Ryzen 9 3900X 12-Core Processor
  WORD_SIZE: 64
  LIBM: libopenlibm
  LLVM: libLLVM-11.0.1 (ORCJIT, znver2)

```

@Marie: do you want to open an issue here: [https://github.com/JuliaDatabases/SQLite.jl/issues](https://github.com/JuliaDatabases/SQLite.jl/issues) ?  
You can link to this discussion.

---

<div class="post-metadata">

**Author:** ![Marie](https://avatars.discourse-cdn.com/v4/letter/m/bc8723/32.png) [@Marie](https://discourse.julialang.org/u/Marie)\
**Post date:** [February 28, 2022, 4:29pm UTC](https://discourse.julialang.org/t/sqlite-database-remains-in-use-by-julia-repl/77194/7 "2022-02-28T16:29:08Z")

</div>

Thanks so much for your help and time!!! Just submitted an issue.  
Currently solved by adding GC.gc() prior to deleting existing db:

```julia
function createdb()
    GC.gc() #current julia bug: explicit garbage collection is needed to release existing outputs.db
      if isfile("outputs.db")
        rm("outputs.db")
      end
.....
end

```