{"id":2526,"date":"2016-06-03T12:23:35","date_gmt":"2016-06-03T16:23:35","guid":{"rendered":"http:\/\/local.brightwhiz\/?p=2526"},"modified":"2024-02-20T10:39:02","modified_gmt":"2024-02-20T07:39:02","slug":"sqlite-overview","status":"publish","type":"post","link":"http:\/\/local.brightwhiz\/sqlite-overview\/","title":{"rendered":"Getting to Know SQLite for the Absolute Beginner"},"content":{"rendered":"

The problem with relational database server systems such as Oracle<\/a>, SQL Server,\u00a0PostgreSQL, MySQL<\/a> and others is that they require a significant amount of computer resources to function optimally. Enter SQLite<\/a> the solution initially release back in 2000.<\/p>\n

SQLite which began as a Tcl extension was started back in 2000. It was developed by D. Richard Hipp. At the time he was working for General Dynamics on contract with the United States Navy designing software for guided missile destroyers.<\/p>\n

What is SQLite?<\/h2>\n

SQLite is a relational database<\/a> management system that does not run off as a server in the traditional sense. It is designed to run as a light-weight library written in the C programming language.<\/p>\n

Rather than running as a client-server model, SQLite is designed to be embedded into the program with which it is designed to run on. It is therefore widely used and preferred for embedded applications that need some sort of local storage.<\/p>\n

The library uses standard Structured Query Language (SQL)<\/a> to do transactions on the data managed by the library.<\/p>\n

The SQLite database format with multiple tables, indices, triggers, and views is contained in a single portable file stored on disk. The cross platform<\/a> data format can be moved around by simply copying the file. This file is compatible with 32-bit and 64-bit systems. It also works just the same between big-endian and little-endian architectures.<\/p>\n

Where Can I Find SQLite?<\/h2>\n

You can find this versatile library in several places from Desktop applications, Web browsers<\/a>, smartphones and regular cellphones. You can also find it used in all sorts of hand held devices, portable music players, DVD players and more. The list is endless. You can find it running in as little as 4 KB stack space and as little as 100 KB of available heap.<\/p>\n

Being open source<\/a> and in public domain, this reduces restrictions and makes this embedded database engine free and available to be used by anyone for both commercial and personal uses.<\/p>\n

As this library is considered ACID (conforms to the properties of Atomicity, Consistency, Isolation and Durability), it is considered safe to use even when memory allocation failures and disk I\/O errors are encountered.<\/p>\n

Usage<\/h2>\n

SQLite is not ideal for use in situations where client-server applications would make more sense. This situations are for example where several clients are performing transactions on the same database server over a network especially where latency is a factor.<\/p>\n

High volume websites will not play well with SQLite as opposed to a low volume website. In this case an enterprise class client-server database system like those mentioned in the first paragraph of this article may make more sense.<\/p>\n

If you are dealing with high concurrency one reads, it may not be a problem using this library however when using multiple writes you will land into problems trying to rely on it as it supports only one writer instance at a time.<\/p>\n

Again using very large datasets may not be what you want out of this library. It may well support a file size up to 140 terabytes, notwithstanding the maximum filesize for the underlying platform. Nevertheless it would not be a good ideal to hod all that data in a single file database.<\/p>\n

SQLite however can be ideal for education and training purposes. It can also be used to stand in for more permanent enterprise database systems during demos or prototypes.<\/p>\n

It can be used for archival purposes as well as Ad hoc data files. It can also be used for local caching of enterprise data, local storage for websites and many more.<\/p>\n

Security<\/h2>\n

By default SQLite databases are not encrypted nor are they password protected. By this very fact it makes this embedded database not ideal for storing sensitive information off the shelf.<\/p>\n

The good news is that there are tools like SQLite Encryption Extension and SQLite Crypt and many other ports that allow users to secure their databases. It is highly recommended to implement those solutions should your application store sensitive data.<\/p>\n

You can get a copy of the SQLite source code in all its variants as well as compiled binaries from the official download page<\/a>.<\/p>\n

Ref:
\nhttps:\/\/www.sqlite.org<\/a> – Official SQLite Website
\nhttps:\/\/en.wikipedia.org\/wiki\/ACID<\/a> – Definition of ACID
\nhttps:\/\/www.sqlite.org\/cvstrac\/wiki?p=SqliteWebSecurity – Web Security
\nhttp:\/\/stackoverflow.com\/questions\/1259561\/encrypt-sqlite-database-in-c-sharp<\/a> – SQLite security ports<\/p>\n","protected":false},"excerpt":{"rendered":"

The problem with relational database server systems such as Oracle, SQL Server,\u00a0PostgreSQL, MySQL and others is that they require a significant amount of computer resources to function optimally. Enter SQLite…<\/p>\n","protected":false},"author":1,"featured_media":2527,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[111,164,180,220,424,433,449,543,544,549,551,591],"yoast_head":"\nSQLite Explained in Three Minutes or Less<\/title>\n<meta name=\"description\" content=\"This is a quick overview of SQLite, an open source, cross platform, lightweight and server-less database engine that you can embed in your applications\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"http:\/\/local.brightwhiz\/sqlite-overview\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQLite Explained in Three Minutes or Less\" \/>\n<meta property=\"og:description\" content=\"This is a quick overview of SQLite, an open source, cross platform, lightweight and server-less database engine that you can embed in your applications\" \/>\n<meta property=\"og:url\" content=\"http:\/\/local.brightwhiz\/sqlite-overview\/\" \/>\n<meta property=\"og:site_name\" content=\"Brightwhiz.com\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/brightwhiz\/\" \/>\n<meta property=\"article:published_time\" content=\"2016-06-03T16:23:35+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-02-20T07:39:02+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/local.brightwhiz\/wp-content\/uploads\/2016\/05\/SQLite-Explained.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"630\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Michael Bright\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@brightwhizmag\" \/>\n<meta name=\"twitter:site\" content=\"@brightwhizmag\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Michael Bright\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"http:\/\/local.brightwhiz\/sqlite-overview\/#article\",\"isPartOf\":{\"@id\":\"http:\/\/local.brightwhiz\/sqlite-overview\/\"},\"author\":{\"name\":\"Michael Bright\",\"@id\":\"http:\/\/local.brightwhiz\/#\/schema\/person\/81f0f3126f13834ae2e7f381b3028e32\"},\"headline\":\"Getting to Know SQLite for the Absolute Beginner\",\"datePublished\":\"2016-06-03T16:23:35+00:00\",\"dateModified\":\"2024-02-20T07:39:02+00:00\",\"mainEntityOfPage\":{\"@id\":\"http:\/\/local.brightwhiz\/sqlite-overview\/\"},\"wordCount\":735,\"commentCount\":0,\"publisher\":{\"@id\":\"http:\/\/local.brightwhiz\/#organization\"},\"image\":{\"@id\":\"http:\/\/local.brightwhiz\/sqlite-overview\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/local.brightwhiz\/wp-content\/uploads\/2016\/05\/SQLite-Explained.jpg\",\"keywords\":[\"C++\",\"Cross Platform\",\"Database\",\"Embedded\",\"Open Source\",\"Optimization\",\"Performance\",\"Software Design\",\"Software development\",\"SQL\",\"SQLite\",\"Tools\"],\"articleSection\":[\"Articles\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"http:\/\/local.brightwhiz\/sqlite-overview\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"http:\/\/local.brightwhiz\/sqlite-overview\/\",\"url\":\"http:\/\/local.brightwhiz\/sqlite-overview\/\",\"name\":\"SQLite Explained in Three Minutes or Less\",\"isPartOf\":{\"@id\":\"http:\/\/local.brightwhiz\/#website\"},\"primaryImageOfPage\":{\"@id\":\"http:\/\/local.brightwhiz\/sqlite-overview\/#primaryimage\"},\"image\":{\"@id\":\"http:\/\/local.brightwhiz\/sqlite-overview\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/local.brightwhiz\/wp-content\/uploads\/2016\/05\/SQLite-Explained.jpg\",\"datePublished\":\"2016-06-03T16:23:35+00:00\",\"dateModified\":\"2024-02-20T07:39:02+00:00\",\"description\":\"This is a quick overview of SQLite, an open source, cross platform, lightweight and server-less database engine that you can embed in your applications\",\"breadcrumb\":{\"@id\":\"http:\/\/local.brightwhiz\/sqlite-overview\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\/\/local.brightwhiz\/sqlite-overview\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/local.brightwhiz\/sqlite-overview\/#primaryimage\",\"url\":\"http:\/\/local.brightwhiz\/wp-content\/uploads\/2016\/05\/SQLite-Explained.jpg\",\"contentUrl\":\"http:\/\/local.brightwhiz\/wp-content\/uploads\/2016\/05\/SQLite-Explained.jpg\",\"width\":1200,\"height\":630,\"caption\":\"SQLite Explained\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\/\/local.brightwhiz\/sqlite-overview\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\/\/local.brightwhiz\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Getting to Know SQLite for the Absolute Beginner\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\/\/local.brightwhiz\/#website\",\"url\":\"http:\/\/local.brightwhiz\/\",\"name\":\"Brightwhiz.com\",\"description\":\"Best Tech guides, Tutorials, and News\",\"publisher\":{\"@id\":\"http:\/\/local.brightwhiz\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\/\/local.brightwhiz\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"http:\/\/local.brightwhiz\/#organization\",\"name\":\"Brightwhiz\",\"url\":\"http:\/\/local.brightwhiz\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/local.brightwhiz\/#\/schema\/logo\/image\/\",\"url\":\"http:\/\/local.brightwhiz\/wp-content\/uploads\/2021\/11\/brightwhiz-com-logo-orange.png\",\"contentUrl\":\"http:\/\/local.brightwhiz\/wp-content\/uploads\/2021\/11\/brightwhiz-com-logo-orange.png\",\"width\":706,\"height\":135,\"caption\":\"Brightwhiz\"},\"image\":{\"@id\":\"http:\/\/local.brightwhiz\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/brightwhiz\/\",\"https:\/\/x.com\/brightwhizmag\",\"https:\/\/instagram.com\/bright_whiz\/\",\"https:\/\/www.pinterest.com\/sobbayi\/\",\"https:\/\/www.youtube.com\/channel\/UC6sCdP_d_RiTIM7ErFT-PSQ\"]},{\"@type\":\"Person\",\"@id\":\"http:\/\/local.brightwhiz\/#\/schema\/person\/81f0f3126f13834ae2e7f381b3028e32\",\"name\":\"Michael Bright\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/local.brightwhiz\/#\/schema\/person\/image\/\",\"url\":\"http:\/\/1.gravatar.com\/avatar\/da90485875ff0aafa38fdd494abe87d1?s=96&d=mm&r=g\",\"contentUrl\":\"http:\/\/1.gravatar.com\/avatar\/da90485875ff0aafa38fdd494abe87d1?s=96&d=mm&r=g\",\"caption\":\"Michael Bright\"},\"sameAs\":[\"https:\/\/sobbayi.com\"],\"url\":\"http:\/\/local.brightwhiz\/author\/sobbayiadmin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQLite Explained in Three Minutes or Less","description":"This is a quick overview of SQLite, an open source, cross platform, lightweight and server-less database engine that you can embed in your applications","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"http:\/\/local.brightwhiz\/sqlite-overview\/","og_locale":"en_US","og_type":"article","og_title":"SQLite Explained in Three Minutes or Less","og_description":"This is a quick overview of SQLite, an open source, cross platform, lightweight and server-less database engine that you can embed in your applications","og_url":"http:\/\/local.brightwhiz\/sqlite-overview\/","og_site_name":"Brightwhiz.com","article_publisher":"https:\/\/www.facebook.com\/brightwhiz\/","article_published_time":"2016-06-03T16:23:35+00:00","article_modified_time":"2024-02-20T07:39:02+00:00","og_image":[{"width":1200,"height":630,"url":"http:\/\/local.brightwhiz\/wp-content\/uploads\/2016\/05\/SQLite-Explained.jpg","type":"image\/jpeg"}],"author":"Michael Bright","twitter_card":"summary_large_image","twitter_creator":"@brightwhizmag","twitter_site":"@brightwhizmag","twitter_misc":{"Written by":"Michael Bright","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"http:\/\/local.brightwhiz\/sqlite-overview\/#article","isPartOf":{"@id":"http:\/\/local.brightwhiz\/sqlite-overview\/"},"author":{"name":"Michael Bright","@id":"http:\/\/local.brightwhiz\/#\/schema\/person\/81f0f3126f13834ae2e7f381b3028e32"},"headline":"Getting to Know SQLite for the Absolute Beginner","datePublished":"2016-06-03T16:23:35+00:00","dateModified":"2024-02-20T07:39:02+00:00","mainEntityOfPage":{"@id":"http:\/\/local.brightwhiz\/sqlite-overview\/"},"wordCount":735,"commentCount":0,"publisher":{"@id":"http:\/\/local.brightwhiz\/#organization"},"image":{"@id":"http:\/\/local.brightwhiz\/sqlite-overview\/#primaryimage"},"thumbnailUrl":"http:\/\/local.brightwhiz\/wp-content\/uploads\/2016\/05\/SQLite-Explained.jpg","keywords":["C++","Cross Platform","Database","Embedded","Open Source","Optimization","Performance","Software Design","Software development","SQL","SQLite","Tools"],"articleSection":["Articles"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["http:\/\/local.brightwhiz\/sqlite-overview\/#respond"]}]},{"@type":"WebPage","@id":"http:\/\/local.brightwhiz\/sqlite-overview\/","url":"http:\/\/local.brightwhiz\/sqlite-overview\/","name":"SQLite Explained in Three Minutes or Less","isPartOf":{"@id":"http:\/\/local.brightwhiz\/#website"},"primaryImageOfPage":{"@id":"http:\/\/local.brightwhiz\/sqlite-overview\/#primaryimage"},"image":{"@id":"http:\/\/local.brightwhiz\/sqlite-overview\/#primaryimage"},"thumbnailUrl":"http:\/\/local.brightwhiz\/wp-content\/uploads\/2016\/05\/SQLite-Explained.jpg","datePublished":"2016-06-03T16:23:35+00:00","dateModified":"2024-02-20T07:39:02+00:00","description":"This is a quick overview of SQLite, an open source, cross platform, lightweight and server-less database engine that you can embed in your applications","breadcrumb":{"@id":"http:\/\/local.brightwhiz\/sqlite-overview\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/local.brightwhiz\/sqlite-overview\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/local.brightwhiz\/sqlite-overview\/#primaryimage","url":"http:\/\/local.brightwhiz\/wp-content\/uploads\/2016\/05\/SQLite-Explained.jpg","contentUrl":"http:\/\/local.brightwhiz\/wp-content\/uploads\/2016\/05\/SQLite-Explained.jpg","width":1200,"height":630,"caption":"SQLite Explained"},{"@type":"BreadcrumbList","@id":"http:\/\/local.brightwhiz\/sqlite-overview\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/local.brightwhiz\/"},{"@type":"ListItem","position":2,"name":"Getting to Know SQLite for the Absolute Beginner"}]},{"@type":"WebSite","@id":"http:\/\/local.brightwhiz\/#website","url":"http:\/\/local.brightwhiz\/","name":"Brightwhiz.com","description":"Best Tech guides, Tutorials, and News","publisher":{"@id":"http:\/\/local.brightwhiz\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/local.brightwhiz\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"http:\/\/local.brightwhiz\/#organization","name":"Brightwhiz","url":"http:\/\/local.brightwhiz\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/local.brightwhiz\/#\/schema\/logo\/image\/","url":"http:\/\/local.brightwhiz\/wp-content\/uploads\/2021\/11\/brightwhiz-com-logo-orange.png","contentUrl":"http:\/\/local.brightwhiz\/wp-content\/uploads\/2021\/11\/brightwhiz-com-logo-orange.png","width":706,"height":135,"caption":"Brightwhiz"},"image":{"@id":"http:\/\/local.brightwhiz\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/brightwhiz\/","https:\/\/x.com\/brightwhizmag","https:\/\/instagram.com\/bright_whiz\/","https:\/\/www.pinterest.com\/sobbayi\/","https:\/\/www.youtube.com\/channel\/UC6sCdP_d_RiTIM7ErFT-PSQ"]},{"@type":"Person","@id":"http:\/\/local.brightwhiz\/#\/schema\/person\/81f0f3126f13834ae2e7f381b3028e32","name":"Michael Bright","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/local.brightwhiz\/#\/schema\/person\/image\/","url":"http:\/\/1.gravatar.com\/avatar\/da90485875ff0aafa38fdd494abe87d1?s=96&d=mm&r=g","contentUrl":"http:\/\/1.gravatar.com\/avatar\/da90485875ff0aafa38fdd494abe87d1?s=96&d=mm&r=g","caption":"Michael Bright"},"sameAs":["https:\/\/sobbayi.com"],"url":"http:\/\/local.brightwhiz\/author\/sobbayiadmin\/"}]}},"_links":{"self":[{"href":"http:\/\/local.brightwhiz\/wp-json\/wp\/v2\/posts\/2526"}],"collection":[{"href":"http:\/\/local.brightwhiz\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/local.brightwhiz\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/local.brightwhiz\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/local.brightwhiz\/wp-json\/wp\/v2\/comments?post=2526"}],"version-history":[{"count":1,"href":"http:\/\/local.brightwhiz\/wp-json\/wp\/v2\/posts\/2526\/revisions"}],"predecessor-version":[{"id":13526,"href":"http:\/\/local.brightwhiz\/wp-json\/wp\/v2\/posts\/2526\/revisions\/13526"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/local.brightwhiz\/wp-json\/wp\/v2\/media\/2527"}],"wp:attachment":[{"href":"http:\/\/local.brightwhiz\/wp-json\/wp\/v2\/media?parent=2526"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/local.brightwhiz\/wp-json\/wp\/v2\/categories?post=2526"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/local.brightwhiz\/wp-json\/wp\/v2\/tags?post=2526"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}