# How to execute .sql file

**URL:** <https://discourse.julialang.org/t/how-to-execute-sql-file/123675>\
**Category:** Data\
**Created:** [December 10, 2024, 6:23pm UTC](https://discourse.julialang.org/t/how-to-execute-sql-file/123675 "2024-12-10T18:23:33Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![slwu89](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/slwu89/32/217323_2.png) [@slwu89](https://discourse.julialang.org/u/slwu89)\
**Post date:** [December 10, 2024, 6:23pm UTC](https://discourse.julialang.org/t/how-to-execute-sql-file/123675/1 "2024-12-10T18:23:33Z")

</div>

Hi, I am just getting started with using databases in Julia and am confused how I could read SQL commands from a file (for example, test.sql as below). I am using `DuckDB.jl` but the question is generic across `SQLite.jl`, etc. Is there some function that does something like the `.read` command of SQLite, for example?

```julia
CREATE TABLE tab1 (
    _id INTEGER PRIMARY KEY,
    col1 TEXT
);

CREATE TABLE tab2 (
    _id INTEGER PRIMARY KEY,
    col1 TEXT
);

```

---

<div class="post-metadata">

**Author:** ![g-gundam](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/g-gundam/32/47593_2.png) [@g-gundam](https://discourse.julialang.org/u/g-gundam)\
**Post date:** [December 10, 2024, 7:47pm UTC](https://discourse.julialang.org/t/how-to-execute-sql-file/123675/2 "2024-12-10T19:47:44Z")

</div>

You can read any text file from disk by doing something like the following.

```julia
sql = read("create.sql", String)

```

You can send that sql string to SQLite just like you would any other SQL query.

```julia
using SQLite
db = SQLite.DB("test.db")
result = DBInterface.execute(db, sql)

```

If you think you’ll be doing this a lot, you could make a function out of it.

```julia
using DataFrames
function read_sql(db::SQLite.DB, filename::AbstractString)
    sql = read(filename, String)
    return DBInterface.execute(db, sql) |> DataFrame
end

```

And you could use it like this:

```julia-repl
julia> df = read_sql(db, "count.sql")
1×1 DataFrame
 Row │ COUNT(*) 
     │ Int64    
─────┼──────────
   1 │ 18657

```

In this case, `count.sql` contained “SELECT COUNT(\*) FROM schedule;” which makes sense in the context of one of my local SQLite databases.

Do you get it?

I haven’t used [DuckDB](https://juliahub.com/ui/Packages/General/DuckDB) before, but you could probably do something very similar for it.

---

<div class="post-metadata">

**Author:** ![slwu89](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/slwu89/32/217323_2.png) [@slwu89](https://discourse.julialang.org/u/slwu89)\
**Post date:** [December 10, 2024, 8:03pm UTC](https://discourse.julialang.org/t/how-to-execute-sql-file/123675/3 "2024-12-10T20:03:05Z")

</div>

Thanks @g-gundam it makes sense in general.

Unfortunately in my specific case, using the small test I provided I got the `ERROR: Invalid Input Error: Cannot prepare multiple statements at once!` error. Do the interfaces for Julia databases have something similar to `executescript` in Python’s SQLite API that allows one to execute all statements in a .sql file?

> **[sqlite3 — DB-API 2.0 interface for SQLite databases](https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.executescript)**
>
> Source code: Lib/sqlite3/ SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process and allows accessing the database using a nonstandard ...

---

<div class="post-metadata">

**Author:** ![g-gundam](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/g-gundam/32/47593_2.png) [@g-gundam](https://discourse.julialang.org/u/g-gundam)\
**Post date:** [December 10, 2024, 8:53pm UTC](https://discourse.julialang.org/t/how-to-execute-sql-file/123675/4 "2024-12-10T20:53:15Z")

</div>

[https://juliadatabases.org/DBInterface.jl/dev/#DBInterface.executemultiple](https://juliadatabases.org/DBInterface.jl/dev/#DBInterface.executemultiple)

There is a DBInterface.executemultiple, but I discovered that it doesn’t work for SQLite. If the sql file contains multiple SQL queries, it just runs the first one. I don’t know about DuckDB. If executemultiple doesn’t work out for you, I would split the `sql` on `;` and loop through it myself.

```julia
function read_sql(db, filename)
    sql = read(filename, String)
    queries = filter(q -> isnothing(match(r"^\s*$", q)), split(sql, ";"; keepempty=false))
    results = []
    for q in queries
        @info q
        res = DBInterface.execute(db, q)
        push!(results, res)
    end
    return results
end

```

That’s all the help I got in me today.

---

<div class="post-metadata">

**Author:** ![stephancb](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/stephancb/32/14243_2.png) [@stephancb](https://discourse.julialang.org/u/stephancb)\
**Post date:** [December 10, 2024, 9:59pm UTC](https://discourse.julialang.org/t/how-to-execute-sql-file/123675/5 "2024-12-10T21:59:27Z")

</div>

You could do

```julia
run(pipeline(`cat statements.sql`, `sqlite3 test.db`))

```

but on Windows it would be `type` instead of `cat`.

---

<div class="post-metadata">

**Author:** ![slwu89](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/slwu89/32/217323_2.png) [@slwu89](https://discourse.julialang.org/u/slwu89)\
**Post date:** [December 10, 2024, 10:34pm UTC](https://discourse.julialang.org/t/how-to-execute-sql-file/123675/6 "2024-12-10T22:34:14Z")

</div>

Thanks all! I tested both SQLite and DuckDB using `execute` and `executemultiple` and they both give the same error.

```julia
using DuckDB
sql = read("./test.sql", String)
db = DuckDB.DB("test.db")
result = DBInterface.executemultiple(con, sql)

using SQLite
sql = read("./test.sql", String)
db = SQLite.DB("test.db")
result = DBInterface.executemultiple(con, sql)

```

I believe that in DuckDB’s case at least, the Python API had this similar issue until this feature was added by PR so I opened a feature request on their GitHub: [Julia API should be able to execute multiple statements in one API call · duckdb/duckdb · Discussion #15264 · GitHub](https://github.com/duckdb/duckdb/discussions/15264)

---

<div class="post-metadata">

**Author:** ![rdavis120](https://avatars.discourse-cdn.com/v4/letter/r/b5a626/32.png) [@rdavis120](https://discourse.julialang.org/u/rdavis120)\
**Post date:** [December 10, 2024, 10:39pm UTC](https://discourse.julialang.org/t/how-to-execute-sql-file/123675/7 "2024-12-10T22:39:07Z")

</div>

This is already implemented in DuckDB.query()

---

<div class="post-metadata">

**Author:** ![slwu89](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/slwu89/32/217323_2.png) [@slwu89](https://discourse.julialang.org/u/slwu89)\
**Post date:** [December 10, 2024, 10:57pm UTC](https://discourse.julialang.org/t/how-to-execute-sql-file/123675/8 "2024-12-10T22:57:24Z")

</div>

Ah, I see. Thanks, I was only looking at the docs for DBInterface.jl

Does this mean there is some shortcoming in `DBInterface.executemultiple`, given it does not execute multiple statements for either SQLite or DuckDB? I’m not very familiar with the Julia Databases ecosystem.

---

<div class="post-metadata">

**Author:** ![Palli](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/palli/32/3380_2.png) [@Palli](https://discourse.julialang.org/u/Palli)\
**Post date:** [December 10, 2024, 11:01pm UTC](https://discourse.julialang.org/t/how-to-execute-sql-file/123675/9 "2024-12-10T23:01:39Z")

</div>

It’s currently not possible for SQLite.jl:

> <https://github.com/JuliaDatabases/SQLite.jl/issues/303#issuecomment-1257102182>
>
> So, I have the following SQL query:
> 
> \`\`\`sql
> DELETE FROM "COHORT"
> WHERE cohor…t\_definition\_id = 1;
> INSERT INTO "COHORT"
> SELECT
> 1 AS "cohort\_definition\_id",
> "drug\_era\_7"."subject\_id",
> "drug\_era\_7"."cohort\_start\_date",
> "drug\_era\_7"."cohort\_end\_date"
> FROM (
> SELECT
> "drug\_era\_6"."person\_id" AS "subject\_id",
> MIN("drug\_era\_6"."start\_date") AS "cohort\_start\_date",
> MAX("drug\_era\_6"."end\_date") AS "cohort\_end\_date"
> FROM (
> SELECT
> "drug\_era\_5"."person\_id",
> (SUM("drug\_era\_5"."bump") OVER (PARTITION BY "drug\_era\_5"."person\_id" ORDER BY "drug\_era\_5"."start\_date", (- "drug\_era\_5"."bump") ROWS UNBOUNDED PRECEDING)) AS "group",
> "drug\_era\_5"."start\_date",
> "drug\_era\_5"."end\_date"
> FROM (
> SELECT
> "drug\_era\_4"."person\_id",
> (CASE WHEN ("drug\_era\_4"."start\_date" \<= (MAX("drug\_era\_4"."op\_end\_date") OVER (PARTITION BY "drug\_era\_4"."person\_id" ORDER BY "drug\_era\_4"."start\_date" ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING))) THEN 0 ELSE 1 END) AS "bump",
> "drug\_era\_4"."start\_date",
> "drug\_era\_4"."op\_end\_date" AS "end\_date"
> FROM (
> SELECT
> "drug\_era\_3"."person\_id",
> "drug\_era\_3"."start\_date",
> "drug\_era\_3"."op\_end\_date",
> (ROW\_NUMBER() OVER (PARTITION BY "drug\_era\_3"."person\_id" ORDER BY "drug\_era\_3"."start\_date")) AS "row\_number"
> FROM (
> SELECT
> "drug\_era\_2"."person\_id",
> "drug\_era\_2"."start\_date",
> "op\_1"."end\_date" AS "op\_end\_date",
> (ROW\_NUMBER() OVER (PARTITION BY "drug\_era\_2"."person\_id" ORDER BY "drug\_era\_2"."sort\_date")) AS "row\_number"
> FROM (
> SELECT
> "drug\_era\_1"."person\_id",
> "drug\_era\_1"."drug\_era\_start\_date" AS "start\_date",
> "drug\_era\_1"."drug\_era\_start\_date" AS "sort\_date"
> FROM ""."drug\_era" AS "drug\_era\_1"
> WHERE ("drug\_era\_1"."drug\_concept\_id" IN (
> SELECT "concept\_1"."concept\_id"
> FROM ""."concept" AS "concept\_1"
> WHERE ("concept\_1"."concept\_id" = 1118084)
> ))
> ) AS "drug\_era\_2"
> JOIN (
> SELECT
> "observation\_period\_1"."person\_id",
> "observation\_period\_1"."observation\_period\_end\_date" AS "end\_date",
> "observation\_period\_1"."observation\_period\_start\_date" AS "start\_date"
> FROM ""."observation\_period" AS "observation\_period\_1"
> ) AS "op\_1" ON ("drug\_era\_2"."person\_id" = "op\_1"."person\_id")
> WHERE
> ("op\_1"."start\_date" \<= "drug\_era\_2"."start\_date") AND
> ("drug\_era\_2"."start\_date" \<= "op\_1"."end\_date")
> ) AS "drug\_era\_3"
> WHERE ("drug\_era\_3"."row\_number" = 1)
> ) AS "drug\_era\_4"
> WHERE ("drug\_era\_4"."row\_number" = 1)
> ) AS "drug\_era\_5"
> ) AS "drug\_era\_6"
> GROUP BY
> "drug\_era\_6"."person\_id",
> "drug\_era\_6"."group"
> ) AS "drug\_era\_7";
> \`\`\`
> 
> I have a \`SQLite.DB\` set up and try to run this SQL as follows:
> 
> \`\`\`
> DBInterface.execute(db, my\_sql) # Does not work
> SQLite.execute(db, my\_sql) # Does not work
> \`\`\`
> 
> However, when I run this exact same SQL within the tool, \`litecli\`, it works as expected in deleting and creating rows. What is going on here? 
> 
> Thanks!
> 
> ~ tcp :deciduous\_tree:

> A workaround is to execute single statement sequentially.  
> We can also implement DBInterface.executemultiple correctly with the method described in [SQLite forum](https://sqlite.org/forum/forumpost/f47ae41b4d336239) which is similar with [executescript](https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.executescript) in Python.  
> All C API functions in SQLite have been exposed now so you can propose a PR if you are interested in it.

It seems rather simple to implement, not done directly by SQLite for good reasons, and I think I located how here the loop that must be implemented (note the `tail` part, in other contexts I see `NULL` there):

> <https://github.com/python/cpython/blob/51216857ca8283f5b41c8cf9874238da56da4968/Modules/_sqlite/cursor.c#L1055-L1062>

> **[C/C++ Interface For SQLite Version 3](https://www.sqlite.org/capi3ref.html#sqlite3_prepare)**

> The preferred routine to use is sqlite3\_prepare\_v2(). The sqlite3\_prepare() interface is legacy and should be avoided. sqlite3\_prepare\_v3() has an extra “prepFlags” option that is used for special purposes.

> [@slwu89](#):
>
> using the small test I provided I got the `ERROR: Invalid Input Error: Cannot prepare multiple statements at once!` error. Do the interfaces for Julia databases have something similar to `executescript` in Python’s SQLite API

As you noticed, it’s by design in databases to only run one query (at a time), e.g. in general you want to retrieve the query results, and it can also be a security risk to allow more than one (if you think you have and someone SQL injected more).

That said, there are tools for e.g. bulk loading data for databases like for PostgreSQL, and I guess SQLite too, just not too familiar with it.

A workaround is using the shell, and the tool for this, as I see already suggest here, or using a different database where `executemultiple` API call is implemented, or implement it for SQLite.jl.

Another workaround is you can use some API in another language (if you like it more than from the shell, possibly easier for cross-platform code), such as in Python, with PythonCall.jl, since I know it implemented there, or I suppose some Rust API has this.

---

<div class="post-metadata">

**Author:** ![stephancb](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/stephancb/32/14243_2.png) [@stephancb](https://discourse.julialang.org/u/stephancb)\
**Post date:** [December 11, 2024, 6:03am UTC](https://discourse.julialang.org/t/how-to-execute-sql-file/123675/10 "2024-12-11T06:03:48Z")

</div>

Right. Typically something gets inserted into a database with such SQL scripts which are out in the wild. In database jargon a “query” includes `INSERT` statements, though nothing gets “queried”.

An `executemultiple` would not be difficult to implement, but not entirely so. Quoted semicolons and new lines inside strings can occur, error codes need to be checked. Therefore I would recommend to just pipe a mulit-statement script into the `sqlite3` CLI.

---

<div class="post-metadata">

**Author:** ![Palli](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/palli/32/3380_2.png) [@Palli](https://discourse.julialang.org/u/Palli)\
**Post date:** [December 11, 2024, 2:55pm UTC](https://discourse.julialang.org/t/how-to-execute-sql-file/123675/11 "2024-12-11T14:55:41Z")

</div>

> [@stephancb](#):
>
> An `executemultiple` would not be difficult to implement, but not entirely so.

No, you’re thinking of parsing, that would be nontrivial yes, but if you look at the loop in Python I pointed to, you see you don’t need to find the end of each statement, `sqlite3_prepare_v2` does it for you and gives you a pointert to the next statement with `tail`, so it’s a rather trivial loop.

> [@stephancb](#):
>
> In database jargon a “query” includes `INSERT` statements, though nothing gets “queried”.

Right, that’s why SQL in not (just) a query language, despite the Q meaning that… Actually it’s neither structured [programming or procedural language, as opposed to “structured English”], query, or a language…

That said even inserts can return values:

[https://www.sqlite.org/lang\_returning.html](https://www.sqlite.org/lang_returning.html)

> SQLite’s syntax for RETURNING is modelled after [PostgreSQL](https://www.postgresql.org).

I.e. I’m not sure this is in the SQL standard, seemingly not, but a useful extension:

> **[6.4. Returning Data from Modified Rows](https://www.postgresql.org/docs/current/dml-returning.html)**
>
> 6.4. Returning Data from Modified Rows # Sometimes it is useful to obtain data from modified rows while they are being …

> Use of `RETURNING` avoids performing an extra database query to collect the data, and is especially valuable when it would otherwise be difficult to identify the modified rows reliably.

> <https://stackoverflow.com/questions/7917695/sql-server-return-value-after-insert>

Off topic:  
SQL is not a language, as in it has many variant languages/proprietary extensions (and the official SQL has many official extensions: SQL-86 … SQL-92 … [SQL:2023](https://en.wikipedia.org/wiki/SQL:2023)) and:

> <https://softwareengineering.stackexchange.com/questions/334289/why-is-sql-the-only-database-query-language>

> please also recognize that SQL is not a language like you would think of an object-oriented language or procedural language. In many ways the ANSI SQL standard is more like a protocol

> **[Is it true that SQL does not stand for Structured Query Language?](https://www.quora.com/Is-it-true-that-SQL-does-not-stand-for-Structured-Query-Language)**
>
> Answer (1 of 3): Yes, it is true that SQL does not stand for Structured Query Language. SQL was originally developed by IBM in the 1970s. It originated before the concept of a structured language was developed as a defense against the horrors of...

> Yes, it is true that SQL does not stand for Structured Query Language. SQL was originally developed by IBM in the 1970s. It originated before the concept of a structured language was developed as a defense against the horrors of “spaghetti code.” Unstructured languages allowed you to jump from one place in a program to another, usually with a GO TO command. SQL, originally named SEQUEL contained that jumping capability and still does to this day with:
> 
> WHENEVER GO TO ;
> 
> SEQUEL was an acronym for Structured English QUEry Language. This clever name was used because SEQUEL statements were very much like English-language sentences, but were more highly structured. It was structured English. SEQUEL was also a query language. However, it was not a structured query language. IT was not a structured language of any kind. It was and it remains an unstructured query language. The idea that SQL is an acronym for Structured Query Language was retconned onto the language by people who did not know the history and who made the assumption that structured query language is what the letters SQL must stand for. While in the SEQUEL acronym S stood for Structured, Q stood for Query, and L stood for Language, that is not what those letters stand for in SQL. In fact they don’t stand for anything, just like C does not stand for anything in the C language.