Until now SQLite3 does not support Stored Procedures (at least as far as I am aware)

Are there any plans to introduce also Stored Procedures into SQLite3 ?

Since many more than 10 years I am using therefore my own extension functions to mimic such a feature. I named my function "call" and its first parameter is the name of the procedure and the other parameters are the inputs to that function.

The call function inserts the parameters into a table with name "stack", which fires a trigger which is watching the name, the first parameter of my call.

The trigger itself can work with all the parameters from the stack record, a helper extension function supports this task.

The trigger itself calls a function return, which can be filled into the stack record but more important becomes the return value of the "call" extended function call and therefore is then the reply into the original sql statement, which caused the trigger to fire.

Because all this happens in scope of the same db context the stack leaves only a trace if there was no delete all from stack happening. I am controlling this for debug purposes, too.

Here is an example for the recursive form of the faculty for number 6 ( = 1 * 2 * 3 * 4 * 5 * 6 = 720 ) , 7! = 5040 , 8! = 40320

D:\SQLite3x\sandbox>sqlite3 LoadExTest.SQB
SQLite version 3.35.5 2021-04-19 18:32:05
Enter ".help" for usage hints.
sqlite> .load sqlite3.dll
sqlite> .mode box
sqlite> select call('Fakt',6),call('Fakt',7),call('Fakt',8);
┌────────────────┬────────────────┬────────────────┐
│ call('Fakt',6) │ call('Fakt',7) │ call('Fakt',8) │
├────────────────┼────────────────┼────────────────┤
│ 720            │ 5040           │ 40320          │
└────────────────┴────────────────┴────────────────┘
sqlite>

The stored function itself and the stack table looks like the following:

• Debug() sends output to Windows function OutputDebugString
• argc() returns the count if non-NULL parameters
• argv() returns one parameter from the recent stack table record
• JobLevel() return the recursive depth of calls from the level column
• result() fills the column result and will be the return value for call

CREATE TABLE stack( job text, -- the function name which will fire the trigger
   level integer, inserted float, returned float,        -- some debug support
   result blob,
   par0 blob, par1 blob, par2 blob, par3 blob, par4 blob,
   par5 blob, par6 blob, par7 blob, par8 blob, par9 blob );

CREATE TRIGGER Fakt after UPDATE OF job ON stack
FOR EACH ROW WHEN new.job=='Fakt' and argc()>=1
BEGIN
  select Debug(concat('JobLevel()=',JobLevel(),': Fakt(',argv(0),')=',
    result( argv(0) * case
      when argv(0)<2 then 1
      else call(new.job,argv(0)-1)
    end   )   )      );
END;

Stored procedures aren't really needed for Sqlite, since the program you write around Sqlite can certainly implement any 'stored' procedures itself.

But beyond that you can certainly implement user functions which provide stored procedure results; but that's not a thing other databases can do.

Not only that, but Stored procedures would require an additional language implementation to handle variable declaration, assignment and expressions... there's no standard when it comes to writing scripts to be executed by a SQL engine; other than the SQL commends themselves.

Stored procedures aren't really needed for Sqlite, since the program you write around Sqlite can certainly implement any 'stored' procedures itself.

This argument applies equally well to all database engines.

There are several advantages to using stored procedures. (I won't list them, Google them). From those, only the client-server over network speedup is irrelevant to SQLite3.

I believe the number of people (including programmers) who use SQLite3 as a stand-alone database tool is vastly underestimated.

SQLite3 has become very powerful over the years due to its relative simplicity, single-file database, and immediacy. Many who would otherwise use a client/server database now find it more than enough to use SQLite3 as it does almost everything the bigger ones do with far less overhead and practically zero setup effort.

Stored procedures would make their life quite a bit easier in many circumstances. A minimal implementation of stored procedures would be a welcome addition to what is already almost a full-fledged database engine.

By 'minimal' I mean at least:

1. Be able to group several statements together
2. Allow parameters when calling the proc.
3. A simple method to rollback and exit after each statement if there are errors or (by default) continue to the next statement.

That's all. No variables, or other perhaps complicated programming structures found in larger database engines.

[1] is already implemented in triggers.

[2] is already setup in the CLI with the .parameter command. It needs to become part of the core using the PROC(PARM1,PARM2,...) syntax.

It is also very handy for having to maintain it at one place. When several programs do at some part the same it would be better to maintain stored procedures as to have to maintain al the programs.

Yeah, but you can encapsulate business logic in a re-usable library as well. True though that in the database then it's client language independent. Coding DDL to handle business logic is generally a horrendous experience though and I avoid them like the plague. Really, all business should go in more expressive and composable languages where decent coding patterns can be applied. My take on having stored procs in SQLite is it's advantageous for wrapping smaller units that could gain a serious performance benefit or provide a consistent way of pulling data back in special circumstances but then Views provide that already.

I also like to have stored procedures, but I also think that the existing capabilities of views and triggers are good enough for many purposes, so we don't need an actual stored procedure capability. (And often you can use extensions if the existing capabilities are not good enough, although there are some things that are not currently a capability of SQLite even with extensions; this could perhaps be improved.)

I'll give you just one scenario to help you see that views and triggers are not always enough. Consider stand-alone use to clear your mind from thoughts like "the application will do that for you".

A very simple example; two tables: people and phones. (A person may have multiple phone numbers, hence the separate table.)

You want to insert a new person.

INSERT INTO people(pid,name,address) values(1,'First Last','Home');
INSERT INTO phones(pid,phone) values(1,'555-1212');

Each time you want to add a person you have to issue these two statements. You could make a script, and edit the data before each run.

Or, play civilized and do something like:

add_person(1,'First Last','Home','555-1212');

Easy enough to do with a sqlite library..

import {sack} from "sack.vfs"
const db = sack.Sqlite( "database.db" );

db.function( "add_person", addPerson );
const success = db.do( "select add_person(?,?,?,?) f", 1,'first last','home','555-1212' ) ;
if( !success[0].f ) {
   // failed to insert somehow...
}


function addPerson(pid,name,address,phone)=>{
   try {
      db.do( "insert into people(pid,name,address) values(?,?,?)", pid,name,address));
      db.do( "INSERT INTO phones(pid,phone) values(?,?)", pid,phone ) ;
      return 1;
   } catch(err) {
      return 0;
   }
}

though - that just ends up as part of the library of the app...

"though - that just ends up as part of the library of the app..."

Exactly, if it was, on the other hand, part of the database itself, it makes it a lot easier to use that database to back multiple applications, written potentially in different languages.

In fact, accessing SQLit from multiple, polyglot applications would be much easier if:

1. There was a pseudo language with which we can define: 1.1. Custom SQL functions 1.2. Stored procedures
2. There was a way to (optionally) provide a default connection configuration file, which can be overriden on a per connection basis, but they all pick up the default first if it exists. Maybe even an option to make the defaults non-modifiable, as part of the defense against dark arts when the db can be accessed by potentially rogue processes.

Any support for stored procedures (as in MS SQL Server) does not stop with the provision of facilities for storing SQL code by name and then executing it by name.

At the very least, you'd need the ability to edit the stored procedures and, ideally, run the procedures according to a chronological schedule and the it would follow that you would want to know the history (timestamp and outcome) of previous invocations.

Not so simple to provide support for stored procedures.

" run the procedures according to a chronological schedule and the it would follow that you would want to know the history (timestamp and outcome) of previous invocations."

that is not needed for simple stored procedure execution - MS SQL Server doesn't even provide that out of the box, so is a separate discussion.

1. have executable stored procuedures
2. if we want, have a logging and scheduling engine around them.

I'll give you just one scenario to help you see that views and triggers are not always enough.

How are views and triggers not enough for this?

SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table people (pid, name, address);

sqlite> create table phones (pid, phone);

sqlite> create view add_person as select pid, name, address, phone from people inner join phones using (pid);

sqlite> create trigger add_person_trg instead of insert on add_person begin insert into people values (new.pid, new.name, new.address); insert into phones values (new.pid, new.phone); end;

