Strict Tables – Column type constraints in SQLite - Draft
sqlite.org
sqlite.org
CREATE TABLE tIssue (
id INTEGER PRIMARY KEY NOT NULL CHECK (typeof(id) = 'integer'),
col1 BLOB NOT NULL CHECK (typeof(col1) = 'blob'),
col2 TEXT CHECK (typeof(col2) = 'text' OR col2 IS NULL)
);
I'm very much looking forward to using strict tables.
Most of the common databases that I'm aware of (PostgreSQL, MySQL, SQL Server) do implicit type conversion on insertion (as well as in in many other places). I haven't checked this, but it wouldn't surprise me if that's actually ANSI SQL standard behavior.
Quoting Postgres docs (because ain’t nobody paying for the ANSI SQL standard just to quote it):
> If the cast is marked AS ASSIGNMENT then it can be invoked implicitly when assigning a value to a column of the target data type. For example, supposing that foo.f1 is a column of type text, then:
INSERT INTO foo (f1) VALUES (42);
Presumably, in any ANSI SQL-compliant DBMS, you could redefine whatever assignment casts are annoying you to be non-assignment casts. (AFAIK, there’s no ANSI SQL standardization on what casts must be assignment casts; so lowest-common-denominator SQL-standard-compliant queries shouldn’t be relying on the presence of any defined assignment casts.)
$ CREATE TABLE t (x int);
CREATE TABLE
$ INSERT INTO t (x) VALUES (42);
INSERT 0 1
$ INSERT INTO t (x) VALUES ('42');
INSERT 0 1
$ INSERT INTO t (x) VALUES (CAST('42' AS text));
ERROR: column "x" is of type integer but expression is of type text
LINE 1: INSERT INTO t (x) VALUES (CAST('42' AS text));
^
HINT: You will need to rewrite or cast the expression.
$ INSERT INTO t (x) VALUES ('42a');
ERROR: invalid input syntax for type integer: "42a"
LINE 1: INSERT INTO t (x) VALUES ('42a');
$ INSERT INTO t (x) VALUES (CAST(42 AS bigint));
INSERT 0 1Are your projects in a dynamically typed language? I struggle to think why a statically typed language would have this problem, because the data being transferred at the boundary between application and database is already well-defined and strictly typed.
You need more than just a statically typed language to prevent it. Either you need something like an ORM or you need some clever hackery like sqlx that interrogates your schema at compile time.
sqlite3_bind_int(stmt, 3, 45);
A strictly typed database would error out here, a dynamically typed one will just assign the int to the string column, potentially corrupting data.
This is exactly equivalent to messing up types for variables in a dynamically typed language, all pros and cons of typechecking apply equally for SQL. The fact that the host language C is statically typed is irrelevant.
I would assume posgres is considered "strictly typed", yet it will never error on the correct version of this: all parameters are necessarily passed as `char*` equivalent to "normal" untyped literals/strings.
The best you can do is provide an explicit type (via `paramTypes`), which skips inference and is equivalent to typed constants[0].
[0] https://www.postgresql.org/docs/current/sql-syntax-lexical.h...
I have no idea how the postgres C API looks, but the SQLite API has different functions for binding different kinds of data types to prepared statements, like `sqlite3_bind_int` for ints, `sqlite3_bind_blob` for binary blobs, `sqlite3_bind_text` for strings, and so forth. So SQLite "knows" the source type you're binding.
For example a "PostgreSQL" personality (set via PRAGMA maybe?) which dynamically imposes the same type names and "limitations" on SQLite as PostgreSQL.
Ditto for other SQL dialects (MySQL, etc).
SQLite already has the flexibility to allow the data types, so this idea is a way of putting constraints in place that are really beneficial for a _lot_ of common use cases.
eg "You're using SQLite as a local test for a [PostgreSQL/MySQL/etc] prod database"
We now use the awesome Testcontainers library (available for multiple languages) that makes it easy to launch the real database in a Docker container. Testing applications against multiple databases is now trivial.
I have small scripts that do things like send an email on a given business event. These scripts pull data from the main ERP. To keep the system upgradable we avoid changes to the ERP's database. So these scripts need their own DB to store state, such as when the email was sent. At first this involved one Postgres instance with databases for each script or application. If that Postgres server moves IP or has a version upgrade often every script or app needs a config change or library upgrade. Everything gets coupled together. By using an SQLite DB per script a range of problems go away. Of course anything that has multiple users or needs to be reported on has to go into a central DB but a surprising amount can happly run on it's own independent SQLITE DB.
To move hosts or backup the application and DB you take a copy of the folder. It's so easy.
Yeah it does a lot more than fopen(). So does SQLite. It's still, bottom line, just secondary storage with a fancy API.
Not a dependency in the sense of not requiring a specific version of a Postgres db provider, or a specific version of a Postgres itself, or able to do any work without Postgres existing? Laughably no.
DB agnostic software is very uncommon, even though ODBC is well-established and mature.
I haven't spoken to too many folks who've had to make this decision, but off the top of my head I think there's a number of factors that aren't obvious at first glance that factor into this equation:
1. You need authors of queries to understand the least common denominator of supported SQL features and syntax (probably ANSI SQL, but there may be cases where it isn't)
2. Such projects would need to test or otherwise verify that all SQL queries execute in the ways they're expected to. This can be tricky when you have regressions in query performance from one database flavor to the next.
3. Your application can't have features that are made or broken by features specific to a particular database. I can see this being a very limiting constraint in a world where business intelligence and data analysis at scale has become a the norm.
Are there other factors here that would compel projects to stick to something like ODBC (or your database abstraction layer of choice) rather than against it?
For me it's about using the full power of my underlying technologies. Just like i don't restrict myself to a subset of C that would compile with any C compiler ever, i don't restrict myself to generic SQL.
It's designed to be embedded into software and used to replace raw files. That's why the database is a single file, which is something that is a feature in every situation except if you have a server doing many concurrent writes.
The only reason that SQLite even works as a stand-in for an RDBMS is because they did such a good job implementing the SQL standard.
Testcontainers are the proper solution for automated tests, but it is not trivial to set up CI as AFAIK it requires access to the Docker socket.
It keeps databases in a single local file like SQLite, and it supports PostgreSQL syntax (not sure which version). It does a load of funky stuff to be really fast at analytics workloads, but I would guess it's fast enough at transactional workloads for small-scale use.
This is not a challenge commonly faced on on the enterprise server side, hence why many of us cannot imagine a use case for SQLite. Our workloads involve multiple readers/writers for both scaling and availability. An RDBMS cluster is the obvious choice.
But for mobile/embedded, all the data is local to a single device and commonly also a single process. In this case, an RDBMS would add unnecessary overhead. Additionally, there's desktop software that has to work with relatively large datasets, which require reliable persistence and efficient searching/reading. Adding an RDBMS would complicate installation and support.
Further, researchers and data analysts benefit from SQLite databases when the data is too large to hold in memory, yet not large enough to warrant a proper data warehouse. Even ~100 MB datasets can benefit from SQLite if you're performing a lot of random writes/reads or want to execute complex queries. There are other alternatives such as Apache Arrow, but SQLite is tried and tested option. It can be populated and queried similar to SQL-based data warehouses, and it also includes secondary indexes, an efficient query engine, and fast random writes with durability and atomic transactions.
There's a reason why SQLite can reputably claim to be the "Most Widely Deployed and Used Database Engine" with an estimated one trillion SQL database in use. [0]
Sqlite is a beast, there's a lot you can do with it. But there's no shared buffer cache or strategy for keeping disk pages in-memory across transactions and files, so you pay for it with random disk I/O access proportionate to your client load. But for many workloads, it's a really low-maintenance option with lots of low tech and effort back-up/disaster recovery options and depending on how chatty your clients are you can get a lot out of modern hardware/clouds.
You could also get clever, and provide pure in-memory access when pinning clients to hosts and treat it like a write-through cache.
A common use case is using SQLite as a local stand in replacement for "whatever production database" people are using.
Commonly PostgreSQL, but not always. So, my idea was just a way to make that work better, nothing more.
As always the TCL tests are worth admiring:
# STRICT tables have on a limited number of allowed datatypes.
#
do_catchsql_test strict1-1.1 {
CREATE TABLE t1(a) STRICT;
} {1 {unknown datatype for t1.a: ""}}
do_catchsql_test strict1-1.2 {
CREATE TABLE t1(a PRIMARY KEY) STRICT, WITHOUT ROWID;
} {1 {unknown datatype for t1.a: ""}}
do_catchsql_test strict1-1.3 {
CREATE TABLE t1(a PRIMARY KEY) WITHOUT ROWID, STRICT;
} {1 {unknown datatype for t1.a: ""}}
do_catchsql_test strict1-1.4 {
CREATE TABLE t1(a BANJO PRIMARY KEY) WITHOUT ROWID, STRICT;
} {1 {unknown datatype for t1.a: "BANJO"}}
do_catchsql_test strict1-1.5 {
CREATE TABLE t1(a TEXT PRIMARY KEY, b INT, c INTEGER, d REAL, e BLOB, f DATE) strict;
} {1 {unknown datatype for t1.f: "DATE"}}
do_catchsql_test strict1-1.6 {
CREATE TABLE t1(a TEXT PRIMARY KEY, b INT, c INTEGER, d REAL, e BLOB, f TEXT(50)) WITHOUT ROWID, STRICT;
} {1 {unknown datatype for t1.f: "TEXT(50)"}}
do_execsql_test strict1-2.0 {
CREATE TABLE t1(
a INT,
b INTEGER,
c BLOB,
d TEXT,
e REAL
) STRICT;
} {}
do_catchsql_test strict1-2.1 {
INSERT INTO t1(a) VALUES('xyz');
} {1 {cannot store TEXT value in INT column t1.a}}It really is a great project.
In a couple of projects I work on the main reason for plopping an ORM between sqlite and the application is to implement type strictness, and removing that need would be excellent.
It would also neatly shut down a discussion that comes up far too often when someone wants to use sqlite as data store, which is surely a good thing as it is often just a stop motion argument in my experience.
¹ I am closer to that camp iff I have to choose an option from the article.
The "doesn't enforce foreign key constraints by default" is much more surprising / annoying to me.
https://news.ycombinator.com/item?id=28050198
"Aghast" feels like an appropriately accurate characterization to me. ;)
I'm actually curious if this change was a direct (and incredibly fast) reaction to that thread, or if the timing was just coincidental?
If they can, and they insert an invalid row, what will happen when a modern version of SQLite attempts to read from the table?
Link: https://www.sqlite.org/gencol.html#compatibility
Quote:
Generated column support was added with SQLite version 3.31.0 (2020-01-22). If an earlier version of SQLite attempts to read a database file that contains a generated column in its schema, then that earlier version will perceive the generated column syntax as an error and will report that the database schema is corrupt.
To clarify: SQLite version 3.31.0 can read and write any database created by any prior version of SQLite going back to SQLite 3.0.0 (2004-06-18). And, earlier versions of SQLite, prior to 3.31.0, can read and write databases created by SQLite version 3.31.0 and later as long as the database schema does not contain features, such as generated columns, that are not understood by the earlier version. Problems only arise if you create a new database that contains generated columns, using SQLite version 3.31.0 or later, and then try to read or write that database file using an earlier version of SQLite that does not understand generated columns.
End of quote.
I can't find it easily in documentation, but I've read somewhere that SQLite stores schema in database as text - and reasoning behind this was that it allows SQLite devs change internal representation of schema without worrying about backwards compatibility too much.
Personally I don't particularly care - SQLite's loose typing has never caused any problems for me - but I've seen SO many programmers dismiss SQLite as an option because they find its loose typing distasteful.
Eliminating one of the most common reasons that people reject SQLite feels like a big win to me.
I wish they add a real strict type for dates and times too.
There's a pattern for enforcing datetime formats using a CHECK constraint which I've not tried myself yet but which looks like it could work: https://sqlite.org/forum/forumpost/4f4c96938f4bef32?t=h
- Use an integer column with a Unix timestamp. Not human-readable and no sub-second accuracy if you need it (unless you store the time in milliseconds instead of full seconds, requiring larger int columns, or just get completely crazy and use a float field of some sort.
- Use a `datetime` field. Human-readable, supports milliseconds natively, and supports any time between years 1000 and 10,000, but doesn't store a time zone - so if you define the column with something like `'created' TIMESTAMP NOT NULL DEFAULT current_timestamp`, who knows what will actually be inserted in that field when the row is created?
- Use a `timestamp` field. Like datetime fields, you get a human-readable field with sub-second precision, but the range is only from the Unix epoch to Y2K38, which is seventeen years away now. These fields store the date unambiguously as UTC - hooray! But… when you retrieve the date, the time will be adjusted "back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis." You can make an unwieldy cast to get the date as UTC - `SELECT CONVERT_TZ(created, @@session.time_zone, "+00:00") AS created_utc` - but obviously this is easy to forget about and performs an unperformant double-cast.
All three of these options suck in their own distinct ways.
All this rant is to say that date storage in at least two of the most common databases is a bit of a mess right now (it's been long enough since I've used Postgres that I can't recall if things are any better there, but I'm sure one of its fanboys will let us know soon enough if it is). So don't get too hard on your previous developer or blame SQLite in particular.
I'm pretty sure that format is also string comparable for sorting.
I used to think that, but now I realize those problems would disappear if you set server time to UTC and only work with UTC values. It's not an excuse for MySQL's absolutely flawed approach to datetime values, but it's a pretty sane workaround.
- it doesn't matter that DATETIME lacks timezone information, since you implicitly treat all timezones as UTC.
- TIMESTAMP would add still the timezone offset, but it wouldn't do anything since the timezone is UTC +00.
With server timezone set to UTC, TIMESTAMP functions identically to DATETIME but with massively reduced capacity, so I don't see any reason to use TIMESTAMP anymore. Compared to BIGINT unix epoch, DATETIME has the advantage of being compatible with MySQL's datetime-manipulating functions (such as ON UPDATE CURRENT_TIMESTAMP).
- TIMESTAMP 'stores' timezone, but it has a limit of year 2038.
- DATETIME has a limit of year 9999, but it does not store timezone at all.
There really is no better way. Pick DATETIME for its higher capacity, then only ever use UTC time in the database to get around the lack of timezone storage in DATETIME.
From what I understand, this would mean that you would always have to set the value of datetime columns manually (with a UTC-adjusted value) instead of being able to use 'ON UPDATE current_timestamp'.
Of course, that's unless you set the server time to use UTC (as you suggested above), which strikes me as an extreme and not-very-portable approach.
Could you elaborate on why timestamptz should be used? The name would imply it stores a time zone; is that correct, and the normal timestamp type does not as in MySQL?
You almost always want to use timestamptz in columns and only use timestamp when doing certain types of timestamp arthmetics.
Also the per-table opt-in is reasonable.
sqlite is really becoming this bedrock thing in the computing landscape, it's silently everywhere and all agree that it is kind of a good thing.
I haven't checked recently, but I was unable to determine the type of ? parameters in SQLite prepared statements. Maybe this is something that can also be accommodated with STRICT tables.
CREATE TABLE t1(a INT, b TEXT); INSERT INTO t1(a,b) VALUES(1,'2'); SELECT * FROM t1 WHERE a=?;
The type of the ? is ambiguous. You can say that it "prefers" an integer, but most RDBMSes will also accept a string literal in place of the ?:
SELECT * FROM t1 WHERE a='1'; -- works in PG, MySQL, SQLServer, and Oracle
====
select "23 % 3", 23 % 3;
select "12.3 % 3", 12.3 % 3;
select "12 % 2.5", 12 % 2.5;
select "23 / 3", 23 / 3;
select "12.3 / 3", 12.3 / 3;
select "12 / 2.5", 12 / 2.5;
create table ta(a text, b integer, c float);
select 'insert declared types == value types';
insert into ta(a,b,c) values('a', 1, 2.0);
select 'insert declared types != value types text';
insert into ta(a,b,c) values('b', '1', '2.0');
select 'insert declared types != value types';
insert into ta(a,b,c) values('c', 1.0, 2);
select 'update declared types == value types';
update ta set a = 'a' ,b = 1, c = 2.0 where a = 'a';
select 'update declared types != value types text';
update ta set a = 'a' ,b = '1', c = '2.0' where a = 'a';
select 'update declared types != value types';
update ta set a = 'a' ,b = 1.0, c = 2 where a = 'a';
select 'update one value declared types != value types';
update ta set b = 1.0 where a = 'a';
select 'update one value declared types != value types';
update ta set a = 49 where a = 'b';
====
Output default sqlite:
====
23 % 3|2
12.3 % 3|0.0
12 % 2.5|0.0
23 / 3|7
12.3 / 3|4.1
12 / 2.5|4.8
insert declared types == value types
insert declared types != value types text
insert declared types != value types
update declared types == value types
update declared types != value types text
update declared types != value types
update one value declared types != value types
update one value declared types != value types
====
Output of sqlite with "pedantic":
====
23 % 3|2
FP Remainder received non integer values 3.000000 :: 12.300000
12.3 % 3|0.0
FP Remainder received non integer values 2.500000 :: 12.000000
12 % 2.5|0.0
23 / 3|7
FP Division received non integer values 3.000000 :: 12.300000
12.3 / 3|4.1
FP Division received non integer values 2.500000 :: 12.000000
12 / 2.5|4.8
insert declared types == value types
insert declared types != value types text
Affinity applied on make record 1 : 1 : D
Affinity applied on make record 2 : 1 : E
insert declared types != value types
Affinity applied on make record 1 : 2 : D
update declared types == value types
update declared types != value types text
Affinity applied on make record 1 : 1 : D
Affinity applied on make record 2 : 1 : E
update declared types != value types
Affinity applied on make record 1 : 2 : D
update one value declared types != value types
Affinity applied on make record 1 : 2 : D
update one value declared types != value types
Affinity applied on make record 0 : 3 : B
====
I think if you expect your database to use certain types, you write your code accordingly. Why would you ever want to insert a BLOB into an INTEGER column? Some kind of data validation/sanitizing is necessary anyway, the database engine being strict about it is just an additional layer of protection, but it shouldn't be one's first line of defense against mistakes. IMHO.
OTOH, if type safety is a concern, one can already enforce it using CHECK constraints or triggers. But yeah, it's like a seat belt in a car - in order to benefit from it, you have to actually put it on.
EDIT: The fact that Golang is type-checked should make most of the issues here moot (since the only way to get the wrong type would be to have a malformed INSERT query, and Golang would choke the next time one of those rows showed up in a query); but it would mean I wouldn't have to be quite so paranoid when frobbing about manually with the sqlite3 command-line tool, for instance.
That really depends on how “golang” fetches data: https://www.sqlite.org/c3ref/column_blob.html
C API makes it clear that if you request an int, you get an int, no matter what. The only way it could care is a client/wrapper library that looked at a column type, called a specific accessor function and then tried to push that value into non-compatible golang structure field(?). It doesn’t make the database incompatible, it’s just a wrapper with a poorly thought out type consistency. At C level, there is no such issue.
The problem is opposite - not having type error. The simplest example probably is import from CSV with incorrect column order. Using other DB you get an error, SQLite will just import everything.
What sort of performance impact could we expect to see when accessing strict tables?
Also, is there going to be an ALTER TABLE variant?
Is having a "STRICT" keyword appended to the end of the CREATE TABLE syntax the best option? I'd have thought this would be a PRAGMA option if ever implemented.
I use sqlite a fair bit but admittedly know little about the layers beneath, and it feels like tagging this feature to the end of CREATE TABLE just means more "magic invocation boilerplate" that might be straying from SQL syntax?
Again, not too clued into the deep minuta of SQL so maybe suffixes to the CREATE TABLE are "valid"? This would count similar to engine or charset configuration in a mysql statement?
Very grateful to the SQLite team! Kudos for not digging your heels in and trying to accommodate your diverse community.
Big respect.
I always knew about it but basically never used it, just treated tables as though they were strongly typed.
Can anyone please share common pitfalls of $subj issue? (Apart from obvious “you can insert a mess into a column and get hit by it later”)
This happens because most tutorials do not mention this either. I will not be surprised if most authors of the tutorials don’t know this either.
I did read some webpages on the official website, but I don’t remember seeing this either. It’s possible that I read pass this information but did not pay attention to it.
I find it surprising that apparently many devs use SQLite without knowing any of this. Pretty much the first thing I try to find out about languages or databases new to me that I might want use is the datatype support.
[1] https://www.sqlite.org/draft/different.html
Very scary sqlite doesn't do that already...
For the kinds of uses SQLite sees, the loose approach to data integrity may not have caused much grief, but I can't imagine many situations where it would have helped either.
Having support for enabling sane behaviour is welcome even if it has to be opt-in for compatibility.