Click here to Skip to main content
13,259,194 members (52,607 online)
Click here to Skip to main content
Add your own
alternative version


126 bookmarked
Posted 24 Dec 2007

A SQLite Server/Client Implementation

, 23 Jan 2008
Rate this:
Please Sign up or sign in to vote.
Implementing server/client architecture for the great SQLite!

Introduction / Background

Recently, I had to choose SQL Server for a big project I'm working on, and to be honest - I did not like my options. First, there were all those "x vs. y" performance tests on the web, which did not give very good grades for MS-SQL Server (not to mention the costs, or weight of that hog), and then there are also MySQL, PostGRE, and others, but none of them satisfied me. What I wanted was something that allows me to choose exactly what I want from my server, to use a database engine as good as the SQLite... and at no cost.

Which means - I have to write one...

I started out by searching the web, and there was only one serious result, which was Alex K.'s project, from 2004 I think (his project can be found here).

I got his permission to use his code (actually, I asked him right before posting this article...), and I have adopted some of his code, and wrote a lot of new code. Some of the function names may sound familiar for those who were ever introduced to his code.

Anyway, his code has four main problems that I did not like:

  1. He was passing handles (!) over network.
  2. He was compiling statements and stepping each time.
  3. He was actually wrapping SQLite 2 and not SQlite 3...
  4. VC2005 found leaks in the threads!!!

The problem with no. 1 is that it is pretty clear that the communication between the client and the server must be transparent, no handles and such. The problem with no. 2 is that it generates too much overhead. You should execute one SQL query, get the results at once, and that's it! (Anyway, that is how I see it...) The problem with no. 3 is that most people, most importantly me, like to update their SQLite engine when an update comes out and says in big letters "A minor bug was fixed". The problem with no. 4 is eh... Well, I dunno... Do you like memory leaks?

Basically, what I did is this:

I took the sockets handling function, tweaked it a bit, and replaced gethostbyname with getaddrinfo so it can support IPv6. IPv4 is pretty much deprecated. I rewrote the thread handling classes, the same architecture, some differences here and there, but I just had to be sure there are no memory leaks. The stack classes are almost unharmed; the main thing I changed for them is the naming... I also rewrote the handler class, with the same basic architecture and even function names. Also, added support for SQL execution and SELECT queries! :-)

On the client side, I added an interface, which I took completely from my SQLite wrapper classes, so you do not have to parse the tables' results and convert from UTF8... It does it all for you. One more important thing - I have added support for user authentication on the server.

Well, I'm not really going to tell you each and every word of code I wrote there, so you will just have to download and see for yourself. I'm also not planning on writing any kind of documentation here! I do not have the time for it. Besides, the code is pretty much self-explanatory.

Note: If you are planning on compiling for Win2K/Me/98/95, you will have to include wspiapi.h because of my use of getaddrinfo. Seems like before WinXP, IPv6 was not yet the standard as stated in MSDN.

P.S. - If anyone is serious enough and wants to take this SQLite server/client thing forward, be my guest. It will be nice to post on SourceForge or something like that, keep track of things, documentation, etc... I just do not have the time for it...

Using the code

While writing the libraries, I had to write a demo application to test them... so they are attached in the same solution. The solution basically includes: server classes, client classes, server app, and a client test app. If you want to use the code, just include the relevant library. Also, to compile the server library, you have to include the SQLite3 library/DLL/sources, which can be obtained from here. (The sqlite3.h file is expected at the subfolder ./SQLite/.)

Update 21/01/2008: Now the ZIP file comes with SQLite3 sources as well, because it seems like many users here just do not know how to go to the SQLite website, download sources, and include it in the project... I mean, guys! You cannot do that, but you can use SQL???

For the security part: the default mode of the server library is such that no authentication is required. Unless you write an authentication class and attach it to the server. What I did was write an interface class, which you should derive. In the server demo application, I created a class called CINIAuth, which manages users/passwords/permission in an INI file. Of course, this is not the most secure way, but if you need more security, you can easily write a class which encrypts the passwords and store them wherever you like. (Heck! Even in a SQLite DB!)

Update 21/01/2008: The server now encrypts the passwords with SHA512!

Update 21/01/2008: Now, the server can run as a service. It works as a tray icon, and accepts command line arguments:


  • /instsvc - Install as a service.
  • /remsvc - Uninstall service.
  • /minimize - Minimize to tray on startup.
  • /start - Start listening on startup.
  • /port:[port_num] - Set server listen port to [port_num]. (Will not save this port to settings.ini.)
  • /? or /help - This message.

The way I see it, all you have to do now is take a look at the code, and try it... So, go on!