sqlite> insert into add_person values (1, 'First Last', 'Home', '555-1212');

sqlite> select * from people;
pid|name|address
1|First Last|Home

sqlite> select * from phones;
pid|phone
1|555-1212

sqlite>

Right. It was an unfortunate example.

What if I had a combination of insert, update, and select?

Can you still get by with just views and triggers? (I think even a single select in the trigger would not work.)

You would have separate INSTEAD OF triggers on

• INSERT
• UPDATE
• DELETE

Any trigger can do INSERT, UPDATE or DELETE on any table in the statements between BEGIN and END.

Triggers can do SELECTs where needed to make decisions of what values to use for INSERT, UPDATE or DELETE.

You can even use multiple INSERT/UPDATE/DELETE triggers with different WHEN clauses to discriminate between cases.

I would implement a "SELECT in a trigger" as a view. No need for a trigger.

With a proc you can do INSERT/DELETE/UPDATE/SELECT in any conceivable order to get the desired action and/or result. You can't do that with triggers. And even if you manage to figure out a way to do so for some specific case, in general it won't be possible or straight forward/readable.

Why pollute your schema with a whole bunch of funny named view-trigger combinations so you can remember which one to use when a simple concise proc can do the same.

Apparently, it's hard to reach a consensus that stored procedures are a good thing.

I guess there will always be two kinds of people; those who like simplicity and beauty, and those who enjoy programming as if in brainf@ck language :)

I've been gnawing on a similar problem for the past 3 days, and this solves it beautifully.

Great post, thank you.

You could do this easily with a INSTEAD OF trigger on a view, which will execute both INSERT statements in the trigger program. And then, you could write: INSERT INTO add_person VALUES(1,'First Last','Home','555-1212');

Stored procedures aren't really needed for Sqlite, since the program you write around Sqlite can certainly implement any 'stored' procedures itself.

True. However in a multi-language environment (say Python, C, PHP) that would mean implementing all 'stored' procedures multiple times. And of course it will not be a question IF these implementations will be out of sync one day but WHEN - with potentially fatal results for your data.

The same goes for Triggers - easy to implement in application code. And who needs things like Foreign Keys - it is not as if that could not be implemented in an application. (That was sarcasm - sorry).

There is a lot of functionality in SQLite that not everyone needs or uses. A lot of people will never use FTS (I do), others will never need Geopoly (I don't).

The same goes for Triggers - easy to implement in application code. And who needs things like Foreign Keys - it is not as if that could not be implemented in an application. (That was sarcasm - sorry).

Sarcasm? really? we couldn't tell. The problem here is your sarcasm proves the opposite point than which you are trying to make. Triggers in program code is impossible (rephrasing - exquisitely difficult). So is foreign key maintenance. By contrast, scripts inside Triggers and UDF's are both very easy to add and any other processing in your code is as easy as how good a programmer you are.

There is a lot of functionality in SQLite that not everyone needs or uses. A lot of people will never use FTS (I do), others will never need Geopoly (I don't).

The truth is that very many people use FTS and so it is included in the standard builds (but you can exclude it), as opposed to Geopoly that has to be included if you wish to use it, precisely because not many implementations use it. Both of these however are based on the virtual table mechanism (always included) and infinitely less complex than would be a scripting language for stored procedures.

Furthermore, such an inclusion as stored procedures wouldn't be able to be an add-on or loadable library, it would have to form intricate part of the main engine code and cause a lot of bloat and added processing, something punishing all of us for a thing which very few of us have real use for in SQLite.

I'll say this: There are a couple of requests like this almost every year, so you are not alone, but even tens of requests per year likely won't move the needle.

I'm not saying it won't be "nice" to have, just saying the gain-vs-input efficiency ratio is way too low currently. Don't lose hope though, we've seen lots of other claimed-to-be-bloaty additions making it into SQLite over time, such as Backup tooling, CTE's, Window functions, and the very recent RETURNING clause. That said, they all either had been requested much more (possibly monetary requests too), or took much less complexity/effort to add.

You have replied to a message which was written almost three years ago.

Are you trolling, or what?

Nope, just asking a question given CTE's were 2005+ and this topic appeared recently.

CTEs were early 2014 (3.8.3 to be specific). They're not exactly new, but in the grand scheme of things, they're not exactly old either.

2005 was stuff like ALTER TABLE...ADD COLUMN, CAST, and DISTINCT in aggregates.

I was talking about CTE's in the wild. ~9 years to implement in SQLite.

True. However in a multi-language environment (say Python, C, PHP) that would mean implementing all 'stored' procedures multiple times.

Write your "stored procedures" once in C and call them from python et al?

Maybe I'm missing something, but for functions like factorial couldn't you just make an extension? It's simple to load it on each connection, or even just compile it in to sqlite if you don't want to load extensions. It would even have the benefit of better performance.

Also, as pointed out by someone else, creativity with views and triggers can yield some good results.

My example of UDF (user defined function) was just to show how I mimic stored procedures. My implementation is more like an UDF than stored procedure or function because I still need to use the select statement or an other expression consumer. However, what was important for me -to put this effort into such a small set of extension functions- was that big portions of logic were required to be handled. Let's say: My application get data from various sources (text formatted and blobs), inserted into one table and has to schedule different actions based on them. My application has to run uninterrupted for years. By putting the logic into the database in form of triggers enabled to do that without interrupting the operation; enabled also to improve the logic for some data formats while the scheduler for the others were not affected. "SQL injection as a feature." (I am using a controlling input pipe for this).

It is possible to load extension .so/.dll files - but how can they be unloaded or updated/replaced on a permanently in-use db connection? This is OS dependent if possible at all, I guess. With triggers this is very easy, you can simply drop them and create a new one - even both within one transaction, which makes sure that the rest does not get disturbed and even the next message can use the updated logic without getting lost.

For me the main benefit for UDF or Stored Procedure is the parameterization. If a view could be called with parameters, that would be almost what was in my mind from the beginning.

My implementation cannot generate table results, which a stored procedure (called with parameters) might be able to return. That's the main difference and where I see the benefit.

Yes, I also wrote some virtual tables e.g. to read a directory listing or a list of lines from all files which are selected by another SQL statement or simply to use DBase/FoxPro files from within SQLite (long time ago now and not used anymore since about 2010); but this was long before the value enabled virtual tables became invented. I may have to step into this subject myself once more to see if and how our wishes for parameterized functionality can be accomplished with that capabilities. Unfortunately I am very busy and short in time. But thanks for this good hint.

For me the main benefit for UDF or Stored Procedure is the parameterization. If a view could be called with parameters, that would be almost what was in my mind from the beginning.

Actually, this I agree with, and is something I had wanted too. I would suggest extending the syntax for CREATE VIEW so that the list of column names can include type names and DEFAULT (and maybe also COLLATE); if the type name includes HIDDEN then it is a hidden column which can be constrained by table valued function syntax like virtual tables can.

However, it is then seems not possible to copy the value that is constrained, unless a virtual table extension is used to do that (and I don't know if that will work in a recursive CTE). Host parameter syntax could be another way, maybe.

My implementation cannot generate table results, which a stored procedure (called with parameters) might be able to return. That's the main difference and where I see the benefit.

I believe you that the ability to generate table results can be useful. There is RETURNING, although it is limited:

• A view/triggers cannot specify what values are returned by RETURNING.

• You cannot use RETURNING anywhere that SELECT is allowed.

https://github.com/0x09/sqlite-statement-vtab/ gives you essentially parameterized VIEWs (but with an optimization boundary).

Thanks for the hint .. this is really working great!

I also found another virtual table, which I often were working around for, which is the https://github.com/jakethaw/pivot_vtab to transpose a long table into a wide table. Unfortunately that implementation seems to work only with a single columns solution; there is some space for improvements...

That looks like a very nice module: since it glues together existing features to give something very powerful - that is - create table-valued functions using only SQL.

Is there somewhere we can vote to have this included as a 'standard' module?

Does this help? https://cgsql.dev/docs/introduction

CG/SQL is a code generation system for the popular SQLite library that allows developers to write stored procedures in a variant of Transact-SQL (T-SQL) and compile them into C code that uses SQLite’s C API to do the coded operations.

A problem with this is that unlike standard SQL you cannot read the SQL code at run time, it seems like to me.

I am quiet happy with my own way of implementation as a user defined function, which works like a wrapper around a table as "call stack" and using normal trigger mechanisms. It would be nice to have a "standard language frame" for this but it is unlikely to come with SQLite in standard.

What I was missing I found in that value enabled virtual table "statement". This works nice and offers probably even more opportunities than what it does now for me.

I also implemented Lua into my list of extensions and with few extension functions I am able to use that for whatever would be difficult to code in SQL otherwise. My applications became script enabled this way.

The code generator could generate a static character string for the C code which contains the text of the SQL. Easy - peasy!

You can, but what I mean is that, the SQL code has to be available at compile time. You cannot accept CG/SQL code at run time (that was not compiled into the program) and execute it.

A compiled program is a stored procedure. In fact, that is precisely the purpose of a "stored procedure" in "far away high latency" client/server RDBMS systems. The procedure is precompiled and "stored" on the server so that the latency of a myriad of line turnarounds are avoided.

Since SQLite3 sufferes no such problem because the database is "in process", the "stored procedure" is called a "subroutine" and is part and parcel of the application itself. There is no need whatever for a client/server type "stored procedure" because the issues that those stored procedures were designed to address do not exist.

For a given database file, if there is only ever a single application that reads and writes that file, then you may be correct.

You may want to consider a broader set of use-cases.

What use cases would there be outside of what can be done in a trigger program? Being a stored procedure wouldn't necessarily grant extra capabilities (and SQLite's not about to grow a permission system; that's an explicit non-goal). Functions whose definition lies outside SQLite are, by definition, not something that SQLite itself can define; the environment embedding the library has to do so, and those environments can have considerable extra capabilities and don't really need stored procedures to do so.

OK, I thought of a possible scenario that makes sense.

• You could define a complex query and give it a name so that, when you have two programming language environments that use a single DB then they'd share a single definition of that query.

The larger scenario I have that could perhaps make use of that is that I use a DB to store a description of the simulation task I'm carrying out (the description is a complex graph with mappings between subgraphs) and I need to convey that description from the Python code that authors it to other programs (such as parallel data transfer helpers and visualisers) that can be written in other languages such as Java or C++. Some of the queries needed are moderately complex, and can't be easily converted into views as far as I can see due to the parameterisation required. Being able to name those queries in common would mean that I don't have to worry about synchronizing the exact definitions of multi-page queries across several apps in several programming languages that don't really care about the internal mechanics of the query but just what parameters to pass and what rows are produced.

It's not just for queries. You can standardize a whole set of data-object operations that way. The procedure interfaces can remain steady even as the schema underneath it evolves over time.

So a Virtual Table. You can already do that.

How would that work in Fellow's "larger scenario", where not all the applications are written in the same language?

What does that have to do with anything? The whole point of a virtual table is that once it is written you "use it" via standard sql as if it were an ordinary table.

The fts virtual tables are written in C. What difference does that make to applications written in the Kiddie Language of the Day (or even the various Adult use languages)?

...applications written in the Kiddie Language of the Day...

What are some of the Kiddie Languages of the Day today?

PHP, Python, Lua, JavaScript, Go, Rust, Kotlin, <anything newer teen age>.

I'll look at your Virtual Table idea more closely, and get back to you.

Is there something like that that will work at run time (parsing the procedural SQL code at run time, and then preparing a procedural SQL object which can be executed later) instead of at compile time?

Not sure if this thread is still open - I hope below can contribute …

Instead of taking the large effort to agree on grammar for variables, loops, etc, why not take an approach similar to postgresql - which allows using existing languages to extend SQLite. This will build on the SQLite strong foundation.

Natural candidates are LUA, JavaScript, given that there are already easy to integrate libraries:

• LUA has been used to extend redis, and has integration with SQLite.
• JavaScript does not need to be introduced

Given both languages can register sql user defined function, the remaining part is to be able to create a syntax wrapper for calling a stored proc, which is practically the same as the proposed “select”, with the minor:
Call function-name(…) which will be equivalent to “select function-name(…)”, and will ignore the result.

The create procedure will maintain the stored procs in a table that track defined procedures: Create table _stored_proc(name varchar, engine varchar , body text).

Of course, one day, someone might contribute an engine that can process Postgresql pgsql, or microsoft TSQL …

Nope. The most natural candidate is Tcl, in which tests to SQLite are written, and DB extension to which SQLite itself was at the beginning. Both Lua and JavaScript are at least external to SQLite in sense of license/maintainers/etc, not to to mention technical problems like garbage collection.

Or TH1 subset of Tcl, as mentioned above in thread.

I know of people who keep Tcl code in their SQLite database. Keeping code that becomes the definition of SQLite functions seems like a natural extension... but something that really should be very application-specific; other use cases will really not want it! (Most stored procedures I've seen have always struck me as being likely to be vulnerability vectors, though admittedly they were shown to me exactly because this was the case so I don't know if the sample is biased. The point still stands though: can't have problems with that if the capability is entirely absent.)

I just build the functions I need as part of connection setup.

All of this becomes messier if you want to use the functions as part of, say, trigger definitions (instead of being directly in queries), since then you might want them to exist even when the database is loaded by a completely different language. That strikes me as being a really good reason for Not Doing That.

Here's a different take. SQLite knows that there is an embedding language/application, be it the simple sqlite3 shell or a complex application server. It assumes that some things are going to be done by the outside environment for it, such as managing periodic maintenance tasks. Stored procedures straddle that boundary. (Periodic maintenance tasks have to be outside as, unlike with a standard database server, the SQLite library cannot know whether it will be executing at the time that the maintenance task comes due; the application it is embedded within can know that.)

All of this becomes messier if you want to use the functions as part of, say, trigger definitions (instead of being directly in queries), since then you might want them to exist even when the database is loaded by a completely different language

That is, and such need arose in parallel thread about conditions/variables in triggers (based on article which shows such triggers in Postgres).

Perhaps even a TH1 is not needed, and subset of if conditions and variables for triggers could be compiled directly into SQLite VM bytecode?.. As real need for most cases would be just calling SQL again, which - if from application function - is kinda recursion.

Most stored procedures I've seen have always struck me as being likely to be vulnerability vectors, though admittedly they were shown to me exactly because this was the case so I don't know if the sample is biased. The point still stands though: can't have problems with that if the capability is entirely absent.

Then +1 to SQLite VM bytecode and restricting even TH1 subset.

However, your point is applicable to any language stored procedures, right? As if we import any application function to SQLite, it becomes "part" of SQL here, and subject to more or less same possibilities of vulnerability vectors, depending on what care author if function has taken. Thus, we can say "can't have problems with that if the capability is entirely absent" only if any application functions prohibited at all.

However, your point is applicable to any language stored procedures, right?

Yes. Storing code (however defined) in the DB is a risk. But then so too is storing it on the filesystem. If the code is outside the DB, it's at least obviously not the DB's job to worry about it.

I suppose a compromise might be to support the syntax for calling stored procedures, but having those actually just be calling functions (however defined) from inside a trivial select without a current "real" table. The function will be able to do whatever it wants, and any changes it does to the DB will be defined (assuming they're well-formed in the first place, etc.) It'd be a punt, but it would be a punt that would work. (I guess we could also have a flag for functions that marks them as only being callable this way; that wouldn't be a lot of extra hair to add — really just an extension of what's already there — and that makes it easier to be sure it is right. Whether those function definitions were stored in the DB or not would then become moot; you wouldn't be calling them from tricky places in the first place.)

I'm spitballing here, of course.

Stored Procedures are now available for SQLite as a separate project:

https://github.com/aergoio/sqlite-stored-procedures

Use cases are mainly for replicated SQLite, and possibly with Node.js' node-sqlite3 package, that does not have proper support for transactions (for those who cannot use the better-sqlite3 package)

Is there any possible way of executing procedures in SQLite in C#