Points of interest

"Did you learn anything interesting/fun/annoying while writing the code? Did you do anything particularly clever or wild or zany?" (

Ouch, I hate that article-model of CodeProject. It is like they are forcing me to answer a stupid question like in those tests in high-school. Well, yes! I did learn something interesting - Win2K does not support IPv6 by default! And yes, I did learn something fun - I love SQLite! And another yes - the annoying thing is other people's memory leaks... :-)

Just kiddin', CodeProject, I love ya'!


  1. In the connections list, show connected username and open DB (if any is open).
  2. Allow to define DB aliases instead of using file paths (two different modes...).
  3. Enhance security: Add encryption for all communication. (Allow client to request encrypted/non-encrypted communication, and server to force encryption or configure the default mode).
  4. Allow client to request DBs' aliases available. (If the client has permission to request so, and if we are not in direct filenames mode.)
  5. Find the time to actually do these "TODO"s!


  • 21/01/2008:

    Now the server demo application is much more than a demo!

    • You can run it as a service...
    • It works as a tray icon.
    • It accepts command line arguments in case you do not want it as an NT Service...
  • 23/01/2008:
    • Server app: Fixed a bug with reading the passwords from the INI...
    • Server app: Now determines correctly when a client has disconnected.
    • Server app: Now lists the connected clients and allows you to kill them one-by-one! :-P


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Daniel Cohen Gindi
Software Developer (Senior)
Israel Israel
No Biography provided

You may also be interested in...


Comments and Discussions

QuestionBug found Pin
szomi22-Dec-13 7:20
memberszomi22-Dec-13 7:20 
SuggestionNew version, wellcome :) Pin
infal29-Dec-11 1:22
memberinfal29-Dec-11 1:22 
GeneralRe: New version, wellcome :) Pin
woj11719-Jan-12 6:56
memberwoj11719-Jan-12 6:56 
GeneralRe: New version, wellcome :) Pin
Nicolas Mohamed1-Feb-12 2:54
memberNicolas Mohamed1-Feb-12 2:54 
GeneralRe: New version, wellcome :) Pin
jlmw4-Feb-12 15:26
memberjlmw4-Feb-12 15:26 
GeneralRe: New version, wellcome :) Pin
tigerml9-May-12 18:21
membertigerml9-May-12 18:21 
GeneralRe: New version, wellcome :) Pin
singwg22-Jul-12 4:12
membersingwg22-Jul-12 4:12 
GeneralRe: New version, wellcome :) Pin
Daniel Cohen Gindi22-Jul-12 4:40
memberDaniel Cohen Gindi22-Jul-12 4:40 
GeneralRe: New version, wellcome :) Pin
singwg22-Jul-12 19:41
membersingwg22-Jul-12 19:41 
GeneralRe: New version, wellcome :) Pin
hullihulli16-Apr-13 4:14
memberhullihulli16-Apr-13 4:14 
BugRevised version of ThreadProc Pin
infal28-Dec-11 14:04
memberinfal28-Dec-11 14:04 
GeneralRe: Revised version of ThreadProc Pin
Daniel Cohen Gindi28-Dec-11 20:46
memberDaniel Cohen Gindi28-Dec-11 20:46 
BugOther bug's in code and thread termination Pin
infal27-Dec-11 13:57
memberinfal27-Dec-11 13:57 
GeneralRe: Other bug's in code and thread termination Pin
Daniel Cohen Gindi28-Dec-11 20:43
memberDaniel Cohen Gindi28-Dec-11 20:43 
BugSome Improvements! Daniel, how I can contact you? Pin
infal25-Dec-11 12:57
memberinfal25-Dec-11 12:57 
GeneralRe: Some Improvements! Daniel, how I can contact you? Pin
Daniel Cohen Gindi28-Dec-11 20:42
memberDaniel Cohen Gindi28-Dec-11 20:42 
GeneralThanks Pin
Jerry Evans13-Jul-10 6:02
memberJerry Evans13-Jul-10 6:02 
GeneralSQL Server Compact Edition Pin
Hkan Nilsson11-Mar-10 21:38
memberHkan Nilsson11-Mar-10 21:38 
GeneralYou showed very nice example of using SQLite Pin
Khusniddin28-Feb-10 5:40
memberKhusniddin28-Feb-10 5:40 
Generalbug when transmission packet is fragmented Pin
nearperfect15-Jan-10 12:07
membernearperfect15-Jan-10 12:07 
GeneralRe: bug when transmission packet is fragmented Pin
Daniel Cohen Gindi16-Jan-10 10:34
memberDaniel Cohen Gindi16-Jan-10 10:34 
GeneralClient Lib not working On Windows CE Pin
Cüneyt ELÝBOL11-Apr-08 23:07
memberCüneyt ELÝBOL11-Apr-08 23:07 
GeneralTODOs / Requests / BUGs - Here please! Pin
Daniel Cohen Gindi24-Jan-08 6:33
memberDaniel Cohen Gindi24-Jan-08 6:33 
Questionhonoring data types? Pin
Unruled Boy24-Jan-08 3:40
memberUnruled Boy24-Jan-08 3:40 
Questionrole-based per db auth? Pin
Unruled Boy23-Jan-08 23:43
memberUnruled Boy23-Jan-08 23:43 
Questioncompress data? Pin
Unruled Boy23-Jan-08 20:56
memberUnruled Boy23-Jan-08 20:56 
Questionsql profile? Pin
Unruled Boy23-Jan-08 17:01
memberUnruled Boy23-Jan-08 17:01 
Generalso far best sqlite server Pin
Unruled Boy22-Jan-08 3:32
memberUnruled Boy22-Jan-08 3:32 
GeneralRe: so far best sqlite server Pin
Unruled Boy22-Jan-08 4:51
memberUnruled Boy22-Jan-08 4:51 
GeneralRe: so far best sqlite server Pin
Daniel Cohen Gindi22-Jan-08 11:32
memberDaniel Cohen Gindi22-Jan-08 11:32 
Generaldb alias? Pin
Unruled Boy22-Jan-08 15:44
memberUnruled Boy22-Jan-08 15:44 
GeneralRe: db alias? Pin
Daniel Cohen Gindi22-Jan-08 19:51
memberDaniel Cohen Gindi22-Jan-08 19:51 
GeneralRe: db alias? [modified] Pin
Unruled Boy22-Jan-08 19:59
memberUnruled Boy22-Jan-08 19:59 
GeneralRe: db alias? Pin
Daniel Cohen Gindi22-Jan-08 20:15
memberDaniel Cohen Gindi22-Jan-08 20:15 
Generallisting server dbs? Pin
Unruled Boy22-Jan-08 23:09
memberUnruled Boy22-Jan-08 23:09 
GeneralRe: listing server dbs? Pin
Daniel Cohen Gindi23-Jan-08 1:05
memberDaniel Cohen Gindi23-Jan-08 1:05 
Generalbug: did not list users Pin
Unruled Boy23-Jan-08 3:19
memberUnruled Boy23-Jan-08 3:19 
GeneralRe: so far best sqlite server Pin
pophelix4-Jul-09 5:37
memberpophelix4-Jul-09 5:37 
GeneralRe: so far best sqlite server Pin
xnlab.com5-Jul-09 15:38
memberxnlab.com5-Jul-09 15:38 
GeneralRe: so far best sqlite server Pin
Daniel Cohen Gindi5-Jul-09 21:58
memberDaniel Cohen Gindi5-Jul-09 21:58 
GeneralRe: so far best sqlite server Pin
xnlab.com6-Jul-09 3:45
memberxnlab.com6-Jul-09 3:45 
GeneralGuys there's a major update! Pin
Daniel Cohen Gindi21-Jan-08 2:19
memberDaniel Cohen Gindi21-Jan-08 2:19 
GeneralSQLITE_BUSY is an error that just sucks Pin
Djalma R. Santos Filho4-Jan-08 1:26
memberDjalma R. Santos Filho4-Jan-08 1:26 
GeneralRe: SQLITE_BUSY is an error that just sucks Pin
Daniel Cohen Gindi21-Jan-08 2:22
memberDaniel Cohen Gindi21-Jan-08 2:22 
GeneralHelp for C not C/C++ Pin
symons6528-Dec-07 1:42
membersymons6528-Dec-07 1:42 
GeneralRe: Help for C not C/C++ Pin
Daniel Cohen Gindi2-Jan-08 21:35
memberDaniel Cohen Gindi2-Jan-08 21:35 
GeneralRe: Help for C not C/C++ Pin
symons659-Jan-08 22:36
membersymons659-Jan-08 22:36 
GeneralThank You! Pin
Member 238825724-Dec-07 16:18
memberMember 238825724-Dec-07 16:18 
GeneralRe: Thank You! Pin
Daniel Cohen Gindi25-Dec-07 5:43
memberDaniel Cohen Gindi25-Dec-07 5:43 
Generalsome points Pin
John A. Johnson24-Dec-07 6:29
memberJohn A. Johnson24-Dec-07 6:29 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.171114.1 | Last Updated 23 Jan 2008
Article Copyright 2007 by Daniel Cohen Gindi
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid